DMV revisited.. PART 1

For basic details please see dynamic-management-views
diagnostic script created to collect wait stats-
-- Isolate top waits for server instance since last restart or statistics clearWITH Waits AS(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rnFROM sys.dm_os_wait_statsWHERE wait_type NOT IN('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))SELECT W1.wait_type,CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,CAST(W1.pct AS DECIMAL(12, 2)) AS pct,CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pctFROM Waits AS W1INNER JOIN Waits AS W2ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pctHAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage thresholdGO

This script uses Dynamic Management View sys.dm_os_wait_stats to collect the wait stats. It omits the system-related wait stats which are not useful to diagnose performance-related bottleneck. Additionally, note OPTION (RECOMPILE) at the end of the DMV will ensure that every time the query runs, it retrieves new data and not the cached data.

This dynamic management view collects all the information since the time when the SQL Server services have been restarted. You can also manually clear the wait stats using the following command:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

when do the wait stats occur?

Here is the simple answer. When SQL Server is executing any task, and if for any reason it has to wait for resources to execute the task, this wait is recorded by SQL Server with the reason for the delay. Later on we can analyze these wait stats to understand the reason the task was delayed and maybe we can eliminate the wait for SQL Server. It is not always possible to remove the wait type 100%, but there are few suggestions that can help.

Before we continue learning about wait types and wait stats, we need to understand three important milestones of the query life-cycle.

Running - a query which is being executed on a CPU is called a running query. This query is responsible for CPU time.

Runnable – a query which is ready to execute and waiting for its turn to run is called a runnable query. This query is responsible for Signal Wait time. (In other words, the query is ready to run but CPU is servicing another query).

Suspended – a query which is waiting due to any reason (to know the reason, we are learning wait stats) to be converted to runnable is suspended query. This query is responsible for wait time. (In other words, this is the time we are trying to reduce).

In simple words, query execution time is a summation of the query Executing CPU Time (Running) + Query Wait Time (Suspended) + Query Signal Wait Time (Runnable). Again, it may be possible a query goes to all these stats multiple times.

I hope this analogy is bit clear with the wait stats. You can check the Signalwait stats using following query

-- Signal Waits for instanceSELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) ASNUMERIC(20,2))AS [%signal (cpu) waits],CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2))AS [%resource waits]FROM sys.dm_os_wait_stats OPTION (RECOMPILE);

Higher the Signal wait stats are not good for the system. Very high value indicates CPU pressure. In my experience, when systems are running smooth and without any glitch the Signal wait stat is lower than 20%. Again, this number can be debated (and it is from my experience and is not documented anywhere). In other words, lower is better and higher is not good for the system.

The key Dynamic Management View (DMV) that helps us to understand wait stats is sys.dm_os_wait_stats; this DMV gives us all the information that we need to know regarding wait stats. This is the statement which has inspired me to write this series.

Let us first run the following statement from DMV.

SELECT *FROM sys.dm_os_wait_statsORDER BY wait_time_ms DESCGO

wait_type – this is the name of the wait type. There can be three different kinds of wait types – resource, queue and external.

waiting_tasks_count – this incremental counter is a good indication of frequent the wait is happening. If this number is very high, it is good indication for us to investigate that particular wait type. It is quite possible that the wait time is considerably low, but the frequency of the wait is much high.

wait_time_ms – this is total wait accumulated for any type of wait. This is the total wait time and includes singal_wait_time_ms.

max_wait_time_ms – this indicates the maximum wait type ever occurred for that particular wait type. Using this, one can estimate the intensity of the wait type in past. Again, it is not necessary that this max wait time will occur every time; so do not over invest yourself here.

signal_wait_time_ms – this is the wait time when thread is marked as runnable and it gets to the running state. If the runnable queue is very long, you will find that this wait time becomes high.

Additionally, please note that this DMV does not show current wait type or wait stats. This is cumulative view of the all the wait stats since server (instance) restarted or wait stats have been cleared.

In future blog post, we will also cover two more DMVs which can be helpful to identify wait-related issues.

sys.dm_os_waiting_tasks
sys.dm_exec_requests

