SQLskills SQL101: Why do some wait types need to be ignored?

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

Wait statistics analysis is one of my favorite things to talk about because it’s so incredibly useful for performance tuning and can dramatically shorten the time it takes to zero in on the root cause of a performance problem. But you have to do it correctly. You can’t just do a SELECT * FROM sys.dm_os_wait_stats. Various people have published scripts online to aggregate and display wait statistics in an actionable way, and my script is one of the most popular (latest version is always in this post).

One question I’m often asked is why does my script have a list of wait types that it specifically filters out? The answer is that those wait types are what I call ‘benign’ – they’re usually not a problem but happen frequently enough from regular SQL Server operations that they would show up as the top waits and so would obscure the waits that you can do something about.

For instance, if I take my waits script and remove all the filtering of benign waits, the results on my laptop where I’m forcing a tempdb contention problem are as follows:

WaitType                            Wait_S    Resource_S  Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
----------------------------------- --------- ----------  -------- --------- ---------- --------- -------- -------- -----------------------------------------------------------------------
SLEEP_TASK                          123335.21 123326.43   8.77     5232828   10.68      0.0236    0.0236   0.0000   https://www.sqlskills.com/help/waits/SLEEP_TASK
DIRTY_PAGE_POLL                     82215.60  82214.61    0.98     808502    7.12       0.1017    0.1017   0.0000   https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
HADR_FILESTREAM_IOMGR_IOCOMPLETION  82215.08  82214.43    0.65     163809    7.12       0.5019    0.5019   0.0000   https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
LOGMGR_QUEUE                        82213.89  82210.58    3.31     669980    7.12       0.1227    0.1227   0.0000   https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
SQLTRACE_INCREMENTAL_FLUSH_SLEEP    82212.97  82212.94    0.03     20546     7.12       4.0014    4.0014   0.0000   https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
REQUEST_FOR_DEADLOCK_SEARCH         82212.74  0.00        82212.74 16442     7.12       5.0002    0.0000   5.0002   https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
LAZYWRITER_SLEEP                    82210.41  82209.82    0.59     86524     7.12       0.9501    0.9501   0.0000   https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
CHECKPOINT_QUEUE                    82204.96  82204.92    0.04     125       7.12       657.6396  657.6394 0.0003   https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
XE_TIMER_EVENT                      82204.08  0.00        82204.08 37409     7.12       2.1974    0.0000   2.1974   https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
QDS_CLEANUP_STALE_QUERIES_TASK_
                   MAIN_LOOP_SLEEP  82201.37  82201.36    0.01     1371      7.12       59.9572   59.9572  0.0000   https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
QDS_PERSIST_TASK_MAIN_LOOP_SLEEP    82201.29  82201.28    0.01     1371      7.12       59.9572   59.9572  0.0000   https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
SP_SERVER_DIAGNOSTICS_SLEEP         82200.36  0.00        82200.36 299612    7.12       0.2744    0.0000   0.2744   https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
XE_DISPATCHER_WAIT                  82198.10  82198.10    0.00     686       7.12       119.8223  119.8223 0.0000   https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT

All of these are benign waits happening on system threads. For instance, you can see that the lazy writer thread on my instance is waking up every 1 second or so to check for memory pressure in the buffer pool, finding none, and then sleeping again (that’s the LAZYWRITER_SLEEP wait type, showing an average of 0.95s average resource wait time in the AvgRes_S column). You can also see that nearly all of these waits have a total wait time of around 82, 200 seconds, which is how long my laptop has been running since its last reboot.

The point is that these waits always occur and if you don’t filter them out, they will show up as the most prevalent wait types on your instance, and they’re not related to performance issues.

When I put the filters back in, and re-run the script, I get the following output:

WaitType                            Wait_S    Resource_S  Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
----------------------------------- --------- ----------  -------- --------- ---------- --------- -------- -------- -----------------------------------------------------------------------
PAGELATCH_UP                        3451.97   3312.34     139.63   502282    56.73      0.0069    0.0066   0.0003   https://www.sqlskills.com/help/waits/PAGELATCH_UP
PAGELATCH_SH                        2324.96   1449.37     875.60   2030686   38.21      0.0011    0.0007   0.0004   https://www.sqlskills.com/help/waits/PAGELATCH_SH
LATCH_EX                            217.89    214.96      2.94     7628      3.58       0.0286    0.0282   0.0004   https://www.sqlskills.com/help/waits/LATCH_EX

Now I can see an indication of a problem and know to go look at my sys.dm_os_waiting_tasks script (latest version always here) for further investigation (and note the automatically-generated URLs which will take you to the relevant page of my waits library for explanation of the wait types and troubleshooting advice).

Bottom line: always make sure you’re filtering out benign wait types so you’re not trying to troubleshoot a problem that you can’t do anything about.

SQLskills SQL101: Why does repair invalidate replication subscriptions?

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

