Sunday, April 24, 2016

DMV Queries From my Pluralsight Course "What Every Developer Should Know About SQL Server Performance"

I've actually broken these queries up into separate blog posts, so here are the links to the individual posts where you will find the queries.

Exposing SQL Server DMV Data to Developers and other non-DBA Users

Using DMV's to Find SQL Server Connection and Session Info

Which Statements Are Currently Running in my SQL Server Database

Finding the Most Expensive, Longest Running Queries in SQL Server

Analyzing Index Usage in SQL Server
(Contains queries for both missing indexes and index usage stats)

Using DMV's to Find SQL Server Connection and Session Info

It is often times useful to understand how your application is connecting to SQL Server and any other connections that may be present to your application's database.  This can be understood by lookng at the sys.dm_exec_sessions view.  Here is a sample query to use:


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
SELECT
    database_id,    -- SQL Server 2012 and after only
    session_id,
    status,
    login_time,
    cpu_time,
    memory_usage,
    reads,
    writes,
    logical_reads,
    host_name,
    program_name,
    host_process_id,
    client_interface_name,
    login_name as database_login_name,
    last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY cpu_time DESC;


This simply lists all of the current connections to SQL Server, but we also get some useful information with each connection.  We see we have fields like cpu_time (in milliseconds), memory_usage (in 8 KB pages), reads, writes and logical_reads.  So if we have a session that is consuming a lot of resources in SQL Server, we can immediately see that by looking at these fields.

We also have columns like host_name, program_name and host_process_id which can help us identify where a connection is coming from.  You would be surprised how many times you look at a production database and see users connecting directly to the database from applications like Management Studio or even Excel.

We can write a little bit different query to get a different look at this data:


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT
    login_name,
    host_name,
    host_process_id,
    COUNT(1) As LoginCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY
    login_name,
    host_name,
    host_process_id;

This counts up all of the logins by login name and client process.  So if you have an ASP.NET web application, you would see each worker process with the number of connections the worker process has to the database, plus any other processes that might be connected up.  Sometimes this is a useful view, because you can validate that indeed all of the machines in your web cluster are indeed talking to the database and you can get a feel for how many connections they have in their connection pools.

It might seem simple to know who is logged in, but what this can help you do is understand any connectivity issues you may be having and see if there is a session using a lot of resources, so for those reasons, these simple queries against the dm_exec_sessions view can tell you quite a bit.


Saturday, April 23, 2016

Exposing SQL Server DMV Data to developer and other non-DBA Users

As a developer, it is very useful to have access to a number of SQL Server's Dynamic Management Views so you can view performance related statistics about how your application is interacting with the database.  This includes finding which of your SQL Statements are taking the longest, if SQL Server thinks there are any missing indexes and usage data for your existing indexes.

Unfortunately, access to DMV's in SQL Server requires the VIEW SERVER STATE permission.  Many DBA's are reluctant to grant this permission to members of the development team because of the amount of data that one can see when they have this level of access.  Unfortunately, this removes an important set of performance tuning tools from the developer's toolbox or forces them to ask a DBA for help whenever they need this information.  There has to be a better way.

What we need is a way that we can grant to a developer or any other non-DBA type user access to a select subset of DMV's information.  For example, it is useful for a developer to see query execution statistics, missing index information and index usage data.  Further, we would like to limit the information to just the database a developer is working in.  So how do we do this?

You would think this would be easy to do--create a view as a dba user that exposes only the information you want a developer to access and then grant the developer SELECT permission on that view.  This fails though with the following message.

Msg 300, Level 14, State 1, Line 1 VIEW SERVER STATE permission was denied on object 'server', database 'master'. Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.


The next thought you might have is to create a table valued function with EXECUTE AS set to a user that has permissions to view the DMV's.  However, this also fails with an error message complaining about the module not being digitally signed.

There is a solution, but it turned out to be much more complex than I would have initially thought.  My approach is based on this blog post by Aaron Betrand, though I had to do things a little bit differently than he did.

The Solution

What really helps is to visualize the solution before we start going through the individual steps, so here is what it looks like.



What we have to do is create a database (called PerfDB in this example) that is going to be a holding area for a set of table valued functions and views that we create to expose our DMV information.  This database will have the option TRUSTWORTHY ON option set, which will allow our table valued function to execute as a user with VIEW SERVER STATE permissions and access the DMV's (this gets around the error about function not being digitally signed that was mentioned above).  Then, we can create the rest of our objects to expose the DMv information we want to.  This approach gives us a way to expose just the information we want, which is what we are after.