This DMV is written taking the following into consideration: we want to analyze the queries that are currently running or which have recently ran and their plan is still in the cache.

SELECT dm_ws.wait_duration_ms,dm_ws.wait_type,dm_es.status,dm_t.TEXT,dm_qp.query_plan,dm_ws.session_ID,dm_es.cpu_time,dm_es.memory_usage,dm_es.logical_reads,dm_es.total_elapsed_time,dm_es.program_name,DB_NAME(dm_r.database_id) DatabaseName,-- Optional columnsdm_ws.blocking_session_id,dm_r.wait_resource,dm_es.login_name,dm_r.command,dm_r.last_wait_typeFROM sys.dm_os_waiting_tasks dm_wsINNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id =dm_r.session_idINNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id =dm_r.session_idCROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_tCROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qpWHERE dm_es.is_user_process = 1
GO

You can change CROSS APPLY to OUTER APPLY if you want to see all the details which are omitted because of the plan cache.

Let us analyze the result of the above query and see how it can be helpful to identify the query and the kind of wait type it creates.

The above query will return various columns. There are various columns that provide very important details. e.g.

wait_duration_ms – it indicates current wait for the query that executes at that point of time.

wait_type – it indicates the current wait type for the query

text – indicates the query text

query_plan – when clicked on the same, it will display the query plans

There are many other important information like CPU_time, memory_usage, and logical_reads, which can be read from the query as well.

I use the following method to measure the wait stats over the time.

-- Create TableCREATE TABLE [MyWaitStatTable]([wait_type] [nvarchar](60) NOT NULL,[waiting_tasks_count] [bigint] NOT NULL,[wait_time_ms] [bigint] NOT NULL,[max_wait_time_ms] [bigint] NOT NULL,[signal_wait_time_ms] [bigint] NOT NULL,[CurrentDateTime] DATETIME NOT NULL,[Flag] INT)GO-- Populate Table at Time 1INSERT INTO MyWaitStatTable([wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],[CurrentDateTime],[Flag])SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],GETDATE(), 1FROM sys.dm_os_wait_stats
GO----- Desired Delay (for one hour) WAITFOR DELAY '01:00:00'
-- Populate Table at Time 2INSERT INTO MyWaitStatTable([wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],[CurrentDateTime],[Flag])SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],GETDATE(), 2FROM sys.dm_os_wait_stats
GO-- Check the difference between Time 1 and Time 2SELECT T1.wait_type, T1.wait_time_ms Original_WaitTime,T2.wait_time_ms LaterWaitTime,
(T2.wait_time_ms - T1.wait_time_ms) DiffenceWaitTimeFROM MyWaitStatTable T1INNER JOIN MyWaitStatTable T2 ON T1.wait_type = T2.wait_typeWHERE T2.wait_time_ms > T1.wait_time_msAND T1.Flag = 1 AND T2.Flag = 2ORDER BY DiffenceWaitTime DESCGO-- Clean upDROP TABLE MyWaitStatTable
GO

If you notice the script, I have used an additional column called flag. I use it to find out when I have captured the wait stats and then use it in my SELECT query to SELECT wait stats related to that time group. Many times, I select more than 5 or 6 different set of wait stats and I find this method very convenient to find the difference between wait stats.

CXPACKET has to be most popular one of all wait stats. I have commonly seen this wait stat as one of the top 5 wait stats in most of the systems with more than one CPU. CXPACKET Explanation:

When a parallel operation is created for SQL Query, there are multiple threads for a single query. Each query deals with a different set of the data (or rows). Due to some reasons, one or more of the threads lag behind, creating the CXPACKET Wait Stat. There is an organizer/coordinator thread (thread 0), which takes waits for all the threads to complete and gathers result together to present on the client’s side. The organizer thread has to wait for the all the threads to finish before it can move ahead. The Wait by this organizer thread for slow threads to complete is called CXPACKET wait.

Note that not all the CXPACKET wait types are bad. You might experience a case when it totally makes sense. There might also be cases when this is unavoidable. If you remove this particular wait type for any query, then that query may run slower because the parallel operations are disabled for the query.

Reducing CXPACKET wait:

We cannot discuss about reducing the CXPACKET wait without talking about the server workload type.

