Find the actual cause of the problem instead of guessing.
SQL Server Consulting
Slow queries, blocking, replication lag, storage growth, TempDB pressure — SQL Server performance problems are rarely simple. The symptoms are often clear. The root cause requires investigation.
Query Performance Analysis
A slow query is a symptom. The cause is typically found in the execution plan — suboptimal join strategies, index scans where seeks are possible, incorrect row estimates caused by stale statistics, or plans reused from different parameter values. Performance analysis starts with reading what SQL Server is actually doing, not what you expect it to be doing.
- Execution plan analysis — actual vs estimated rows, operator costs
- Parameter sniffing identification and resolution
- Statistics currency review (sys.stats, DBCC SHOW_STATISTICS)
- Query rewrite for better plan selection
- OPTION (RECOMPILE), OPTIMIZE FOR, and plan guide usage
- Query Store review for plan regression
Blocking & Deadlock Investigation
Blocking is one of the most common causes of application timeouts. A single long-running transaction can hold locks that prevent dozens of other sessions from proceeding. Deadlocks introduce application errors that are difficult to reproduce. Both require systematic investigation to resolve correctly.
- Session analysis using sys.dm_exec_requests and sys.dm_os_waiting_tasks
- Blocking chain identification and lead blocker analysis
- Deadlock graph analysis from the error log or Extended Events
- Isolation level review — READ COMMITTED SNAPSHOT assessment
- Transaction scope and duration review
- LCK_M_* wait type analysis
Index Analysis
Indexes improve read performance but carry write overhead. Over time, databases accumulate missing indexes identified by SQL Server but never reviewed, redundant or duplicate indexes that serve no purpose, and fragmented indexes that increase I/O. A systematic index review identifies what to add, what to remove, and what to maintain.
- Missing index identification via sys.dm_db_missing_index_details
- Duplicate and redundant index audit
- Index fragmentation assessment (sys.dm_db_index_physical_stats)
- Index usage statistics review — finding unused indexes
- Covering index design for high-impact queries
- Rebuild vs reorganise strategy for maintenance
TempDB Analysis
TempDB is a shared resource used by all databases on an instance. Problems in TempDB — allocation page contention, version store growth, spill operations — affect all workloads simultaneously and can be difficult to attribute to a specific query or session without careful analysis.
- TempDB file configuration review (data file count vs core count)
- Allocation page contention (PAGELATCH_EX/SH waits on pages 2, 3, 4)
- Version store size and growth monitoring
- Spill detection from sort and hash operations in execution plans
- Temporary object usage patterns — temp tables vs table variables vs CTEs
Replication Troubleshooting
SQL Server replication latency and failures have specific causes that are identifiable through agent profiling, distribution database analysis and subscriber-side investigation. Replication falling behind during peak load is a common pattern with well-understood root causes.
- Distribution agent and log reader agent profiling
- Latency measurement and historical trending
- Distribution database maintenance and growth review
- Subscriber index and performance review
- Replication agent configuration analysis
- Subscriber data drift investigation
- Replication error log analysis
SQL Server Configuration Review
Many SQL Server performance problems are caused or amplified by configuration choices made at installation and never revisited. Max server memory, MAXDOP, cost threshold for parallelism, I/O configuration — each has a significant impact on how SQL Server behaves under load.
- Max server memory configuration
- MAXDOP and cost threshold for parallelism
- Instant file initialisation and lock pages in memory
- I/O subsystem review — tempdb on separate drive, data/log separation
- Auto-shrink and auto-growth event review
- Compatibility level and its effect on query optimiser behaviour
Database Growth & Storage
Log file growth, data file autogrowth events, and the consequences of file shrink operations are recurring causes of SQL Server performance problems. Understanding how the database has grown and how storage is managed is an important part of any performance assessment.
- Log file growth and VLF count review
- Autogrowth event history
- Auto-shrink consequences and fragmentation
- Large table and index space usage
- Backup and recovery model review
Performance Monitoring & Baselines
Without a performance baseline, it is impossible to determine when performance changed or by how much. Query Store, when configured correctly, provides the most useful historical performance data. Establishing what normal looks like is a prerequisite for diagnosing what abnormal means.
- Query Store configuration and review
- Wait statistics baseline and analysis (sys.dm_os_wait_stats)
- DMV-based performance monitoring approach
- Identifying performance regression patterns
- Extended Events for targeted problem capture
Have a SQL Server Problem?
Describe the problem — what is happening, how long it has been happening, and what has already been tried. Conceptlab will assess whether a diagnostic engagement is the right next step.
Discuss Your SQL Server Problem