In this blog post we will see three new DMVs which are introduced in Denali. The DMVs are very simple and there is not much to describe them. So here is the simple game. I will be asking a question back to you after seeing the result of the each of the DMV and you help me to complete this blog post.
SELECT * FROM sys.dm_server_memory_dumps
Above DMV returns following result.
SELECT * FROM sys.dm_server_services
Above DMV returns following result.
SELECT * FROM sys.dm_server_registry
Above DMV returns following result.
Now here is the question for you – how will you use this DMV in your application. One thing for sure is that this makes it easier to find out various information. We can easily know which services are running and what was the start time etc but also where exactly you will use this in production server?
Response to Question: SQLConcept – Feodor Georgiev has given excellent summary on this subject. A Must Read
In my recent Office Hours, I received a question on how to determine the case sensitivity of the database.
The quick answer to this is to identify the collation of the database and check the properties of the collation. I have previously written how one can identify database collation. Once you have figured out the collation of the database, you can put that in the WHERE condition of the following T-SQL and then check the case sensitivity from the description.
SELECT * FROM fn_helpcollations()
The method shown above is the most recommended method and I suggest using the same.
When I was a young DBA, I did not have the patience to follow the above method. I used to do something very simple.
SELECT 1 WHERE 'SQL' = 'sql'
If the above query returns me the result, it means that the database is case-insensitive. Please note that by no means do I suggest using this method; I really recommend using the method fn_helpcollations().
Another interesting suggestion was from Dave Dustin who is SQL Server MVP from New Zealand. He has provided the following script:
SELECT 1 FROM sys.Databases WHERE name='<databasename>' AND (collation_name LIKE '%CS%' OR collation_name LIKE '%BIN%')
Insert your database name in the WHERE clause. If the query returns any result, it means the database is case-sensitive.
It’s interesting to see that one simple question can result to three interesting ways to know the answer. Do you know any other method to know the database case sensitivity? Please share it here and I will post it with due credit.
One more quick introduction to DMV for Denali. Following DMV provides information about Windows Operating System. Here is the quick example of the same.
This DMV returns information about the operating system volume (directory) on which the specified databases and files are stored. Here is the quick example I have created for the same.
SELECT * FROM sys.dm_os_windows_info;
Here is the screenshot of the same:
Here is my question back to you – where would you use this stored procedure in your application? What is your preferred method to know details about Windows? One last question – what is 1033 in the last column of the table result and what is 1033 represent?
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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
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
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.