Let's walk through the individual steps we need to take for all of this to work.


Step 1 - Create your PerfDB container database
So to get started, we'll create the PerfDB database.  I did this in SQL Server Management Studio using the UI, mainly because that is the easiest way for me to do it.

Once the database is created, I open a query window and run the following command to set the TRUSTWORTHY option to ON.

1
ALTER DATABASE PerfDB SET TRUSTWORTHY ON;

Some DBA's might have some concerns about setting TRUSTWORTHY to ON, but that is the reason for using a separate database that is just a container for the objects we create.  By separating these objects out into a separate database, we've limited any security risks we might have.


Step 2 - Create the Custom DMV Performance Functions in the PerfView Database
Now we are ready to create a table valued functions which will query DMV's of interest for us.  In this example, I am creating a function that will return back the query execution statistics in SQL Server.  You would create one of these functions for each piece of DMV info you wanted to expose.

The function basically just executes a query against a DMV or DMVs and returns that data in a table object.  In this function, you can see I am taking in a parameter of the database id so that in the next step, I'll be able to able to limit access for a user to only seeing data for one database.

You will need to create this function as a user that has the VIEW SERVER STATE privilege, so this could be an existing user or you could create a separate, dedicated user that owns this function and others like it.  What is important is that this function has the WITH EXECUTE AS SELF option set, so that it will run as the user who created the function.  This will allow this function to query the DMV's even if the calling user does not have permission to see the DMV's.


 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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE FUNCTION dbo.GetSqlExecutionStatistics(@database_id INT)
    RETURNS @x TABLE 
    (
        DatabaseId            INT,
        DatabaseName          VARCHAR(100), 
        ExecutionCount        BIGINT, 
        CpuPerExecution       BIGINT,
        TotalCpu              BIGINT,
        IOPerExecution        BIGINT,
        TotalIO               BIGINT,
        AverageElapsedTime    BIGINT,
        AverageTimeBlocked    BIGINT,
        AverageRowsReturned   BIGINT,
        TotalRowsReturned     BIGINT,
        QueryText             NVARCHAR(max),
        ParentQuery           NVARCHAR(max),
        ExecutionPlan         XML,
        CreationTime          DATETIME,
        LastExecutionTime     DATETIME
    )
    WITH EXECUTE AS OWNER
    AS
BEGIN

    INSERT @x (DatabaseId, DatabaseName, ExecutionCount, CpuPerExecution, TotalCpu, IOPerExecution, TotalIO, 
     AverageElapsedTime, AverageTimeBlocked, AverageRowsReturned, TotalRowsReturned, 
  QueryText, ParentQuery, ExecutionPlan, CreationTime, LastExecutionTime)
 SELECT
        [DatabaseId] = CONVERT(int, epa.value),
        [DatabaseName] = DB_NAME(CONVERT(int, epa.value)), 
        [ExecutionCount] = qs.execution_count,
        [CpuPerExecution] = total_worker_time / qs.execution_count ,
        [TotalCpu] = total_worker_time,
        [IOPerExecution] = (total_logical_reads + total_logical_writes) / qs.execution_count ,
        [TotalIO] = (total_logical_reads + total_logical_writes) ,
        [AverageElapsedTime] = total_elapsed_time / qs.execution_count,
        [AverageTimeBlocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,
        [AverageRowsReturned] = total_rows / qs.execution_count,    
  [TotalRowsReturned] = total_rows,
        [QueryText] = 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),
        [ParentQuery] = qt.text,
        [ExecutionPlan] = p.query_plan,
        [CreationTime] = qs.creation_time,
        [LastExecutionTime] = qs.last_execution_time   
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) p
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE epa.attribute = 'dbid'
     AND CONVERT(int, epa.value) = @database_id;

 RETURN;
END


Step 3 - Create a Database Specific View to Wrap Your Function
I'll create a view specific to the database I want to expose data for and pass in the appropriate database id to the function created in step 2.  The reason I am doing this is because ultimately, I'll give my users SELECT access on this view and not directly on the table valued function.  This way I have the logic (my query) in just one place (the function) that I can use over and over again, but what my users will see is their specific view for their specific database.


