Troubleshootoing performance problems in sql server with sys.dm_os_wait_stats

The SQL Server DMV sys.dm_os_wait_stats is a well established DMV , used heavily in troubleshooting performance problems in SQL Server. The DMV is an aggregated view on all the sql server wait types . When a thread is executed part of the runtime are waits. sys.dm_os_wait_stats presents the wait type aggregates. It is also possible to break down the waits at a query level. It’s useful to understand the meaning of the columns. According to the MSDN web site Wait_time_ms Total = wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms signal_wait_time_ms = Difference between... Read more →

Guiding principles of wait time analysis

Question: Could you send me some information about the guiding principles of wait time analysis? Answer: Wait time analysis for SQL Server performance tuning is a methodology covering a wide range of problem sets. But at its core are some guiding principles. 1) Identify what the impact is on the end user 2) The main focus is on aiming to to make a positive impact on the application end user. This is in contrast to aiming to deliver a better cache hit ratio or cpu usage etc. This doesn’t mean that cache hit ratio and cpu usage are not important... Read more →

ASYNC_IO_COMPLETION during sql server backup

Question: I’m getting a very high level of ASYNC_IO_COMPLETION during a SQL Server backup schedule. These are very small databases , usually taking a few minutes, but now taking over 1 hr each. My suspicion is it’s an underlying storage issue, but I need a straightforward method to show the storage guys. Answer: This is one of those situations where it’s difficult to present evidence to the storage team . The usual definition of ASYNC_IO_COMPLETION is SQL Server Books online defines Async_io_completion as “Occurs when a task is waiting for I/Os to finish “. Read more on SQL Server Async_io_completion... Read more →

How to turn AUTO_SHRINK off

I run a daily health check across SQL Servers . One of the checks made is whether the SQL Server configurable AUTO_SHRINK is turned ON. Unless there is a clear reason why AUTO_SHRINK needs to be turned ON then keeping AUTO_SHRINK OFF can assist in keeping contention lower. As part of a SQL Server set up I use a SQL Server database files configuration checklist. One of the items on the list is AUTO_SHRINK OFF Configuring AUTO_SHRINK to OFF – means the files are not automatically shrunk – when checks occur for unused space. I execute an Shrink when a... Read more →

Show me the wait stats

Using wait stats to identify pressure points on your SQL Server is a common method for troubleshooting. Unfortunately , it is very easy to jump to conclusions about problems on the basis of the highest wait stats. My general approach is to use wait stats , but letting them guide me to extra analysis. There could be all sorts of reasons why a wait stat or a combination of wait stats appear . The more you use wait stats , you develop methods for troubleshooting. Interpreting the initial findings will help you start to ask the right question. These are... Read more →