Whenever I’m teaching and recovering from corruption, I always stress that if the REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB (which I’ll just call ‘repair’ from now on) has to be used, you should do the following:

  • Run another DBCC CHECKDB to make sure that repair fixed everything and no new problems were exposed (by the repair fixing something that was blocking DBCC CHECKDB‘s access to a portion of the database)
  • Run DBCC CHECKCONSTRAINTS on any tables or indexes that were repaired and are involved in constraints (stated in Books Online too)
  • Reinitialize any replication subscriptions that are based on tables that were repaired (stated in Books Online too)
  • Reevaluate your HA/DR strategy so you don’t have to use repair again in future

One question I’m often asked is about why replication can be broken by running repair.

There are two ways that replication can be affected: repairs on replication metadata tables, and repairs on anything else to do with a subscription.

Repairs on replication metadata tables

This is the simplest case to explain. If the repair operation affects any of the replication metadata tables (i.e. deleted some data from them), the entire replication publication will be in an inconsistent state and you should remove replication completely from the database an reinitialize it. This isn’t limited to a single subscription – all replication should be reconfigured.

Repairs on anything else

Transaction replication captures changes to the publication database by analyzing the transaction log, looking for transactions that change data in any of the publications, and converting those operations into logical operations that can be applied to the subscribers. Merge replication captures changes to the publication database using DML triggers and converting those operations into logical operations that can be applied to the subscribers.

Neither of these mechanisms can capture of operations performed by repair. Repair operations are always direct physical changes to the database structures to fix inconsistencies in the structures (e.g. a database page, table record, or a linkage between two pages), as opposed to physical changes because of queries performing inserts, updates, or deletes on tables.

These repair operations cannot translated into logical operations that can be applied to replication subscribers because there are no logical operations than can be expressed using T-SQL for the equivalent of the direct structural changes that repair is performing. Replication does not preserve the exact physical location of a particular record between the publication and subscription databases, so a direct change to record Y on page X in the publication database would not be able to be replayed on the subscription database (remember, replication ships logical changes, not physical changes). This means that if any table that is part of a replication publication is changed by a repair operation, the replication subscription is no longer valid and must be reinitialized.

As an example, imagine a repair operation is forced to remove a data page from a unique clustered index (essentially deleting some table records), and the subscription is NOT reinitialized. Those records would still exist on the replicated copy of the table. If a subsequent insert operation inserted records with cluster key values corresponding to the records deleted by the repair operation, the Distribution Agent would fail when trying to apply the inserts to the replicated copy – because the repair operation was not applied to the subscription database and a duplicate key violation error will occur when attempting to apply the insert to the replicated table.

A replication subscription must always be reinitialized if any table in the publication is affected by a repair operation, or the replication metadata tables are repaired.

Thanks

SQLskills SQL101: Is the recovery interval guaranteed?

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

One of the concepts I find people misunderstand frequently is the recovery interval, either for the server as a whole or the per-database setting that was introduced in SQL Server 2012 for indirect checkpoints.

There are two misconceptions here:

  1. The recovery interval equals how often a checkpoint will occur
  2. SQL Server guarantees the recovery interval (i.e. crash recovery for the database will only take the amount of time specified in the recovery interval)

A bit of background: crash recovery has two tasks to perform: replaying log records from committed transactions (called REDO) and removing the effect of log records from uncommitted transactions (called UNDO). REDO only has to occur if there have been committed transactions where the changed data pages have not been written to disk (which is done by periodic checkpoints or a lazy writer background thread if there’s memory pressure on the buffer pool).

The recovery interval specifies an ideal upper bound on how long the REDO portion of crash recovery will take. The length of time REDO takes depends on how many log records need to be replayed from committed transactions. To help REDO stay on track, the recovery interval setting forces a checkpoint to occur when a certain number of log records have been generated (a number calculated using the chosen recovery interval).

So although recovery interval *does* control how often a checkpoint occurs, the recovery interval time does not equal the time between checkpoints.

For instance, if the recovery interval is set to one minute (the default), and a checkpoint occurs *now*, when will the next checkpoint occur? If activity in the database is very infrequent, a checkpoint may not occur for a very long time because the calculated recovery time for that activity will be less than one minute. If activity in the database is extremely high, checkpoints may occur every few seconds. Checkpoint frequency entirely depends on how fast log records are being generated *and* the recovery interval setting.

And of course SQL Server cannot *guarantee* the recovery interval. Imagine the recovery interval is set to one minute, and I start a transaction that updates millions of records over several hours. If SQL Server crashes just before the transaction commits, how long do you think it will take to roll back the millions of updates? Certainly it will take much longer than one minute – the configured recovery interval.

So I can update my definition to be: the recovery interval is therefore the ideal upper bound on how long the REDO portion of crash recovery will take, assuming no long-running transactions.

Hope this helps clear up any misconceptions.