1
2
CREATE VIEW AppDatabaseName_SqlExecutionStatistics AS
    SELECT * FROM GetSqlExecutionStatistics( <<application database id>> );

You would replace AppDatabaseName in the name of this view with the name of your application database.  You would also create a view for each different app database you wanted developers to have access to.

If you need to know the database id of your database, this information can be obtained from the sys.databases view in the master database.


Step 4 - Grant Permission for Users to Query From the View
For your user's to be able to query this information, we need to grant them the SELECT privilege to the view we just created in step 4.  But before we do that, we need to map their login to a user in the PerfDB database.  Basically, they have to have access to the database before they can even see the view, so we can do this in the Management Studio GUI or by running the following command.


1
2
3
USE PerfDB;

CREATE <<username>> FROM LOGIN <<user login name>>;

For every user you want to have access to this information, you need to do this.  So that means if you have five developers who need access to this information, you'll need to give all five of those developer logins access to the PerfDB database in this way.

Then, we can grant permission so those user's can query the view.


1
2
GRANT SELECT ON AppDatabaseName_SqlExecutionStatistics 
    TO <<username>>;

You will probably want to create a role that has select permissions for all of the DMV views you create for a database, and then put user's inside of that view.  The important point though is that you have to get the user's permission to be able to select from the view.


Step 5 - Querying the Data
Querying the data is as simple as Querying the view we just created, and this can be done by our non-DBA user who does not have VIEW SERVER STATE permission.


1
2
SELECT * 
    FROM PerfDb.dbo.AppDatabaseName_SqlExecutionStatistics;

If you want to make things a little easier, you can create a synonym in the application database so user's don't have to fully qualify the object name in their queries.


1
2
3
CREATE SYNONYM [dbo].[QueryExecutionStatistics] 
    FOR [PerfDb].[dbo].[AppDatabaseName_SqlExecutionStatistics]
GO 

And now users can simply run this query


1
SELECT * FROM QueryExecutionStatistics;

Now, normal user's who have access to this view can access the information contained within.  This also allows you to selectively choose what information is exposed from your DMV's since you control the query inside of the table valued function and you can limit the results to data from just a single database.

Its unfortunate there isn't a more straightforward way to do this, but at least it can be done.  And of course all of this work can be incorporated into scripts, making it much easier to run.


Saturday, April 2, 2016

Analyzing Index Usage in SQL Server

For our applications, we need to have effective indexes to insure acceptable database performance.  This really boils down into two questions.

Am I missing any indexes that I should have in my database?
If I am missing an index, it is likely that SQL Server will have to resort to a scan operation over the table in order to find the data needed for a SQL Statements.  This means SQL Server will have to read and process each row of the table, which is not only slow but also resource intensive.

Do I have any indexes that are not being used that I should drop?
Indexes are not free.  They are a separate physical structure that SQL Server has to maintain whenever a DML (insert, update or delete) statement is executed against the table.  As long as an index is being used, it is a reasonable trade off to pay the price of maintaining the index because usually, a queries that use the index are run far more frequently than DML statements that have to update the index.  But how do we know this is the case?

These questions can be easily answered using SQL Server dynamic management views.  DBA's have known about these DMV's for years, but many developers do not.  While in some organizations a DBA is fully involved with the development of an application, in my experience this is not the case in most organizations.  So it is the development team that is creating tables and writing SQL for their data access layer.  To do this effectively and have a well performing application, they need visibility to what is happening inside of SQL Server, especially views like the ones I am about to show you that helps someone determine if they have the right indexes created.

So let's see how DMV's can help answer these questions


Finding Missing Indexes

Here is the query we are going to use

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT     
    TableName = d.statement,
    d.equality_columns, 
    d.inequality_columns,
    d.included_columns, 
    s.user_scans,
    s.user_seeks,
    s.avg_total_user_cost,
    s.avg_user_impact,
    AverageCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0), 3),
    TotalCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0) * (s.user_seeks + s.user_scans),3)
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
    ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
    ON d.index_handle = g.index_handle
WHERE d.database_id = db_id()
ORDER BY TableName, TotalCostSavings DESC;

This query works by joining together the sys.dm_db_missing_index_groupssys.dm_db_missing_index_group_stats, and sys.dm_db_missing_index_details views.  It also limits is results to just the current database by virtue of line 18.  If you wanted to get results database wide, you would simply remove this WHERE criteria.

