SQL SERVER – Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28

Earlier, I have tried to cover some important points about wait stats in detail. Here are some points that we had covered earlier.

  • DMV related to wait stats reset when we reset SQL Server services
  • DMV related to wait stats reset when we manually reset the wait types

However, at times, there is a need of making this data persistent so that we can take a look at them later on. Sometimes, performance tuning experts do some modifications to the server and try to measure the wait stats at that point of time and after some duration. I use the following method to measure the wait stats over the time.

-- Create Table
CREATE 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
-- Populate Table at Time 1
SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],
FROM sys.dm_os_wait_stats
----- Desired Delay (for one hour) WAITFOR DELAY '01:00:00'
-- Populate Table at Time 2
SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],
FROM sys.dm_os_wait_stats
-- Check the difference between Time 1 and Time 2
SELECT T1.wait_type, T1.wait_time_ms Original_WaitTime,
T2.wait_time_ms LaterWaitTime,
T2.wait_time_ms - T1.wait_time_ms) DiffenceWaitTime
FROM MyWaitStatTable T1
INNER JOIN MyWaitStatTable T2 ON T1.wait_type = T2.wait_type
WHERE T2.wait_time_ms > T1.wait_time_ms
AND T1.Flag = 1 AND T2.Flag = 2
ORDER BY DiffenceWaitTime DESC
-- Clean up
DROP TABLE MyWaitStatTable

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.

In a future blog post, we will talk about specific wait stats.

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28

Previously, we covered the DMV sys.dm_os_wait_stats, and also saw how it can be useful to identify the major resource bottleneck. However, at the same time, we discussed that this is only useful when we are looking at an instance-level picture. Quite often we want to know about the processes going in our server at the given instant. Here is the query for the same.

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,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1

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.

Click to Enlarage
Click to Enlarage

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.

In future posts on this series, we will see how once identified wait type we can attempt to reduce the same.

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – DMV – sys.dm_os_wait_stats Explanation – Wait Type – Day 3 of 28

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. However, the interpretation is left to us. This is a challenge as understanding wait stats can often be quite tricky. Anyway, we will cover few wait stats in one of the future articles. Today we will go over the basic understanding of the DMV.

The Official Book OnLine Reference for DMV is over here: sys.dm_os_wait_stats. I suggest you all to refer this for all the accuracy.

Following is a statement from the online book:

“Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance.”

This is the statement which has inspired me to write this series.

Let us first run the following statement from DMV.

FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

Above statement will show us few of the columns. Here it is quick explanation of each of the column.

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

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Signal Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28

In this post, let’s delve a bit more in depth regarding wait stats.

The very first question: 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.

Let us try to understand the whole thing with a simple analogy of a taxi and a passenger.

Two friends, Tom and Danny, go to the mall together. When they leave the mall, they decide to take a taxi. Tom and Danny both stand in the line waiting for their turn to get into the taxi. This is the Signal Wait Time as they are ready to get into the taxi but the taxis are currently serving other customer and they have to wait for their turn. In other word they are in a runnable state.

Now when it is their turn to get into the taxi, the taxi driver informs them he does not take credit cards and only cash is accepted. Neither Tom nor Danny have enough cash, they both cannot get into the vehicle. Tom waits outside in the queue and Danny goes to ATM to fetch the cash. During this time the taxi cannot wait, they have to let other passengers get into the taxi. As Tom and Danny both are outside in the queue, this is the Query Wait Time and they are in the suspended state. They cannot do anything till they get the cash.

Once Danny gets the cash, they are both standing in the line again, creating one more Signal Wait Time. This time when their turn comes they can pay the taxi driver in cash and reach their destination. The time taken for the taxi to get from the mall to the destination is running time (CPU time) and the taxi is running.

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

