SQL SERVER – Denali – DMV Enhancement – sys.dm_exec_query_stats – New Columns

SQL Server version next Denali has lots of enhancements. Some of the enhancements are just game changing and overcomes needs of more coding to do the same thing.

Similar function DMV is sys.dm_exec_query_stats. There are four new columns added to this DMV. I have often used this DMV to check recently ran query, their execution plan by joining more DMVs to it. However, there was also need of knowing how many rows my queries have returned.

This DMV is enhanced with four more queries.

total_rows – Total number of rows returned by query
last_rows – Number of the rows return by the last execution of the query
min_rows – Minimum numbers of the rows returned by the query since it is compiled
max_rows – Maximum numbers of the rows returned by the query since it is compiled

Here see the quick example of the columns:

SELECT qs.execution_count,
qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(
CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset
)/2
) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;

If you run above query it will give us different result based on your server’s workload.

You can see that the above result set – it displays how many time query has executed and how new columns (total_rows, last_rows, min_rows and max_rows) returns result based on the query.

Now here is the question back to you – if you have downloaded Denali and installed it, I would like to see you use this new column and come up with some more creative usage.

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

About these ads

SQL SERVER – Author’s Book is Available in India and USA

I am feeling very good to write this short blog post. My book is now officially available on in India and USA.

In India you can get it from Flipkart - http://bit.ly/pinalbook

In USA you can get it from Amazon – http://amzn.to/pb49jq

This book is just like this blog and contains all the complex subject in very simple manner. I am confident that you will for sure like this book if you like this blog.

Here is quick video shot by my wife when I was reading my own book. See the original post to see the video.

Here is quick secret for you – there is big surprise in September for all those who own this book.

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

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)

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)