These views give us information on the index SQL Server believes should be created.  This includes the table name (like 2 - no idea why this is called statement in the DMV), the columns that should be part of the index key (lines 3 and 4) and any possible include columns for the index (line 5).  Between these columns, we could construct our create index statement.

We also get some statistics around why SQL Server believes the index should be created.  The user_scans and user_seeks values (lines 6 and 7) represent how many times this index could have been used in one of the operations had the index existed.  So if you see a high number, especially in the user_seeks columns, this means there are large numbers of statements executing that could benefit from this index.

The avg_total_user_cost column (line 8) gives us the average cost of statements that have been run against this table that would benefit from the index.  The avg_user_impact column (line 9) tells us the percent that SQL Server believes this cost would be reduced by creating this index.  From these two values, you can calculate some the average cost savings per statement (line 10) and the total cost savings for all statement executions (line 11) to give you an idea of how significant the cost savings might be.

I would, however, encourage you to not simply go and create an index for every row in this table.  What you want to do is look through the recommendations in this table and look for patterns.  You will find a number of recommendations for each table and many of these will be similar.  So what you want to do is analyze these together and figure out which indexes make sense.  This may also mean modifying an existing index rather than creating a new index.  With this view, you can see all the recommendations that exist for a table and come up with the right set of indexes for the table.



Index Usage Statistics


Let's now tackle the problems of making sure our indexes are being used and identifying indexes that may need to be dropped.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
SELECT
    [DatabaseName] = DB_Name(db_id()),
    [TableName] = OBJECT_NAME(i.object_id),
    [IndexName] = i.name, 
    [IndexType] = i.type_desc,
    [TotalUsage] = IsNull(user_seeks, 0) + IsNull(user_scans, 0) + IsNull(user_lookups, 0),
    [UserSeeks] = IsNull(user_seeks, 0),
    [UserScans] = IsNull(user_scans, 0), 
    [UserLookups] = IsNull(user_lookups, 0),
    [UserUpdates] = IsNull(user_updates, 0)
FROM sys.indexes i 
INNER JOIN sys.objects o
    ON i.object_id = o.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
    ON s.object_id = i.object_id
    AND s.index_id = i.index_id
WHERE 
    (OBJECTPROPERTY(i.object_id, 'IsMsShipped') = 0)
ORDER BY [TableName], [IndexName];


This query uses the sys.dm_db_index_usage_stats table and joins it with the sys.indexes system view.  Again, we are only getting data for the current database, this time because the sys.objects and sys.indexes views only contain data for the current database

We have some information about the index, including the table it is on, the index name and the index type.  In the index type column, two of the most common values you will see are CLUSTERED and NONCLUSTERED.  CLUSTERED refers to a clustered index, which is how SQL Server typically lays out the data for a table.  We really want to focus on NONCLUSTERED indexes, because these are indexes we have created on the table for some specific purpose, and we have control over the columns in those indexes and if they should exist or not.

There are three types of ways an index can be used:

  • user_seeks - SQL Server is traversing the b-tree structure of the index to perform a lookup on a key.  This is the operation we want to see.  SQL Server is using the index as intended.
  • user_scans - SQL Server is reading the entire index to find the value(s) it wants.  If this is a clustered index, that means SQL Server is reading the entire table.  If this is a Nonclustered index, all of the index keys have to be read.  This is much more expensive than a seek operation
  • user_lookups - These are lookup operations against the table, typically when SQL Server is looking up a row in the clustered index structure of a table.
The user_updates column (line 10) gives the maintenance cost of the index -- that is how many time a DML statement has caused this index to need to be updated.

What you want to look for are indexes with very few or zero user_seeks.  These are indexes that you are paying to maintain, but for whatever reason SQL Server is not able to use.  And then you want to investigate why that index is not being used.  Maybe the index is a unique index and is there to only to enforce a constraint.  Maybe something in the application has changed so an index is no longer used.  Maybe the columns are in the wrong order or the index is not selective enough.  You want to figure this out and then either A) modify the index so it can be used or B) drop the index.  These stats give you the visibility into what indexes you need to look at.





What Statements are Running in my SQL Server Database

Sometimes you need to know what statements are running in your SQL Server database right now.  I've often had this need when someone shows up at me desk and says something like "Something is going on!  Your app is performing really slowly right now!".