OLTP: On Pure OLTP system, where the transactions are smaller and queries are not long but very quick usually, set the “Maximum Degree of Parallelism” to 1 (one). This way it makes sure that the query never goes for parallelism and does not incur more engine overhead.

EXEC sys.sp_configure N'max degree of parallelism', N'1'GORECONFIGURE WITH OVERRIDE
GO

Data-warehousing / Reporting server: As queries will be running for long time, it is advised to set the “Maximum Degree of Parallelism” to 0 (zero). This way most of the queries will utilize the parallel processor, and long running queries get a boost in their performance due to multiple processors.

EXEC sys.sp_configure N'max degree of parallelism', N'0'GORECONFIGURE WITH OVERRIDE
GO

Mixed System (OLTP & OLAP): Here is the challenge. The right balance has to be found. I have taken a very simple approach. I set the “Maximum Degree of Parallelism” to 2, which means the query still uses parallelism but only on 2 CPUs. However, I keep the “Cost Threshold for Parallelism” very high. This way, not all the queries will qualify for parallelism but only the query with higher cost will go for parallelism. I have found this to work best for a system that has OLTP queries and also where the reporting server is set up.

Here, I am setting ‘Cost Threshold for Parallelism’ to 25 values (which is just for illustration); you can choose any value, and you can find it out by experimenting with the system only. In the following script, I am setting the ‘Max Degree of Parallelism’ to 2, which indicates that the query that will have a higher cost (here, more than 25) will qualify for parallel query to run on 2 CPUs. This implies that regardless of the number of CPUs, the query will select any two CPUs to execute itself.

EXEC sys.sp_configure N'cost threshold for parallelism', N'25'GOEXEC sys.sp_configure N'max degree of parallelism', N'2'GORECONFIGURE WITH OVERRIDE
GO

Best Practices to Reduce CXPACKET wait:

Refer earlier article regarding MAXDOP and Cost Threshold.
De-fragmentation of Index can help as more data can be obtained per page. (Assuming close to 100 fill-factor)
If data is on multiple files which are on multiple similar speed physical drive, the CXPACKET wait may reduce.
Keep the statistics updated, as this will give better estimate to query optimizer when assigning threads and dividing the data among available threads. Updating statistics can significantly improve the strength of the query optimizer to render proper execution plan. This may overall affect the parallelism process in positive way.

Bad Practice:

In one of the recent consultancy project, when I was called in I noticed that one of the ‘experienced’ DBA noticed higher CXPACKET wait and to reduce them, he has increased the worker threads. The reality was increasing worker thread has lead to many other issues. With more number of the threads, more amount of memory was used leading memory pressure. As there were more threads CPU scheduler faced higher ‘Context Switching’ leading further degrading performance. When I explained all these to ‘experienced’ DBA he suggested that now we should reduce the number of threads. Not really! Lower number of the threads may create heavy stalling for parallel queries. I suggest NOT to touch the setting of number of the threads when dealing with CXPACKET wait.

SOS_SCHEDULER_YIELD Explanation:

SQL Server has multiple threads, and the basic working methodology for SQL Server is that SQL Server does not let any “runnable” thread to starve. Now let us assume SQL Server OS is very busy running threads on all the scheduler. There are always new threads coming up which are ready to run (in other words, runnable). Thread management of the SQL Server is decided by SQL Server and not the operating system. SQL Server runs on non-preemptive mode most of the time, meaning the threads are co-operative and can let other threads to run from time to time by yielding itself. When any thread yields itself for another thread, it creates this wait. If there are more threads, it clearly indicates that the CPU is under pressure.

You can fun the following DMV to see how many runnable task counts there are in your system.

SELECT scheduler_id, current_tasks_count, runnable_tasks_count,work_queue_count, pending_disk_io_countFROM sys.dm_os_schedulersWHERE scheduler_id < 255
GO

If you notice a two-digit number in runnable_tasks_count continuously for long time (not once in a while), you will know that there is CPU pressure. The two-digit number is usually considered as a bad thing; you can read the description of the above DMV over here.

Additionally, there are several other counters (%Processor Time and other processor related counters), through which you can refer to so you can validate CPU pressure along with the method explained above.

