Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Collecting Query Statistics for SQL Server 2005


By:   |   Read Comments (4)   |   Related Tips: More > 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 -THEN DATALENGTH(sdest.[text]ELSE sdeqs.statement_end_offset END
                       
sdeqs.statement_start_offset)/2) + 1AS SQL_Statement
       
sdeqp.query_plan 
       
sdeqs.execution_count
       
sdeqs.total_physical_reads
       
,(sdeqs.total_physical_reads sdeqs.execution_countAS average_physical_reads
       
sdeqs.total_logical_writes
       
, (sdeqs.total_logical_writes sdeqs.execution_countAS average_logical_writes
       
sdeqs.total_logical_reads
       
, (sdeqs.total_logical_reads sdeqs.execution_countAS average_logical_lReads
       
sdeqs.total_clr_time
       
, (sdeqs.total_clr_time sdeqs.execution_countAS average_CLRTime
       
sdeqs.total_elapsed_time
       
, (sdeqs.total_elapsed_time sdeqs.execution_countAS 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_handleAS sdest
       
CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handleAS 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 870

As 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_timelast_execution_timetotal_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 -THEN DATALENGTH(st.TEXT)
        
ELSE qs.statement_end_offset END 
            
qs.statement_start_offset)/2) + 1AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handleAS st
ORDER BY total_clr_time/execution_count DESC;
GO

Here is the result.

 

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 -THEN DATALENGTH(sdest.[text]ELSE sdeqs.statement_end_offset END
                       
sdeqs.statement_start_offset)/2) + 1AS SQL_Statement
       
sdeqp.query_plan 
       
sdeqs.execution_count
       
sdeqs.total_physical_reads
       
,(sdeqs.total_physical_reads sdeqs.execution_countAS average_physical_reads
       
sdeqs.total_logical_writes
       
, (sdeqs.total_logical_writes sdeqs.execution_countAS average_logical_writes
       
sdeqs.total_logical_reads
       
, (sdeqs.total_logical_reads sdeqs.execution_countAS average_logical_lReads
       
sdeqs.total_clr_time
       
, (sdeqs.total_clr_time sdeqs.execution_countAS average_CLRTime
       
sdeqs.total_elapsed_time
       
, (sdeqs.total_elapsed_time sdeqs.execution_countAS 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_handleAS sdest
       
CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handleAS 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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Ranga Narasimhan Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, October 17, 2009 - 3:32:31 PM - admin Back To Top

Greg,

Thank you for the additional script!

Thank you,
The MSSQLTips Team


Tuesday, October 06, 2009 - 8:23:49 AM - Greg Larsen Back To Top

I've modified the original code to constrain the statistics being displayed based on the plans that are associated with the AdventureWorks database in someway by using information in the sys.dm_exec_sql_text or sys.dm_exec_plan_attributes DMVs.

Greg

 

SELECT COALESCE(DB_NAME(sdest.dbid),DB_NAME(CAST(pa.value AS INT)) + '*')  AS DBNAME       ,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        OUTER APPLY sys.dm_exec_plan_attributes(sdeqs.plan_handle) pa WHERE  sdeqs.last_execution_time > DATEADD(HH,-2,GETDATE())   AND attribute = 'dbid'    AND (DB_NAME(sdest.dbid) = (SELECT DB_ID('AdventureWorks'))        OR DB_NAME(CAST(pa.value AS INT)) = 'AdventureWorks')

 


Monday, October 05, 2009 - 5:18:18 AM - Jeff Moden Back To Top

Cool tip, Ranga.  Thanks for taking the time.


Wednesday, September 23, 2009 - 2:01:40 AM - yuewah Back To Top

Actually,

if you comment the following statement, you can see the SQL in DMV
AND sdest.dbid = (SELECT DB_ID('AdventureWorks'))


Learn more about SQL Server tools