• Madeira Team

Monitor Query Statistics

One of the most common tasks that DBAs are required to perform is monitoring. This is not just a task. Monitoring is a strategy, and a good DBA should have a well-defined plan for carrying out this strategy. But I’m not here to talk about monitoring as a whole. I would like to talk about one of the common monitoring areas, which is query statistics.

The reason query statistics is so common is because most of the problems that users complain about are related to query performance. It might be a specific report that is taking forever or the whole system that is being slow. Whatever it is, you need to monitor query performance over time, find the problematic queries, and then do your magic in order to improve the performance of these queries.

So I’m here to talk about the part of finding the problematic queries. There are many ways to do this, which differ in their efficiency and complexity. One of the use cases that I encounter a lot as a consultant, is when the customer complains about some performance problem that occurred in the past or that is currently happening. The problem is that the customer has no monitoring strategy, and there is currently no process collecting performance metrics of any kind. So you can start such a process, but then you’ll have to wait for a while in order to let the process collect enough data to analyze. You also might not be able to identify the problem that the customer was talking about, which occurred in the past.

Fortunately, SQL Server maintains query statistics through a DMV – sys.dm_exec_query_stats. This view returns aggregated performance data about execution plans that are currently in the plan cache. It accumulates data since the last time the SQL Server instance was started, but only as long as the plan is in cache. So this is a good start for the use case described earlier. You can immediately begin to analyze performance data about the queries in the system, which has been collected and maintained for you. And the nice part is that you can do this by writing simple queries against the DMVs. No need to set up anything or to perform any complex tasks.

Let’s look at some of the columns that this view returns:

Column NameDescriptionsql_handleThis is a token that refers to the text of the batch corresponding to the plan in cachestatement_start_offsetThe offset in bytes from the beginning of the batch text to the beginning of the text of the query represented by this rowstatement_end_offsetThe offset in bytes from the beginning of the batch text to the end of the text of the query represented by this rowplan_generation_numThe number of times this plan has been recompiled since it first entered the plan cacheplan_handleThis is a token that refers to the batch plan in cachecreation_timeThe date & time at which the plan was last compiledlast_execution_timeLast date & time at which the query started executingexecution_countNumber of times that the plan has been executed since the last compile (creation_time)

All the rest of the columns represent aggregated statistics about the executions of the queries.

There are 7 areas that you can monitor through this view:

  1. Worker Time – CPU time (reported in microseconds)

  2. Physical Reads – Number of pages read from disk

  3. Logical Writes – Number of pages updated in the buffer pool

  4. Logical Reads – Number of pages read from the buffer pool

  5. CLR Time – Time consumed by CLR objects (reported in microseconds)

  6. Elapsed Time – Overall time (reported in microseconds)

  7. Rows – Number of rows returned by the query

For each area you can query 4 different metrics: total, last, min and max. So, for example, you can view the total aggregated worker time and the max number of logical reads for some query. You can easily calculate the average (e.g. average elapsed time) by dividing the total value by the execution count.

Monitor-Query-Statistics-Picture-1

There are a few important things you should know about this view…

Batch vs. Statement

Each row represents a single query or statement in a batch. If a batch contains multiple statements, then each statement will have its own row in this view. All the columns described in the table above describe the batch as a whole, except for “statement_start_offset”, “statement_end_offset” and “last_execution_time”, so they will be duplicated across all the rows that belong to the same plan. All the rest of the columns (the statistics data) represent a single statement inside the batch.

You can use the sys.dm_exec_sql_text function to retrieve the text of the batch based on a specific “sql_handle”. You can also use the sys.dm_exec_query_plan function to retrieve the plan in XML format based on a specific “plan handle”. So the following query returns all the plans currently in cache including the text of each corresponding batch and the plan itself:

Transact-SQL

SELECT BatchText = BatchTexts.text , BatchPlan = BatchPlans.query_plan , CreationDateTime = QueryStats.creation_time , LastExecutionDateTime = QueryStats.last_execution_time FROM sys.dm_exec_query_stats AS QueryStats CROSS APPLY sys.dm_exec_sql_text (QueryStats.sql_handle) AS BatchTexts CROSS APPLY sys.dm_exec_query_plan (QueryStats.plan_handle) AS BatchPlans;

1

2

3

4

5

6

7

8

9

10

11

SELECT

BatchText = BatchTexts.text ,

BatchPlan = BatchPlans.query_plan ,

CreationDateTime = QueryStats.creation_time ,

LastExecutionDateTime = QueryStats.last_execution_time

FROM

sys.dm_exec_query_stats AS QueryStats

CROSS APPLY

sys.dm_exec_sql_text (QueryStats.sql_handle) AS BatchTexts

CROSS APPLY

sys.dm_exec_query_plan (QueryStats.plan_handle) AS BatchPlans;

But this is not useful, because the goal of this view is to provide the statistics about query executions, and we need a way to present the specific statement text and the specific statement plan for each row.

The statement text can be obtained by the values of “statement_start_offset” and “statement_end_offset”. I wrote a simple scalar function that accepts the values of “sql_handle”, “statement_start_offset” and “statement_end_offset”, and returns the text of the relevant statement:

Transact-SQL

CREATE FUNCTION Execution.udf_s_StatementLevelText ( @inSQLHandle AS VARBINARY(64) , @inStatementStartOffset AS INT , @inStatementEndOffset AS INT ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @nvcResult AS NVARCHAR(MAX); SELECT @nvcResult = SUBSTRING ( [text] , @inStatementStartOffset / 2 , ( CASE @inStatementEndOffset WHEN -1 THEN LEN ([text]) ELSE @inStatementEndOffset / 2 END - @inStatementStartOffset / 2 ) + 1 ) FROM sys.dm_exec_sql_text (@inSQLHandle); RETURN @nvcResult; END; GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

CREATE FUNCTION

Execution.udf_s_StatementLevelText

(

@inSQLHandle AS VARBINARY(64) ,

@inStatementStartOffset AS INT ,

@inStatementEndOffset AS INT

)

RETURNS

NVARCHAR(MAX)

AS

BEGIN

DECLARE @nvcResult AS NVARCHAR(MAX);

SELECT

@nvcResult = SUBSTRING

(

[text] ,

@inStatementStartOffset / 2 ,

(

CASE @inStatementEndOffset

WHEN -1 THEN LEN ([text])

ELSE @inStatementEndOffset / 2

END

- @inStatementStartOffset / 2

)

+ 1

)

FROM

sys.dm_exec_sql_text (@inSQLHandle);

RETURN @nvcResult;

END;

GO

In order to retrieve the plan of a specific statement, you can use the sys.dm_exec_text_query_plan function, which accepts a “plan_handle”, “statement_start_offset” and “statement_end_offset”. Notice that this function returns the plan in text format, so you need to convert it to XML. Here is the same query as above, but this time each row presents the text and plan of a single statement along with the average elapsed time for that statement: