By: Ranga Narasimhan | Updated: 2009-09-23 | Comments (4) | Related: > Dynamic Management Views and Functions
Problem
With SQL Server 2005 DMVs, we can easily find query performance statistics. Every DBA has a favorite script to find query performance stats. I also have one. But, are all the queries captured by the DMVs? In this tip I will walk through capturing some of this data and show what is capture and what is not captured by the DMVs.
Solution
Here is a simple example which clearly shows that not all queries are captured in the DMVs.
This excellent article was my starting point. I was running the sample queries in that article and was curious to see if they showed up in my query stats script, but they did not.
Here is my favorite script to find query stats. This will show the number of times a query was executed, reads, writes and the execution plan.
The script gets data from these DMVs.
-- Query Stats
SELECT
sdest.dbid
,sdest.[text] AS Batch_Object,
SUBSTRING(sdest.[text], (sdeqs.statement_start_offset/2) + 1,
((CASE sdeqs.statement_end_offset
WHEN -1 THEN DATALENGTH(sdest.[text]) ELSE sdeqs.statement_end_offset END
- sdeqs.statement_start_offset)/2) + 1) AS SQL_Statement
, sdeqp.query_plan
, sdeqs.execution_count
, sdeqs.total_physical_reads
,(sdeqs.total_physical_reads / sdeqs.execution_count) AS average_physical_reads
, sdeqs.total_logical_writes
, (sdeqs.total_logical_writes / sdeqs.execution_count) AS average_logical_writes
, sdeqs.total_logical_reads
, (sdeqs.total_logical_reads / sdeqs.execution_count) AS average_logical_lReads
, sdeqs.total_clr_time
, (sdeqs.total_clr_time / sdeqs.execution_count) AS average_CLRTime
, sdeqs.total_elapsed_time
, (sdeqs.total_elapsed_time / sdeqs.execution_count) AS average_elapsed_time
, sdeqs.last_execution_time
, sdeqs.creation_time
FROM sys.dm_exec_query_stats AS sdeqs
CROSS apply sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest
CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handle) AS sdeqp
WHERE sdeqs.last_execution_time > DATEADD(HH,-2,GETDATE())
AND sdest.dbid = (SELECT DB_ID('AdventureWorks'))
ORDER BY execution_count DESC For a clean server, that has just been started or if we run DBCC FREEPROCCACHE, if the above query is run we can see that no data is captured as shown below.
Let's see what is captured by the DMVs and what is not captured.
Execute Script-1 a few times in query analyzer and then execute the Query Stats script above and see if script-1 shows up in the results.
-- Script-1 USE AdventureWorks GO EXEC sp_executesql @stmt = N'select * from Sales.SalesOrderDetail where ProductID = @ProductID', @params = N'@ProductID int', @ProductID = 870 GO
As we can see below it does not show up.
So, we now know sp_executesql queries are not captured in the DMVs
Execute Script-2 few times in query analyzer and then execute the Query Stats script above and see if script-2 shows up in the results.
-- Script-2 USE AdventureWorks GO SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 870
As we can see below it does not show up.
So, we now know standalone queries are not captured in the DMVs. This is good to know since most developers are very comfortable writing inline T-SQL in their application code. So, those inline T-SQL statements are not captured in the DMVs
Now, let's create a stored procedure that has a select statement using script-3 below. After the SP has been created, execute the stored proc dbo.DMVQueryTest a few times in query analyzer and then execute the Query Stats script to see if the stored procedure and select statement shows up in the results.
-- Script-3
USE AdventureWorks
GO
IF OBJECT_ID('dbo.DMVQueryTest') IS NOT NULL
DROP PROC dbo.DMVQueryTest
GO
CREATE PROC dbo.DMVQueryTest
@productId INT
AS
BEGIN
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @productId
RETURN 0
END
GO
EXEC dbo.DMVQueryTest 870As we can see below we can now see this in the query stats.
So, SQL statements that are within a stored procedure are captured in the DMVs. This proves that NOT all T-SQL executed against a database is captured in the DMVs. This is another reason why stored procedures are the best method for data related operations, so we can track their performance and usage using DMVs.
It turns out that the issue is with trying to include the execution plan. If we run the following script we can see the data, but we do not get the execution plans for all of the SQL batches that are run.
SELECT TOP 5 creation_time, last_execution_time, total_clr_time, total_clr_time/execution_count AS [Avg CLR Time], last_clr_time, execution_count, SUBSTRING(st.TEXT, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_clr_time/execution_count DESC; GO
Note: Upon further investigation of this query based on input from yuewah, the problem is with the line of code that is commented out below that was limiting the data to queries with a database id for AdventureWorks. If this line is commented out the script will return the results for the queries. The reason for this is that the dbid column is NULL for ad hoc and prepared SQL statements, so trying to limit ths to only queries from the AdventureWorks database removed these queries from the resultset.
-- Query Stats
SELECT
sdest.dbid
,sdest.[text] AS Batch_Object,
SUBSTRING(sdest.[text], (sdeqs.statement_start_offset/2) + 1,
((CASE sdeqs.statement_end_offset
WHEN -1 THEN DATALENGTH(sdest.[text]) ELSE sdeqs.statement_end_offset END
- sdeqs.statement_start_offset)/2) + 1) AS SQL_Statement
, sdeqp.query_plan
, sdeqs.execution_count
, sdeqs.total_physical_reads
,(sdeqs.total_physical_reads / sdeqs.execution_count) AS average_physical_reads
, sdeqs.total_logical_writes
, (sdeqs.total_logical_writes / sdeqs.execution_count) AS average_logical_writes
, sdeqs.total_logical_reads
, (sdeqs.total_logical_reads / sdeqs.execution_count) AS average_logical_lReads
, sdeqs.total_clr_time
, (sdeqs.total_clr_time / sdeqs.execution_count) AS average_CLRTime
, sdeqs.total_elapsed_time
, (sdeqs.total_elapsed_time / sdeqs.execution_count) AS average_elapsed_time
, sdeqs.last_execution_time
, sdeqs.creation_time
FROM sys.dm_exec_query_stats AS sdeqs
CROSS apply sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest
CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handle) AS sdeqp
WHERE sdeqs.last_execution_time > DATEADD(HH,-2,GETDATE())
--AND sdest.dbid = (SELECT DB_ID('AdventureWorks'))
ORDER BY execution_count DESC Next Steps
- Keep this in mind when you are using the DMVs for query usage and performance stats. If you are using inline T-SQL and sp_executesql you may not be capturing all of the data that you need.
- Also, think about using stored procedures for all data related operations instead of using inline T-SQL or sp_executesql in your application code.
- Also, take the time to read Books Online to understand when certain values are available and when they are not available.
About the author
Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-09-23
