'SQL Server is slow'. How many times have you heard that? One of the most common complaints raised during performance incidents, but in many cases, the statement doesn't hold up under analysis. More often than not, SQL Server is just responding to the workload it is being given, and the workload itself is the problem.
SQL Server does not guess. It shows us exactly where it's spending its time and why. If the issue is inside the engine, the data will show this. If it is not, it will show that just as clearly. The goal is to separate the maybes from the evidence and verify whether the bottleneck is within SQL Server or the application layer.
Start with Wait Statistics
The first step is to examine wait statistics, which provide a direct view into where SQL Server is spending its time waiting. If there is a bottleneck in IO, locking, or transaction log writes, it will be reflected within the waits.
SELECT TOP (10)
wait_type,
wait_time_ms,
signal_wait_time_ms,
waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TASK_STOP',
'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'CHKPT', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'CLR_SEMAPHORE',
'DBMIRROR_DBM_EVENT', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRROR_WORKER_QUEUE',
'DBMIRRORING_CMD', 'DIRTY_PAGE_POLL', 'DISPATCHER_QUEUE_SEMAPHORE',
'EXECSYNC', 'FSAGENT', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'FT_IFTSHC_MUTEX',
'HADR_CLUSAPI_CALL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_LOGCAPTURE_WAIT',
'HADR_NOTIFICATION_DEQUEUE', 'HADR_TIMER_TASK', 'HADR_WORK_QUEUE',
'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MEMORY_ALLOCATION_EXT',
'ONDEMAND_TASK_QUEUE', 'PARALLEL_REDO_DRAIN_WORKER', 'PARALLEL_REDO_LOG_CACHE',
'PARALLEL_REDO_TRAN_LIST', 'PARALLEL_REDO_WORKER_SYNC',
'PARALLEL_REDO_WORKER_WAIT_WORK', 'PREEMPTIVE_OS_FLUSHFILEBUFFERS',
'PREEMPTIVE_XE_GETTARGETSTATE', 'PWAIT_ALL_COMPONENTS_INITIALIZED',
'PWAIT_DIRECTLOGCONSUMER_GETNEXT', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
'QDS_ASYNC_QUEUE', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
'QDS_SHUTDOWN_QUEUE', 'REDO_THREAD_PENDING_WORK', 'REQUEST_FOR_DEADLOCK_SEARCH',
'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK', 'SLEEP_DBSTARTUP', 'SLEEP_DCOMSTARTUP',
'SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY', 'SLEEP_MASTERUPGRADED',
'SLEEP_MSDBSTARTUP', 'SLEEP_SYSTEMTASK', 'SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP',
'SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'SQLTRACE_BUFFER_FLUSH',
'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'SQLTRACE_WAIT_ENTRIES', 'WAIT_FOR_RESULTS',
'WAITFOR', 'WAITFOR_TASKSHUTDOWN', 'WAIT_XTP_RECOVERY', 'WAIT_XTP_HOST_WAIT',
'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'WAIT_XTP_CKPT_CLOSE', 'XE_DISPATCHER_JOIN',
'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;
The wait profile above is dominated by ASYNC_NETWORK_IO and SOS_SCHEDULER_YIELD, with no meaningful presence of PAGEIOLATCH, WRITELOG, or blocking-related waits. This is not a resource bottleneck inside SQL Server. It is a workload problem.
When the problem is within SQL Server, the top waits will typically point to resource pressure — PAGEIOLATCH_* for storage latency, WRITELOG for transaction log throughput, and LCK_* waits for blocking. These indicate that SQL Server is waiting on something it cannot process efficiently.
If those signals are not present, your focus should shift. One of the most important signs in the waits above is ASYNC_NETWORK_IO, which occurs when SQL Server is waiting for the client to consume result sets. This is not a database performance issue. It is a client or application behavior issue.
Verify That SQL Server Is Not the Bottleneck
At this point, you have the indicators, but you still need to verify. You need to confirm that SQL Server is executing efficiently and that the problem may be the workload pattern.
Start by verifying that the system is not waiting on core resources. If SQL Server is the bottleneck, you will see IO pressure, blocking, or reduced throughput. If you do not see those signals, the engine is not the limiting factor.
If these waits are not present in meaningful volume, SQL Server is not waiting on critical resources:
- PAGEIOLATCH_* - Storage latency
- WRITELOG - Transaction log bottleneck
- LCK_* - Blocking
Next, validate that queries are executing efficiently.
SELECT TOP (20)
qs.execution_count,
qs.total_worker_time / qs.execution_count AS avg_cpu,
qs.total_elapsed_time / qs.execution_count AS avg_duration,
qs.total_worker_time AS total_cpu,
SUBSTRING(qt.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.execution_count DESC;
The result set above shows queries executing hundreds of thousands to millions of times, with average durations ranging from tens of microseconds to a few milliseconds. These are not slow queries. SQL Server is executing them efficiently.
The problem is the volume. High execution counts combined with low per-execution cost indicate a high-frequency workload pattern. SQL Server is not struggling to process these requests — it's just being asked to process too many of them.
This is a classic application-driven pattern, often caused by row-by-row processing, excessive round trips, or ORM-generated query behavior. And this is a key distinction. A slow query is a SQL Server problem. A fast query executed millions of times is an application problem.
Finally, we must correlate this with what we saw in the wait statistics. High ASYNC_NETWORK_IO combined with high query execution counts further supports that SQL Server is not the point of contention. It is processing results quickly while the application is driving excessive requests or consuming results too slowly — or both.
High CPU Utilization
High CPU utilization is often interpreted as proof that SQL Server is struggling, but that conclusion is not always correct. CPU pressure must be evaluated in the context of wait statistics and workload patterns.
SELECT
SUM(signal_wait_time_ms) * 1.0 / SUM(wait_time_ms) * 100 AS signal_wait_pct
FROM sys.dm_os_wait_stats;
In this case, the signal wait percentage is approximately 18 percent, which indicates some CPU scheduling pressure. This is consistent with high request volume rather than resource exhaustion within SQL Server.
This aligns with the earlier observations. CPU utilization is stable, so the bottleneck does not appear to be within SQL Server. Rather than a resource bottleneck, the server is experiencing high request volume causing moderate scheduling pressure — which again points to the application layer.
Use Query Store to Confirm Behavior
Query Store provides historical context that can help us validate whether SQL Server behavior has changed. If execution plans are stable and average durations remain low, then the engine is operating consistently. Performance issues under those conditions are rarely caused by the SQL Server itself.
This is where Query Store can very quickly help us confirm whether SQL Server is executing queries predictably and the slowdown is being driven by changes in the workload. Be it row-by-row processing, excessive round trips, ORM-generated queries or aggressive retry logic — these are all application design characteristics, not database engine failures.
See this call on the Query Store statistics:
SELECT TOP (20)
qt.query_sql_text,
SUM(rs.count_executions) AS execution_count,
SUM(rs.count_executions * rs.avg_duration) / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS avg_duration_ms,
SUM(rs.count_executions * rs.avg_cpu_time) / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS avg_cpu_ms,
SUM(rs.count_executions * rs.avg_duration) / 1000.0 AS total_duration_ms
FROM sys.query_store_query_text qt JOIN sys.query_store_query q
ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan p
ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
GROUP BY qt.query_sql_text
ORDER BY execution_count DESC;
These Query Store stats validate what we saw earlier. Execution counts are high while average duration and CPU remain low. This means that SQL Server is executing efficiently. If those patterns are consistent over time, Query Store confirms that the engine behavior has not degraded. If users are reporting slowness under those conditions, the problem is not query performance or plan regression. It is a problem at the application layer.
Tie it all Together
When wait statistics don't indicate resource bottlenecks, query patterns reveal high execution frequency and Query Store shows stable execution behavior — the conclusion is straightforward: SQL Server is not the problem. The application is. Reducing call frequency, batch operations, and optimizing access patterns will have a far greater impact than tuning individual queries that are already performing efficiently.
More to Read:
Microsoft Docs: sys.dm_os_wait_stats
Brent Ozar: Wait Stats Explained
Paul Randal: Wait Statistics Deep Dive
Erik Darling: SOS_SCHEDULER_YIELD Explained
No comments:
Post a Comment