-- Signal Waits for instance
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(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.

In future articles we will discuss in detail the various wait types and wait stats and their resolution.

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28

I have been working a lot on Wait Stats and Wait Types recently. Last Year, I requested blog readers to send me their respective server’s wait stats. I appreciate their kind response as I have received  Wait stats from my readers. I took each of the results and carefully analyzed them. I provided necessary feedback to the person who sent me his wait stats and wait types. Based on the feedbacks I got, many of the readers have tuned their server.

After a while I got further feedbacks on my recommendations and again, I collected wait stats. I recorded the wait stats and my recommendations and did further research. At some point at time, there were more than 10 different round trips of the recommendations and suggestions. Finally, after six month of working my hands on performance tuning, I have collected some real world wisdom because of this.

Now I plan to share my findings with all of you over here.

Before anything else, please note that all of these are based on my personal observations and opinions. They may or may not match the theory available at other places. Some of the suggestions may not match your situation. Remember, every server is different and consequently, there is more than one solution to a particular problem. However, this series is written with kept wait stats in mind. While I was working on various performance tuning consultations, I did many more things than just tuning wait stats.

Today we will discuss how to capture the wait stats. I use the script diagnostic script created by my friend and SQL Server Expert Glenn Berry to collect wait stats.

Here is the script to collect the wait stats:

-- Isolate top waits for server instance since last restart or statistics clear
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
FROM sys.dm_os_wait_stats
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_pct
FROM Waits AS W1
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold

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);

Once the wait stats are collected, we can start analysis them and try to see what is causing any particular wait stats to achieve higher percentages than the others.

Many waits stats are related to one another. When the CPU pressure is high, all the CPU-related wait stats show up on top. But when that is fixed, all the wait stats related to the CPU start showing reasonable percentages. It is difficult to have a sure solution, but there are good indications and good suggestions on how to solve this.

I will keep this blog post updated as I will post more details about wait stats and how I reduce them. The reference to Book On Line is over here.

Of course, I have selected February to run this Wait Stats series. I am already cheating by having the smallest month to run this series. :)

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DMV – sys.dm_exec_query_optimizer_info – Statistics of Optimizer

Incredibly, SQL Server has so much information to share with us. Every single day, I am amazed with this SQL Server technology. Sometimes I find several interesting information by just querying few of the DMV. And when I present this info in front of my client during performance tuning consultancy, they are surprised with my findings. Today, I am going to share one of the hidden gems of DMV with you, the one which I frequently use to understand what’s going on under the hood of SQL Server.

SQL Server keeps the record of most of the operations of the Query Optimizer. We can learn many interesting details about the optimizer which can be utilized to improve the performance of server.

FROM sys.dm_exec_query_optimizer_info
WHERE counter IN ('optimizations', 'elapsed time','final cost',
'insert stmt','delete stmt','update stmt',
'merge stmt','contains subquery','tables',
'hints','order hint','join hint',
'view reference','remote query','maximum DOP',
'maximum recursion level','indexed views loaded',
'indexed views matched','indexed views used',
'indexed views updated','dynamic cursor request',
'fast forward cursor request')

All occurrence values are cumulative and are set to 0 at system restart. All values for value fields are set to NULL at system restart. I have removed a few of the internal counters from the script above, and kept only documented details. Let us check the result of the above query.

As you can see, there is so much vital information that is revealed in above query. I can easily say so many things about how many times Optimizer was triggered and what the average time taken by it to optimize my queries was. Additionally, I can also determine how many times update, insert or delete statements were optimized.

I was able to quickly figure out that my client was overusing the Query Hints using this dynamic management view. If you have been reading my blog, I am sure you are aware of my series related to SQL Server Views SQL SERVER – The Limitations of the Views – Eleven and more…. With this, I can take a quick look and figure out how many times Views were used in various solutions within the query.

Moreover, you can easily know what fraction of the optimizations has been involved in tuning server.

For example, the following query would tell me, in total optimizations, what the fraction of time View was “reference“. As this View also includes system Views and DMVs, the number is a bit higher on my machine.

FROM sys.dm_exec_query_optimizer_info WHERE counter = 'view reference') /
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')
AS ViewReferencedFraction

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Total Number of Transaction on Interval

In one of my recent Performance Tuning assignment I was asked how do someone know how many transactions are happening on server during certain interval. I had handy script for the same. Following script displays transactions happened on server at the interval of one minute. You can change the WAITFOR DELAY to any other interval and it should work.

-- First PASS
@Second INT
@First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$SQLENT1:Databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
-- Following is the delay
WAITFOR DELAY '00:01:00'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$SQLENT1:Databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
SELECT (@Second - @First) 'TotalTransactions'

If you are using any other method to detect transactions per interval, I request you to post it over here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Queries using Parallelism from Cached Plan

