The Theme Park Analogy
A friend once told me about his trip to Universal Studios: how packed it was, and how it took forever just to get on anything.
His experience basically came down to two kinds of time: the time that actually felt fun and worthwhile (walking around and being on the rides), and the time that felt like dead time (standing around waiting to get in).
When a user application submits a request to SQL Server for data, the same dynamic plays out. Query response time includes CPU processing time, when the query is actually being productive (reading data, processing it, and sending it back), and time spent waiting on a particular resource (disk I/O, memory, network, locking, and so on). In a concurrent database system, with hundreds or thousands of user requests competing for limited resources, waiting is inevitable.
Performance Tuning Starts with Wait Statistics
Sticking with the analogy: there are things visitors and the theme park can do to enhance the overall experience. Visitors may plan their trip on weekdays when things are less busy and plan ahead so that they can get the most out of the day. The theme park can study what contributes most to customers’ wait times and research solutions accordingly.
In the SQL Server world, to reduce response time, there are two parallel levers we can pull:
- Reduce processing time by tuning slow queries: queries can't tune themselves yet (maybe someday, we hope), but for now that's still our job. Maybe the execution plan is inefficient because the query is too complex, maybe there’s a missing index, or maybe it reads so much data that it’s time to consider pagination.
- Reduce wait time: This means finding the bottleneck, what resources the server is waiting on, and directing our tuning efforts accordingly.
When SQL Server as a whole is slow, analyzing wait statistics is an effective way to find the primary reasons queries are waiting, so we can focus our tuning efforts on the right problem. Once we’ve identified the bottlenecks, there are many things we can do to relieve them, depending on the problems we find: add resources, tune queries and indexes, or reconfigure settings.
The Resource Waits
For every wait that happens, SQL Server records the length of the wait and its cause, aka the wait type, which generally tells us what resource the request was waiting on. We can find this data in the sys.dm_os_wait_stats DMV. This DMV records cumulative totals for each wait type since the last SQL Server restart (or since wait stats were manually cleared). I use it to establish a wait baseline chart, which I’ll discuss in more detail below.
Not all waits are meaningful. There are problematic waits and benign waits, and we should filter the benign ones out during analysis. The "More to Read" section at the end links to a blog post by Brent Ozar and a white paper by SQLSkills that cover different wait types in more detail.
Identifying the top waits is only half the battle. Once you know what your server is waiting on, the next question is: what do you actually do about it? I'll be dedicating separate blog posts to the most common wait types I encounter and how I approach troubleshooting each one. Stay tuned for those as this series grows.
The Baseline
If you use First Responder Kit , you can get a quick snapshot of the top waits since the last SQL Server startup with a single call:
exec sp_blitzfirst @sincestartup=1The downside is that you lose that history when SQL Server restarts. For long-term tracking, I needed something that would survive restarts and let me compare across days, weeks, and upgrades. So, I built my own wait stats chart.
The Chart
The chart tracks two performance metrics side by side:
- Batch requests/second (the red line): how many queries the server is handling.
- Wait Time (Second) per Core per Second (the bars): how much time (per core, per second) SQL Server spends waiting on specific wait types.
Having both metrics in one view gives me a few things I find valuable:
- More context: It’s not just “what are we waiting on?"; the Batch Requests/sec line shows how busy the server actually is, which helps separate "high waits because the server is slammed" from "high waits because something is broken."
- Baseline and comparison: I can use it to establish a server performance baseline for things like upgrading SQL Server to a newer version. I can spot anomaly periods where wait time is significantly higher and quickly see which waits are driving the change. For example, at the end of Jan 27 and the beginning of Jan 28, the server had an abnormal increase in CXPACKET waits. After tuning the query that contributed to it, we see a drop by mid-day on Jan 28.
Building the chart
The chart above is powered by a SQL Server Agent job that snapshots wait stats and batch request counters at regular intervals. Here's what the script does at a high level:
It creates a set of tables under a perftune schema to store snapshots and computed deltas. Every time the job fires, it captures the current cumulative counters, computes the difference from the last run, and normalizes the results. One year of history is retained by default.
If you want to build a similar chart:
- Create a SQL Server Agent job that runs every 15 minutes (or whatever interval you want; just make sure to change the @n value in the script too).
- Replace <YourDatabase> with the database where you want to store the monitoring tables
- The script below keeps one year of history by default; you can change that retention window
- Batch Requests/sec: The script reads the current cumulative “Batch Requests/sec” perf counter from sys.dm_os_performance_counters, subtracts the last saved counter value, then divides by the elapsed time window (@delta = @n * 60). BatchRequestsPerSecond = (NewCounterValue - LastCounterValue) / @delta_seconds
- Wait per core per second: The script snapshots sys.dm_os_wait_stats, computes resource wait time as wait_time_ms - signal_wait_time_ms, then takes the delta from the prior snapshot. That delta is converted to seconds, divided by CPU core count, and divided by the time window. WaitSecondsPerCorePerSecond = ((NewResourceWaitMs - LastResourceWaitMs) / 1000) / @delta_seconds / cores
For the visualization itself, I use Power BI connected directly to the monitoring tables. Once the Agent job is running and data starts accumulating, it's straightforward to point Power BI at the perftune.WaitStatsTop10 and perftune.BatchRequestsPerSecond tables and build the stacked bar chart and line overlay from there.
Below is the full script for the agent job. I've commented it inline so you can follow along:
USE <YourDatabase>;
GO
/*------------------------------------------------------------------------------
PERFTUNE COLLECTION SCRIPT
Runs as a recurring job (every @n minutes; default 15).
Captures:
1) Batch Requests/sec (per-interval delta from prior run)
2) Top 10 *resource* waits (seconds/core/second) using deltas from the
previous wait snapshot
How deltas are calculated:
- Batch Requests/sec:
(NewCounterValue - LastCounterValue) / @delta_seconds
- Resource waits:
((NewResourceWaitMs - LastResourceWaitMs) / 1000) / @delta_seconds / cores
Notes:
- First run seeds baseline counters; delta-based results require a prior run.
- CollectionDate is rounded down to the minute (HH:mm:00) for consistent keys.
- History is retained for 365 days (see cleanup section).
------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------
1) BOOTSTRAP PERSISTENT OBJECTS (schema + tables)
------------------------------------------------------------------------------*/
IF NOT EXISTS (
SELECT 1
FROM sys.schemas
WHERE name = N'perftune'
)
BEGIN
EXEC('CREATE SCHEMA perftune');
END
GO
IF OBJECT_ID(N'perftune.LastBatchRequestsCount', N'U') IS NULL
BEGIN
/* Stores last observed cumulative Batch Requests/sec counter value
to compute a per-interval delta on subsequent runs. */
CREATE TABLE perftune.LastBatchRequestsCount
(
LastBatchRequestsCount bigint
)
INSERT INTO perftune.LastBatchRequestsCount VALUES (0)
END
GO
IF OBJECT_ID(N'perftune.BatchRequestsPerSecond', N'U') IS NULL
BEGIN
/* Time series of calculated Batch Requests/sec per collection interval. */
CREATE TABLE perftune.BatchRequestsPerSecond
(
CollectionDate datetime NOT NULL PRIMARY KEY,
BatchRequestsPerSecond int
)
END
GO
IF OBJECT_ID(N'perftune.LastWaitStats', N'U') IS NULL
BEGIN
/* Stores the most recent wait snapshot (one row per wait type).
Truncated/reloaded each run; used to compute per-interval deltas. */
CREATE TABLE perftune.LastWaitStats
(
CollectionDate datetime NOT NULL,
WaitType NVARCHAR(100) NOT NULL,
WaitTimeMs numeric(19,6),
PRIMARY KEY (WaitType)
)
END
GO
IF OBJECT_ID(N'perftune.WaitStatsTop10', N'U') IS NULL
BEGIN
/* Time series of top 10 resource-wait deltas per interval,
normalized to seconds/core/second. */
CREATE TABLE perftune.WaitStatsTop10
(
CollectionDate datetime not null,
WaitType NVARCHAR(100) not null,
WaitTimeSecondPerCorePerSecond numeric(19,6),
PRIMARY KEY (CollectionDate, WaitType)
)
END
GO
/*------------------------------------------------------------------------------
2) COLLECT CURRENT INTERVAL SNAPSHOT
------------------------------------------------------------------------------*/
DECLARE @n as int = 15;
DECLARE @delta as decimal(10,2) = @n*60.;
-- Number of logical CPU cores (used for per-core normalization)
DECLARE @NumberOfCores INT =
(
SELECT cpu_count
FROM sys.dm_os_sys_info
);
-- Collection date/time (rounded down to the minute boundary)
DECLARE @CollectionDate DATETIME2(0) = SYSDATETIME();
SET @CollectionDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @CollectionDate), 0);
SELECT @CollectionDate AS CollectionDate;
----------------------------------------------------------------
-- Batch Requests/sec (delta from prior run)
----------------------------------------------------------------
DECLARE @OldBatchRequestsCount BIGINT =
(
SELECT TOP (1) LastBatchRequestsCount
FROM perftune.LastBatchRequestsCount
);
DECLARE @NewBatchRequestsCount BIGINT =
(
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%:SQL Statistics%'
AND counter_name = 'Batch Requests/sec'
);
IF (@OldBatchRequestsCount>0)
BEGIN
INSERT INTO perftune.BatchRequestsPerSecond
(
CollectionDate,
BatchRequestsPerSecond
)
SELECT
CAST(@CollectionDate AS DATETIME) AS CollectionDate,
(@NewBatchRequestsCount - @OldBatchRequestsCount) / @delta AS BatchRequestsPerSecond
WHERE (@NewBatchRequestsCount - @OldBatchRequestsCount) / @delta > 0;
END
-- Persist the latest cumulative counter value for the next run’s delta
UPDATE perftune.LastBatchRequestsCount
SET LastBatchRequestsCount = @NewBatchRequestsCount;
----------------------------------------------------------------
-- Wait Stats (resource waits only); capture current snapshot
-- ResourceWaitTimeMs = wait_time_ms - signal_wait_time_ms
----------------------------------------------------------------
DROP TABLE IF EXISTS #NewWaitStats;
CREATE TABLE #NewWaitStats
(
CollectionDate DATETIME2(0) NOT NULL,
WaitType NVARCHAR(100) NOT NULL,
WaitTimeMs NUMERIC(19,6) NOT NULL,
CONSTRAINT PK_NewWaitStats PRIMARY KEY (WaitType)
);
INSERT INTO #NewWaitStats
(
CollectionDate,
WaitType,
WaitTimeMs
)
SELECT
@CollectionDate AS CollectionDate,
wait_type AS WaitType,
(wait_time_ms - signal_wait_time_ms) AS ResourceWaitTimeMs
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count <> 0
AND wait_type NOT IN
(
-- These wait types are almost 100% never a problem and so they are
-- filtered out to avoid them skewing the results. Click on the URL
-- for more information.
N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE
-- Maybe comment this out if you have parallelism issues
N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER
-- Maybe comment these four out if you have mirroring issues
N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD
N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
N'FT_IFTS_SCHEDULER_IDLE_WAIT',-- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX
-- Maybe comment these six out if you have AG issues
N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE
N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER
N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE
N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST
N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC
N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK
N'PREEMPTIVE_OS_FLUSHFILEBUFFERS', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_FLUSHFILEBUFFERS
N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
N'PVS_PREALLOCATE', -- https://www.sqlskills.com/help/waits/PVS_PREALLOCATE
N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
N'PWAIT_EXTENSIBILITY_CLEANUP_TASK', -- https://www.sqlskills.com/help/waits/PWAIT_EXTENSIBILITY_CLEANUP_TASK
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
N'SOS_WORK_DISPATCHER', -- https://www.sqlskills.com/help/waits/SOS_WORK_DISPATCHER
N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
N'VDI_CLIENT_OTHER', -- https://www.sqlskills.com/help/waits/VDI_CLIENT_OTHER
N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
);
----------------------------------------------------------------
-- Top 10 waits by delta since last snapshot
-- Convert ms delta to seconds, then normalize by interval duration and cores:
-- (delta_WaitTimeMs / 1000) / @delta / cores
----------------------------------------------------------------
IF ((SELECT COUNT(*) FROM perftune.LastWaitStats) <> 0)
BEGIN
INSERT INTO perftune.WaitStatsTop10
(
CollectionDate,
WaitType,
WaitTimeSecondPerCorePerSecond
)
SELECT TOP (10)
n.CollectionDate,
n.WaitType,
(n.WaitTimeMs - l.WaitTimeMs) / 1000.0 / @delta / NULLIF(@NumberOfCores, 0)
AS WaitTimeSecondPerCorePerSecond
FROM #NewWaitStats AS n
INNER JOIN perftune.LastWaitStats AS l
ON n.WaitType = l.WaitType
WHERE (n.WaitTimeMs - l.WaitTimeMs) > 0
ORDER BY (n.WaitTimeMs - l.WaitTimeMs) DESC
END
GO
----------------------------------------------------------------
-- Persist latest wait snapshot for next run
----------------------------------------------------------------
TRUNCATE TABLE perftune.LastWaitStats;
INSERT INTO perftune.LastWaitStats
(
CollectionDate,
WaitType,
WaitTimeMs
)
SELECT
CollectionDate,
WaitType,
WaitTimeMs
FROM #NewWaitStats;
/*------------------------------------------------------------------------------
3) HISTORY RETENTION (delete rows older than 365 days)
------------------------------------------------------------------------------*/
DECLARE @CleanupNDatesOlder datetime;
SET @CleanupNDatesOlder = DATEADD(dd,-365,GETDATE());
-- perftune.WaitStatsAllTime
IF OBJECT_ID('perftune.WaitStatsTop10', 'U') IS NOT NULL
DELETE FROM perftune.WaitStatsTop10 WHERE CollectionDate < @CleanupNDatesOlder;
-- perftune.BatchRequestsPerSecond
IF OBJECT_ID('perftune.BatchRequestsPerSecond', 'U') IS NOT NULL
DELETE FROM perftune.BatchRequestsPerSecond WHERE CollectionDate < @CleanupNDatesOlder;
The Signal Wait
Every wait in SQL Server has two components: resource wait time and signal wait time. Resource wait time (wait_time_ms - signal_wait_time_ms) is how long a task waited for an external resource, a disk read, a lock, a memory grant, and so on. Signal wait time (signal_wait_time_ms) is how long the task waited to get back on a CPU after the resource became available. In other words, it was ready to run but had to wait in line for a CPU to pick it up.
Both matter, but they point to different problems. High resource waits tell you what the server is waiting on: slow storage, lock contention, memory pressure, etc. High signal waits tell you the CPUs are too busy to keep up; tasks are ready to work but there's no core available to run them.
In this post, we focus on resource waits. I'll cover signal waits and CPU scheduling pressure in a future post.
Conclusion
SQL Server performance tuning has a lot in common with surviving a packed theme park: some time is real work (riding the rides), and some is just waiting in line. Wait stats let us see where SQL Server is spending that "line time," so we can stop guessing and focus on the actual bottleneck.
The baseline chart is my way of keeping that visibility over time, so when things change, new workload, code deploys, upgrades, or just a bad day, I can quickly spot what shifted and why.
The takeaway: if your SQL Server is slow and you don't know where to start, start with wait stats. They won't tell you everything, but they'll almost always point you in the right direction.
More to read
Wait Stats - Brent Ozar Unlimited®
sp_BlitzFirst @SinceStartup = 1 Shows You Wait Stats Since, Uh, Startup - Brent Ozar Unlimited®
How to Measure SQL Server Workloads: Wait Time per Core per Second - Brent Ozar Unlimited®
sql-server-performance-tuning-using-wait-statistics-whitepaper.pdf
If you spot anything off in the explanation (or have ideas to improve the script), I'd appreciate your feedback in the comments or via email. Thank you for reading and have a good day!
No comments:
Post a Comment