Reducing SOS_SCHEDULER_YIELD wait:

This is the trickiest part of this procedure. As discussed, this particular wait type relates to CPU pressure. Increasing more CPU is the solution in simple terms; however, it is not easy to implement this solution. There are other things that you can consider when this wait type is very high. Here is the query where you can find the most expensive query related to CPU from the cache

Note: The query that used lots of resources but is not cached will not be caught here.

SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.TEXT)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2)+1),qs.execution_count,qs.total_logical_reads, qs.last_logical_reads,qs.total_logical_writes, qs.last_logical_writes,qs.total_worker_time,qs.last_worker_time,qs.total_elapsed_time/1000000 total_elapsed_time_in_S,qs.last_elapsed_time/1000000 last_elapsed_time_in_S,qs.last_execution_time,qp.query_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY qs.total_worker_time DESC -- CPU time

You can find the most expensive queries that are utilizing lots of CPU (from the cache) and you can tune them accordingly. Moreover, you can find the longest running query and attempt to tune them if there is any processor offending code.

Additionally, pay attention to total_worker_time because if that is also consistently higher, then the CPU under too much pressure.

You can also check perfmon counters of compilations as they tend to use good amount of CPU. Index rebuild is also a CPU intensive process but we should consider that main cause for this query because that is indeed needed on high transactions OLTP system utilized to reduce fragmentations.

PAGEIOLATCH_XX is such a kind of those wait stats that we would directly like to blame on the underlying subsystem. Of course, most of the time, it is correct – the underlying subsystem is usually the problem.

From Book On-Line:

PAGEIOLATCH_DT
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_EX
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_KP
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_SH
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_UP
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_XX Explanation:

Simply put, this particular wait type occurs when any of the tasks is waiting for data from the disk to move to the buffer cache.

ReducingPAGEIOLATCH_XX wait:

Just like any other wait type, this is again a very challenging and interesting subject to resolve. Here are a few things you can experiment on:

Improve your IO subsystem speed (read the first paragraph of this article, if you have not read it, I repeat that it is easy to say a step like this than to actually implement or do it).
This type of wait stats can also happen due to memory pressure or any other memory issues. Putting aside the issue of a faulty IO subsystem, this wait type warrants proper analysis of the memory counters. If due to any reasons, the memory is not optimal and unable to receive the IO data. This situation can create this kind of wait type.
Proper placing of files is very important. We should check file system for the proper placement of files – LDF and MDF on separate drive, TempDB on separate drive, hot spot tables on separate filegroup (and on separate disk), etc.
Check the File Statistics and see if there is higher IO Read and IO Write Stall SQL SERVER – Get File Statistics Using fn_virtualfilestats.
It is very possible that there are no proper indexes on the system and there are lots of table scans and heap scans. Creating proper index can reduce the IO bandwidth considerably. If SQL Server can use appropriate cover index instead of clustered index, it can significantly reduce lots of CPU, Memory and IO (considering cover index has much lesser columns than cluster table and all other it depends conditions). You can refer to the two articles’ links below previously written by me that talk about how to optimize indexes.
Create Missing Indexes
Drop Unused Indexes

Updating statistics can help the Query Optimizer to render optimal plan, which can only be either directly or indirectly. I have seen that updating statistics with full scan (again, if your database is huge and you cannot do this – never mind!) can provide optimal information to SQL Server optimizer leading to efficient plan.
Checking Memory Related Perfmon Counters
SQLServer: Memory Manager\Memory Grants Pending (Consistent higher value than 0-2)
SQLServer: Memory Manager\Memory Grants Outstanding (Consistent higher value, Benchmark)
SQLServer: Buffer Manager\Buffer Hit Cache Ratio (Higher is better, greater than 90% for usually smooth running system)
SQLServer: Buffer Manager\Page Life Expectancy (Consistent lower value than 300 seconds)
Memory: Available Mbytes (Information only)
Memory: Page Faults/sec (Benchmark only)
Memory: Pages/sec (Benchmark only)