I recently came across wonderful blog post of Feodor Georgiev. He is one fine developer and like to dwell in the subject of performance tuning and query optimizations. He is one real genius and original blogger. Recently I came across his wonderful script, which I was in fact writing myself and I found out that he has already posted the same query over here. After getting his permission I am reproducing the same query on this blog.

Note to not run the following script on busy transactional production environment as well, it does not get all historical results as it only applies to cached plan.

Following T-SQL script gets all the queries and their execution plan where parallelism operations is kicked up. Pay attention there is TOP 10 is used, if you have lots of transactional operations, I suggest that you change TOP 10 to TOP 50.
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
JOIN sys.dm_exec_query_stats qs
ON qs.plan_handle = cp.plan_handle
cp.cacheobjtype = 'Compiled Plan' AND
p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
, 'float') > 0

Above query will return all the queries which are generating parallel plans. I suggest you run above query on your development server and check if above query is returning all the parallel plan queries.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats

In the recent Query Tuning project, one of the developers who were helping me out in the project asked me if there is any way that he could know how many pages are used by any Index,  and if there is any way I could demonstrate the different levels of B-Tree.

The following is the diagram on Clustered Index that I have quickly drawn using MS Word for the said developer.

Clustered Index B-Tree
Clustered Index B-Tree

Let us quickly see the diagram of B-Tree and how the levels are set up. The leaf level is always considered as Level 0. There can be many levels of the intermediate nodes. In the example above, I have listed only one intermediate node for demonstration purposes.

We can use Dynamic Management Views to figure out how many different levels are there for any Index, as well as how many rows are stored at each level and the number of pages used for all Index.

Let us run the following commands and generate a table with data. We will create a Clustered Index so we can have B-Tree structure.

USE tempdb
-- Create Table FragTable
FirstName CHAR(2000),
LastName CHAR(3000),
City CHAR(2253))
-- Create Clustered Index
-- Insert one Million Records
INSERT INTO FragTable (ID,FirstName,LastName,City)
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
sys.all_objects a
CROSS JOIN sys.all_objects b
-- Check the percentages
SELECT avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
-- Clean up

Now let us check the result-set of the table.

From the resultset above, we can see that there are multiple levels of the Index. In our example, we have 4 levels of Index, and each level has different numbers of  pages and rows. In one of the future articles I will post, we will analyze the result in a deeper sense.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Data Pages in Buffer Pool – Data Stored in Memory Cache

This will drop all the clean buffers so we will be able to start again from there. Now, run the following script and check the execution plan of the query.

Have you ever wondered what types of data are there in your cache? During SQL Server Trainings, I am usually asked if there is any way one can know how much data in a table is stored in the memory cache? The more detailed question I usually get is if there are multiple indexes on table (and used in a query), were the data of the single table stored multiple times in the memory cache or only for a single time?

Here is a query you can run to figure out what kind of data is stored in the cache.

USE AdventureWorks
SELECT COUNT(*) AS cached_pages_count,
name AS BaseTableName, IndexName,
FROM sys.dm_os_buffer_descriptors AS bd
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
index_id, allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;

Now let us run the query above and observe the output of the same.

We can see in the above query that there are four columns.

Cached_Pages_Count lists the pages cached in the memory.
BaseTableName lists the original base table from which data pages are cached.
IndexName lists the name of the index from which pages are cached.
IndexTypeDesc lists the type of index.

Now, let us do one more experience here. Please note that you should not run this test on a production server as it can extremely reduce the performance of the database.


This will drop all the clean buffers and we will be able to start again from there. Now run following script and check the execution plan for the same.
USE AdventureWorks
SELECT UnitPrice, ModifiedDate
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID BETWEEN 1 AND 100

The execution plans contain the usage of two different indexes.

Now, let us run the script that checks the pages cached in SQL Server. It will give us the following output.

It is clear from the Resultset that when more than one index is used, datapages related to both or all of the indexes are stored in Memory Cache separately.

Let me know what you think of this article. I had a great pleasure while writing this article because I was able to write on this subject, which I like the most. In the next article, we will exactly see what data are cached and those that are not cached, using a few undocumented commands.

Reference: Pinal Dave (http://blog.SQLAuthority.com)