In situations like this, there is a need to triage what is going on with the app.  Is it application code?  What about the web server, is the CPU spiked there?  What about the database?  Do we have some long running queries that are making things appear to be stuck?  Once we figure out what tier is responsible for the problem, we can drill down further to find and fix specifically what is going on.

To that end, one of the SQL Server queries I keep around is one that will query the SQL Server DMV's and report back all of the statements that are running right now in the database.  For any database that is in use, this query will always return some rows, because generally there is always some process somewhere that is running an application.  But you can also use this query to look for any long running or blocked statements, so lets take a look at the query.


 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
36
37
38
39
40
41
SELECT
        [DatabaseName] = db_name(rq.database_id),
        s.session_id, 
        rq.status,
        [SqlStatement] = SUBSTRING (qt.text,rq.statement_start_offset/2,
            (CASE WHEN rq.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),
            qt.text)) * 2 ELSE rq.statement_end_offset END - rq.statement_start_offset)/2),        
        [ClientHost] = s.host_name,
        [ClientProgram] = s.program_name, 
        [ClientProcessId] = s.host_process_id, 
        [SqlLoginUser] = s.login_name,
        [DurationInSeconds] = datediff(s,rq.start_time,getdate()),
        rq.start_time,
        rq.cpu_time,
        rq.logical_reads,
        rq.writes,
        [ParentStatement] = qt.text,
        p.query_plan,
        rq.wait_type,
        [BlockingSessionId] = bs.session_id,
        [BlockingHostname] = bs.host_name,
        [BlockingProgram] = bs.program_name,
        [BlockingClientProcessId] = bs.host_process_id,
        [BlockingSql] = SUBSTRING (bt.text, brq.statement_start_offset/2,
            (CASE WHEN brq.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),
            bt.text)) * 2 ELSE brq.statement_end_offset END - brq.statement_start_offset)/2)
    FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_requests rq
        ON s.session_id = rq.session_id
    CROSS APPLY sys.dm_exec_sql_text(rq.sql_handle) as qt
    OUTER APPLY sys.dm_exec_query_plan(rq.plan_handle) p
    LEFT OUTER JOIN sys.dm_exec_sessions bs
        ON rq.blocking_session_id = bs.session_id
    LEFT OUTER JOIN sys.dm_exec_requests brq
        ON rq.blocking_session_id = brq.session_id
    OUTER APPLY sys.dm_exec_sql_text(brq.sql_handle) as bt
    WHERE s.is_user_process =1
        AND s.session_id <> @@spid
 AND rq.database_id = DB_ID()  -- Comment out to look at all databases
    ORDER BY rq.start_time ASC;


This query is using the sys.dm_exec_sessions view in conjunction with the sys.dm_exec_requests view to get all of the current running statements in SQL Server.  I am restricting this to the current database (line 39), but you can easily comment out this line to look database wide.

With just these two views, we do have some useful information like what time this statement started executing (line 13), how long it has been running so far (line 12), how much CPU it has consumed so far (line 14) and how many logical reads it has performed so far (line 15).  If we see a statement that has been running for a long time and/or has already consumed a lot of CPU and IO resources, then this is casuse for further investigation.

I also like to get some information about who is running a SQL statement that might be taking a long time, and that is what lines 8 through 11 do.  You would be amazed at how often someone logs into a production database from their desk and runs an ad-hoc query in the middle of the day from management studio or even Excel and has no idea that they are causing a performance impact on the database.  Columns like this help to pinpoint what process is running a statement that might be consuming a lot of resources.

Then, I'm pulling in some other useful information that we can look at in our result set.  Bringing in the sys.dm_exec_sql_text (line 30) allows us to include the statement that is running in the result set, which of course if a statement is taking a long time you want to know what the statement is.  We also use sys.dm_exec_query_plan (line 31) to pull in the execution plan for the statement.  If something is taking a long time, you probably want to just be able to click through and see the execution plan, so this lets us do that.

Finally, sys.dm_exec_requests contains a column called blocking_session_id.  If this statement is currently blocked by another, this value will be non-zero.  By joining this value back to the sys.dm_exec_seesions and sys.dm_exec_requests views a second time (lines 32-36), you can get information about the blocker (lines 21-26), like the blocking SQL and information about the program running that SQL.  Sometimes, you may catch a statement that is blocked when you run this query, but if you run this query twice in a row and you see the same blocker, you know you have a problem.

