SQL SERVER – Find Details for Statistics of Whole Database – DMV – T-SQL Script

I was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command but I remembered that this is marked to be deprecated in future. Again, using DMV is the right thing to do moving forward. I quickly wrote following script which gives a lot more information than sp_helpstats.

USE AdventureWorks
GO
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1);

If you have better script to retrieve information about statistics, please share here and I will publish it with due credit.

Update: Read follow up excellent blog post by Jason Brimhall.

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

About these ads

SQL SERVER – Pending IO request in SQL Server – DMV

I received following question:

“How do we know how many pending IO requests are there for database files (.mdf, .ldf) individually?”

Very interesting question and indeed answer is very interesting as well.

Here is the quick script which I use to find the same. It has to be run in the context of the database for which you want to know pending IO statistics.

USE DATABASE
GO
SELECT vfs.database_id, df.name, df.physical_name
,vfs.FILE_ID, ior.io_pending
FROM sys.dm_io_pending_io_requests ior
INNER JOIN sys.dm_io_virtual_file_stats (DB_ID(), NULL) vfs
ON (vfs.file_handle = ior.io_handle)
INNER JOIN sys.database_files df ON (df.FILE_ID = vfs.FILE_ID)

I keep this script handy as it works like magic every time. If you use any other script please post here and I will post it with due credit.

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

SQL SERVER – CXPACKET – Parallelism – Advanced Solution – Wait Type – Day 7 of 28

Earlier we discussed about the what is the common solution to solve the issue with CXPACKET wait time. Today I am going to talk about few of the other suggestions which can help to reduce the CXPACKET wait. If you are going to suggest that I should focus on MAXDOP and COST THRESHOLD – I totally agree. I have covered them in details in yesterday’s blog post. Today we are going to discuss few other way CXPACKET can be reduced.

Potential Reasons:

  • If data is heavily skewed, there are chances that query optimizer may estimate the correct amount of the data leading to assign fewer thread to query. This can easily lead to uneven workload on threads and may create CXPAKCET wait.
  • While retrieving the data one of the thread face IO, Memory or CPU bottleneck and have to wait to get those resources to execute its tasks, may create CXPACKET wait as well.
  • Data which is retrieved is on different speed IO Subsystem. (This is not common and hardly possible but there are chances).
  • Higher fragmentations in some area of the table can lead less data per page. This may lead to CXPACKET wait.

As I said the reasons here mentioned are not the major cause of the CXPACKET wait but any kind of scenario can create the probable wait time.

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.

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

Note: The information presented here is from my experience and I no way claim it to be accurate. I suggest reading book on-line for further clarification. All the discussion of Wait Stats over here is generic and it varies by system to system. You are recommended to test this on development server before implementing to production server.

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

SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28

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.

Books On-Line:

Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

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'
GO
RECONFIGURE 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'
GO
RECONFIGURE 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'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

Read all the post in the Wait Types and Queue series. Additionally a must read comment of Jonathan Kehayias.

Note: The information presented here is from my experience and I no way claim it to be accurate. I suggest you all to read the online book for further clarification. All the discussion of Wait Stats over here is generic and it varies from system to system. It is recommended that you test this on the development server before implementing on the production server.

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

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
)
GO
-- Populate Table at Time 1
INSERT 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(), 1
FROM sys.dm_os_wait_stats
GO
----- Desired Delay (for one hour) WAITFOR DELAY '01:00:00'
-- Populate Table at Time 2
INSERT 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(), 2
FROM sys.dm_os_wait_stats
GO
-- 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
GO
-- Clean up
DROP 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.

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,
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 columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type
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
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.

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.

SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
GO

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)