Checking Disk Related Perfmon Counters
Average Disk sec/Read (Consistent higher value than 4-8 millisecond is not good)
Average Disk sec/Write (Consistent higher value than 4-8 millisecond is not good)
Average Disk Read/Write Queue Length (Consistent higher value than benchmark is not good)

IO_COMPLETION Explanation:

Any tasks are waiting for I/O to finish. This is a good indication that IO needs to be looked over here.Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.

Reducing IO_COMPLETION wait:

When it is an issue concerning the IO, one should look at the following things related to IO subsystem:

Proper placing of the files is very important. We should check the file system for proper placement of files – LDF and MDF on a separate drive, TempDB on another separate drive, hot spot tables on separate filegroup (and on separate disk),etc.
Check the File Statistics and see if there is higher IO Read and IO Write Stall SQL SERVER – Get File Statistics Using fn_virtualfilestats.
Check event log and error log for any errors or warnings related to IO.
If you are using SAN (Storage Area Network), check the throughput of the SAN system as well as the configuration of the HBA Queue Depth. In one of my recent projects, the SAN was performing really badly so the SAN administrator did not accept it. After some investigations, he agreed to change the HBA Queue Depth on development (test environment) set up and as soon as we changed the HBA Queue Depth to quite a higher value, there was a sudden big improvement in the performance.
It is very possible that there are no proper indexes in the system and there are lots of table scans and heap scans. Creating proper index can reduce the IO bandwidth considerably. If SQL Server can use appropriate cover index instead of clustered index, it can effectively reduce lots of CPU, Memory and IO (considering cover index has lesser columns than cluster table and all other; it depends upon the situation). You can refer to the two articles that I wrote; they are about how to optimize indexes:
Create Missing Indexes
Drop Unused Indexes

Checking Memory Related Perfmon Counters
SQLServer: Memory Manager\Memory Grants Pending (Consistent higher value than 0-2)
SQLServer: Memory Manager\Memory Grants Outstanding (Consistent higher value, Benchmark)
SQLServer: Buffer Manager\Buffer Hit Cache Ratio (Higher is better, greater than 90% for usually smooth running system)
SQLServer: Buffer Manager\Page Life Expectancy (Consistent lower value than 300 seconds)
Memory: Available Mbytes (Information only)
Memory: Page Faults/sec (Benchmark only)
Memory: Pages/sec (Benchmark only)

ASYNC_IO_COMPLETION Explanation:

Any tasks are waiting for I/O to finish. If by any means your application that’s connected to SQL Server is processing the data very slowly, this type of wait can occur. Several long-running database operations like BACKUP, CREATE DATABASE, ALTER DATABASE or other operations can also create this wait type.Occurs when a task is waiting for I/Os to finish.

Reducing ASYNC_IO_COMPLETION wait:

When it is an issue related to IO, one should check for the following things associated to IO subsystem:

Look at the programming and see if there is any application code which processes the data slowly (like inefficient loop, etc.). Note that it should be re-written to avoid this wait type.
Proper placing of the files is very important. We should check the file system for proper placement of the files – LDF and MDF on separate drive, TempDB on another separate drive, hot spot tables on separate filegroup (and on separate disk), etc.
Check the File Statistics and see if there is a higher IO Read and IO Write Stall SQL SERVER – Get File Statistics Using fn_virtualfilestats.
Check event log and error log for any errors or warnings related to IO.
If you are using SAN (Storage Area Network), check the throughput of the SAN system as well as configuration of the HBA Queue Depth. In one of my recent projects, the SAN was performing really badly and so the SAN administrator did not accept it. After some investigations, he agreed to change the HBA Queue Depth on the development setup (test environment). As soon as we changed the HBA Queue Depth to quite a higher value, there was a sudden big improvement in the performance.
It is very likely to happen that there are no proper indexes on the system and yet there are lots of table scans and heap scans. Creating proper index can reduce the IO bandwidth considerably. If SQL Server can use appropriate cover index instead of clustered index, it can effectively reduce lots of CPU, Memory and IO (considering cover index has lesser columns than cluster table and all other; it depends upon the situation). You can refer to the following two articles I wrote that talk about how to optimize indexes:
Create Missing Indexes
Drop Unused Indexes

Search This Blog

Database With Fun (While 1 = 1)