All in all, this query gives you the ability to get a good pulse on what is happening in your database right now.  When you are in the middle of troubleshooting a performance problem, this is very useful, because at a glance, you can determine if the problem you are facing is in the database tier and if so, probably get a pretty good idea of what might be the cause.  So keep this query handy, and next time you need to know what is happening right now inside of SQL Server, you will be prepared.

Finding the Most Expensive, Longest Running Queries in SQL Server

One of the things we often want to know is what are our worst performing statements in our application so we can fix those statements and improve our overall performance.  We can easily do this by querying the dynamic management views in SQL Server.

One of the great advantages of using the DMV's in this way is that we immediately get a global view of what our application is doing inside of SQL Server and where we might need to concentrate our performance tuning efforts.  The statement below gives us a good idea of what statements are being run from our application, how often, how long they take and how many resources they use on average.  That is a lot of information for the low cost of running a single query.

To run this query, you will need to VIEW SERVER STATE permission in SQL Server.  If you don't have this permission, ask your DBA to run this query for you and give you the results.

OK, lets look at the query


 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
SELECT TOP 20    
        DatabaseName = DB_NAME(CONVERT(int, epa.value)), 
        [Execution count] = qs.execution_count,
        [CpuPerExecution] = total_worker_time / qs.execution_count ,
        [TotalCPU] = total_worker_time,
        [IOPerExecution] = (total_logical_reads + total_logical_writes) / qs.execution_count ,
        [TotalIO] = (total_logical_reads + total_logical_writes) ,
        [AverageElapsedTime] = total_elapsed_time / qs.execution_count,
        [AverageTimeBlocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,
     [AverageRowsReturned] = total_rows / qs.execution_count,    
     [Query Text] = 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),
        [Parent Query] = qt.text,
        [Execution Plan] = p.query_plan,
     [Creation Time] = qs.creation_time,
     [Last Execution Time] = qs.last_execution_time   
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) p
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE epa.attribute = 'dbid'
        AND epa.value = db_id()
    ORDER BY [AverageElapsedTime] DESC; --Other column aliases can be used

This query selects the 20 statements in the current database that have the longest average elapsed time, that is, the 20 statements that on average took the longest to run.  

  • If you want more statements, then you can modify the "TOP 20" statement in line 1.  You could also just remove the "TOP 20" criteria to get all of the statements running in this database.
  • You can sort the list by different criteria.  CpuPerExecution and IOPerExecution are also good choices because these tell you what statements take the most resources to run.
  • If you have a batch process, you may get a few statements that were only run once or twice at the top of this list, because statements run in a batch process tend to be expensive by nature.  Just add a criteria in the WHERE clause to only show statements with an execution count > 10, 20 or whatever if you want to exclude these.
What units is the data in?
  • The elapsed time and CPU time numbers are in microseconds, but the MSDN page claims these values are only accurate to the millisecond
  • The number of IO operations is in number of pages
What should I look for?
  • Statements that take a long time to run on average.  For line of business applications, I want to understand anything taking over 500 ms.  But if a statement takes 400 ms and every other statement takes only 50 ms, then I want to look at the 400 ms statement and see what is going on.
  • Any statement that has a high amount of average CPU or IO usage relative to its peers.
  • Statements with high numbers of executions.  Is this legit?  Is the app firing off this statement more than necessary?  Is there a caching opportunity here?
The query above will return the execution plan for each statement in this list, so you can quickly click into the plan to see what is happening and if you want to take further action

What if I want to see a different database on my SQL Server?
  • This query is set up to look at the current database the user is in (line 25 - epa_value = db_id()).  So just switch to the other database or comment out this line to see data for all statements across SQL Server
How current is the data returned by this query?
  • The data for this query comes from SQL Server's plan cache, which generally holds onto execution plans for statements that have been executed in the last few hours.  Its a good idea to run this query a few different times throughout the day to capture any different loads that might be put on the database.  Most of the data will probably agree, but a few different runs will give you a more complete overall picture.


In my opinion, this is probably the single most useful query of the DMV's because it immediately tells me if there are statements that aren't performing well and which ones they are.  If you have a performance issue in your database, this query will take you right to the heart of what is going on.