Chapter 29. My favorite DMVs, and why
This chapter covers some of my favorite Dynamic Management Views and Dynamic Management Functions in SQL Server 2005 and SQL Server 2008. I will start out by giving some background about DMVs. Then I will list some details about the individual views and functions that I use most frequently. Finally, I will show you some interesting queries you can use to take full advantage of the information that these objects expose.
What is so great about DMVs, anyway?
As an architect first and foremost, I have always been interested in the performance and usage information I can extract out of my SQL Server instances. In versions prior to SQL Server 2005, there was a wide range of ways to get this information, such as cryptic DBCC commands, bit flags in system tables (mostly undocumented), and system stored procedures (some unsupported). SQL Server 2005 introduced us to a new way to derive this data: Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), collectively known as DMVs.
DMVs brought about a more consistent way to obtain the existing information we were used to retrieving. They also introduced new data that we had never had access to before, or could only access via auditing, extensive tracing, or memory dumps. Consistency is important, as we finally have a relatively stable set of conventions for how the DMVs and DMFs are named, present common data types and column names, and exist in the sys schema.
Another aspect about DMVs that I appreciate significantly over system stored procedures and DBCC commands is that I can query against them directly, making it easy to pull only the columns and rows that I want. As an example, in the past, if I only wanted to see sessions that were blocking or being blocked, I would have to create a temporary table up front, insert the results of sp_who2, and run filtered queries against the temporary table (or squint at the whole result set, scrolling up and down).
You should note a couple of important things about DMVs. First, they only contain data since the last time the instance was started. All metrics are literally thrown away when the SQL Server service is shut down. You can make this data persist between restarts, but that is out of scope for this topic (Paul Randal discusses the idea of persisting DMV data across SQL Server restarts at http://www.sqlskills.com/BLOGS/PAUL/post/Indexes-From-Every-Angle-How-can-you-tell-if-an-index-is-being-used.aspx, and there is also the DMVStats CodePlex project from Tom Davidson and Sanjay Mishra, available at http://www.codeplex.com/sqldmvstats/). Because they also require VIEW SERVER STATE or VIEW DATABASE STATE permissions, depending on the DMV, it is not always possible to have access to the complete set of information that is available.
I feel that a couple of things are missing. For example, there is no DMV coverage whatsoever of SQL Server Agent, which has a crude API. This makes it tedious to cull information about jobs and schedules. And there is no way to extend the DMVs by creating my own custom views—this was one of my initial questions when the first beta version of SQL Server 2005 arrived in my mailbox. It is also impossible to distinguish, by name alone, between DMVs and DMFs. When using SQL Server Management Studio in SQL Server 2008, and against a SQL Server 2008 instance, IntelliSense will help out a little bit here. The AutoComplete list uses an icon to indicate whether it is a view or a function. Without this hint, I have often queried a DMF without any parameters:
SELECT * FROM sys.dm_exec_sql_text;
That results in this error:
Msg 216, Level 16, State 1, Line 1
Parameters were not supplied for the function 'sys.dm_exec_sql_text'.
This ambiguous naming convention is not helpful to me; similar to the way the same column can have a different name from one system table to the next. This is not the most efficient aspect of my own personal work. It has become a pattern of trial and error for me. The AutoComplete list has accelerated my learning curve when working against SQL Server 2008. I could look at the catalog views to determine the type, but this is a hard habit to get into when writing code and stumbling upon new views and functions in SQL Server 2008.
In a few isolated cases, information that we used to see in system tables has not been carried over to the new DMV structure. I will mention one of these cases briefly when I talk about sys.dm_exec_sessions further on. Thankfully, there is a decent workaround, but I am surprised at this well-publicized omission. It was reported early in the SQL Server 2005 development cycle, and has not been corrected in SQL Server 2008.
I don’t want to write an entire chapter about the background and underlying concepts behind DMVs. I urge you to peruse the Books Online documentation to learn more about them. The starting point in the documentation, which has both SQL Server 2005 and SQL Server 2008 branches, is at the following URL: http://msdn.microsoft.com/en-us/library/ms188754.aspx.
What I want to do is to share with you my most-often used DMVs, and some of the neat things you can do with them. First I will list and explain the objects I will use, and then I will show you some sample code that I currently use in my own production environments. I will also indicate which objects are new in SQL Server 2008.
A brief list of my favorite DMVs and DMFs
Here is some background information on the views and functions that I will be using in sample code later in the chapter.
sys.dm_os_sys_info
The first DMV I want to mention is sys.dm_os_sys_info, because I use it in many places further on. This view makes it easy to gather data about the underlying system, such as the amount of physical memory installed, the number of CPUs, and the number of buffers in the buffer pool.
One of the important columns added in SQL Server 2008 is sqlserver_start_time, which is useful in determining how much information is there when querying DMVs (because, as previously discussed, all of the data is discarded when the instance is restarted). When I am using SQL Server 2005, an easy workaround is to grab the created date/time of the tempdb database, or the login_time of spid 1 from sys.sysprocesses, both of which are reliable and usually close enough.
sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_exec_connections
sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_exec_connections are a complex replacement for the old system table sysprocesses, the system procedure sp_who, and the famously undocumented and unsupported system procedure sp_who2.
sys.dm_exec_sessions returns “one row per authenticated session on SQL Server.” Long-time DBAs can translate this to mean “one row per SPID.” Note that what we used to call a SPID is now referred to throughout all of the DMVs as session_id. The information provided here includes login information, host name, program name, status, environment settings, and metrics such as CPU, memory, and I/O used by the session. An important column that has been left out of this view is database_id. We used to be able to get this information for all SPIDs in sysprocesses, but now it is not accessible if the session_id does not have an active request (in which case you can get the information from other DMVs). Because of this, I continue to have to rely on sys.sysprocesses, which is provided for backward compatibility but in my opinion is still an essential view.
sys.dm_exec_requests returns data for all of the requests currently active against the server. An important note here is that it is possible to have multiple rows for any given session_id (for example, when executing a query where the degree of parallelism (DOP) > 1). The data includes start time, status, type of command (for example, SELECT), and database information, and provides hooks that make it possible to determine the query being executed (or object being called). The data also includes blocking information, environment settings in place when the request started, and the CPU, memory, and I/O required by the request. Finally, in some scenarios (for example, BACKUP DATABASE) the view exposes how far along the process is, and the estimated completion time. In SQL Server 2008, there is also a group_id column for Resource Governor information, and the same query_plan and query_plan_hash columns that were added to sys.dm_exec_query_text.
sys.dm_exec_connections provides details about each connection attached to SQL Server. This includes connection time, protocol information, authentication method, and IP address.
Note that sys.sysprocesses is still provided for backward compatibility purposes. As I mentioned before, this is a good thing—in certain scenarios, this is the only place where database context is provided.
I use these DMVs almost exclusively as a package deal to create stored procedures that allow me to provide relational data about specific things that are going on in my instances that would otherwise require a lot of digging. Later I will show my own version of sp_who, which is much richer than what is provided out of the box.
sys.dm_exec_sql_text
sys.dm_exec_sql_text returns a table containing the database ID, object ID, and text of the SQL query. It also returns the number for deprecated numbered procedures, and a BIT indicating whether the SQL text is encrypted. I use this to correlate information in the sessions, requests, and connections DMVs so I can see exactly what queries these requests are running. This function can be applied inline when pulling data from DMVs like sys.dm_exec_requests, unlike the old ways to get query text (DBCC INPUTBUFFER or ::fn_get_sql()).
sys.dm_exec_query_stats
sys.dm_exec_query_stats returns a row for every query plan in the cache. I use this to find the queries that are being recompiled the most, and also to find inefficient query plans. The DMV returns information including the last compile time, number of times the query has been compiled, and various metrics about each plan including execution counts, physical and logical I/O, and minimum, maximum, and total elapsed times. In SQL Server 2008, two new columns were added. These are query_hash and query_plan_hash, which make it easier to aggregate queries with similar (but not identical) execution plans.
sys.dm_exec_procedure_stats
sys.dm_exec_procedure_stats contains a large number of metrics about all stored procedures that currently have plans in the cache. Like sys.dm_exec_query_stats, there are details about execution counts, I/O, and elapsed time. I use it to determine which of my stored procedures are running long, or to capture trends about stored procedures that are being executed more frequently. Prior to SQL Server 2008, this information wasn’t readily available except via a SQL trace, or through custom logging inside each stored procedure or in the applications that call them. There is another companion DMV called sys.dm_exec_trigger_stats, but because I am not a big trigger user, it is less useful to me. I am hoping that in a future version they add another DMV, sys.dm_exec_function_stats. Then we will be able to better monitor the usage and efficiency of those objects.
sys.dm_db_index_usage_stats
This view returns the count and last occurrence of user (and system) seeks, scans, lookups, and updates. I use this to find indexes which are not very useful, for example where writes far outweigh reads. By comparing the number of reads to the number of writes, I can easily see where some indexes are being overtaxed or underutilized.
sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, - and sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_group_stats allow to me find out which indexes might have been useful if they existed. By doing the work of the Database Engine Tuning Advisor (DETA) behind the scenes, the views let me quickly see where and how seeks or index scans could have improved queries that previously had to perform table scans due to the lack of an index. The recommendations here are not always stellar (for example, they are overly fond of using included columns), but they can provide a good starting point.
Honorable mentions
I didn’t want to ignore a few objects that I use often. Due to limited space, I couldn’t demonstrate queries for all of them. I urge you to investigate these and other objects on your own to get a better understanding of how they can make your database work easier.
sys.dm_os_performance_counters
Indispensable when troubleshooting, sys.dm_os_performance_counters allows me to gather various SQL-related performance counters in a relational format (note that you cannot query this DMV for counters that are not SQL-related). From Page Life Expectancy, to Logins per Second, to Lock Timeouts, to Cache Hit Ratios, it’s all there, and much easier to consume and correlate than with a separate Performance Monitor session or log file.
sys.dm_db_partition_stats
Before SQL Server 2005, I had to rely on the system table sysindexes to determine the size and number of rows of a table, and had to be sure to run DBCC UPDATEUSAGE with COUNT_ROWS first; otherwise, acknowledge that the information may not be 100 percent accurate. Now sys.dm_db_partition_stats can tell me—accurately, and across all databases in my instance—which tables have the most rows, what is the number of pages, and how much LOB and other data is being stored off-row. This sure beats using the undocumented and unsupported sp_msForEachDatabase and sp_msForEachTable procedures to execute sp_spaceused for each database and table, and trying to collate the results myself.
sys.dm_db_index_physical_stats
This table-valued DMF provides physical information about each index and partition in a table. For each b-tree level, IN_ROW_DATA allocation unit, LOB_DATA allocation unit and ROW_OVERFLOW_DATA allocation unit in each partition, a row is returned that includes the index and allocation unit type, fragment counts and sizes, average fragmentation percentage, page count, and row count. I use this view to obtain a picture of indexes that are candidates for rebuilding or reorganizing. This is slightly better than an educated guess, less work than running DBCC SHOWCONTIG over and over again, and much better than blindly running maintenance against all indexes on a fixed schedule. I have used various adaptations of Ola Hallengren’s index optimization scripts. They are located here, and are definitely worth some investigation: http://ola.hallengren.com/.
When composing this topic, I was asked the question, why is this DMF better than using DBCC SHOWCONTIG, given that they produce the same results? Well, for one, it is easier to apply a WHERE clause to the DMV, to limit the output to a set of tables without having to make multiple calls (or parse the verbose output to find the tables of interest). I can also use the DMV in a query that includes other tables or views. I can easily join against, say, sys.dm_db_partition_stats to get the row counts, and sys.indexes to get the index name and other details. The query in listing 1 demonstrates this.
Listing 1. Sample query against sys.dm_db_index_physical_stats
SELECT TOP 10
[object_name] = OBJECT_NAME(s.[object_id]),
index_name = i.name,
i.is_primary_key,
i.is_unique_constraint,
s.partition_number,
s.index_type_desc,
s.alloc_unit_type_desc,
s.avg_fragmentation_in_percent,
s.page_count,
p.reserved_page_count,
p.row_count
FROM
sys.dm_db_index_physical_stats
(
DB_ID(), NULL, NULL, NULL, NULL
) AS s
INNER JOIN
sys.dm_db_partition_stats AS p
ON
s.[object_id] = p.[object_id]
AND s.partition_number = p.partition_number
AND s.index_id = p.index_id
INNER JOIN
sys.indexes i
ON
s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE
OBJECT_NAME(s.[object_id]) LIKE 'pattern%'
ORDER BY
OBJECT_NAME(s.[object_id]),
s.index_id;
Try doing all of that with DBCC SHOWCONTIG.
sys.dm_sql_referenced_entities
sys.dm_sql_referenced_entities (and its companion, sys.dm_sql_referencing_entities) should make dependency tracking in SQL Server 2008 much better, by maintaining information about references between objects in a more reliable way than sysdepends did. A few MVPs have found some bugs in these views, but I wrote a lengthy article about helping to automate dependency tracking slightly more accurately than the engine currently does on its own, using the new catalog view sys.sql_expression_dependencies. This was also introduced in SQL Server 2008: http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx.
Setting up a utility database
Before I get started with some code, I’d like to get a few maintenance tasks out of the way. I usually create a utility database for generic stored procedures and functions. Many of the stored procedures I use on my own systems are system-wide and do not need to run in the context of any specific database. (There are exceptions. Other procedures I create further on do need to be created within each database.)
One of the things I do a lot for reporting purposes is to construct a two- or three-part name (schema.object or database.schema.object). Typically I would do this as follows (at least in SQL Server 2005 SP2 and above), given an @object_id in the current database:
SELECT ThreePartName = QUOTENAME(DB_NAME(DB_ID())) + '.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(@object_id, DB_ID()))
+ QUOTENAME(OBJECT_NAME(@object_id, DB_ID()));
Because this can become quite tedious, I create two user-defined functions in my utility database, dbo.AB_GetTwoPartName() and dbo.AB_GetThreePartName(), to perform this work for me.
Another function I create in my utility databases is dbo.AB_Uptime(), which returns the start time of the SQL Server service and number of minutes the instance has been running. Note that I have not tried this on a server that has been running for 4,000 years, which would overflow the INT return value of the DATEDIFF() function. In SQL Server 2008 I use sys.dm_os_sys_info, but in SQL Server 2005, I must use other workarounds. You can use the SQL Server 2005 version of the function I include below if you are happy enough with using tempdb as your start time indicator. You could also parse the error logs or keep your own log using a startup procedure.
And finally, because I am looking at the results of sys.dm_exec_sql_text() often, and because extracting the relevant statement in the output is complex when the object is a stored procedure or function (because it includes the text for the entire object), I bury this logic in the utility database, in a function called dbo.AB_ParseSQLText(). Believe me, the logic looks much more complex in an already complicated query, when using columns like statement_start_offset instead of simple INT parameters. You can see this code in listing 2.
Listing 2. Creating a utility database
IF DB_ID(N'AB_Utility') IS NULL
CREATE DATABASE AB_Utility;
GO
USE AB_Utility;
GO
IF OBJECT_ID(N'dbo.AB_GetThreePartName', N'FN') IS NOT NULL
DROP FUNCTION dbo.AB_GetThreePartName;
GO
CREATE FUNCTION dbo.AB_GetThreePartName
(
@object_id INT,
@database_id INT
)
RETURNS NVARCHAR(1000)
AS
BEGIN
RETURN (QUOTENAME(DB_NAME(@database_id)) + '.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(@object_id, @database_id))
+ '.' + QUOTENAME(OBJECT_NAME(@object_id, @database_id)));
END
GO
IF OBJECT_ID(N'dbo.AB_GetTwoPartName', N'FN') IS NOT NULL
DROP FUNCTION dbo.AB_GetTwoPartName;
GO
CREATE FUNCTION dbo.AB_GetTwoPartName
(
@object_id INT,
@database_id INT
)
RETURNS NVARCHAR(1000)
AS
BEGIN
RETURN (QUOTENAME(OBJECT_SCHEMA_NAME(@object_id, @database_id))
+ '.' + QUOTENAME(OBJECT_NAME(@object_id, @database_id)));
END
GO
IF OBJECT_ID(N'dbo.AB_Uptime', N'IF') IS NOT NULL
DROP FUNCTION dbo.AB_Uptime;
GO
/* -- you should uncomment one of these functions
-- depending on the version of your instance:
-- SQL Server 2008 :
CREATE FUNCTION dbo.AB_Uptime()
RETURNS TABLE
AS
RETURN
(
SELECT
sqlserver_start_time,
uptime = DATEDIFF(MINUTE, sqlserver_start_time, CURRENT_TIMESTAMP)
FROM
sys.dm_os_sys_info
);
-- SQL Server 2005:
CREATE FUNCTION dbo.AB_Uptime()
RETURNS TABLE
AS
RETURN
(
SELECT
sqlserver_start_time = create_date,
uptime = DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP)
FROM
sys.databases
WHERE
name = 'tempdb'
);
*/
IF OBJECT_ID(N'dbo.AB_ParseSQLText', N'FN') IS NOT NULL
DROP FUNCTION dbo.AB_ParseSQLText;
GO
CREATE FUNCTION dbo.AB_ParseSQLText
(
@text NVARCHAR(MAX),
@start INT,
@end INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN
(
SELECT COALESCE
(
SUBSTRING
(
@text,
@start / 2 + 1,
(CASE WHEN @end = -1
THEN (DATALENGTH(@text))
ELSE @end
END - @start)/2 + 1
),
N''
)
);
END
GO
Now that I have my underlying utility functions set up, I can get on with generating some interesting code for you to use on your own systems.
Some interesting applications of my favorite DMVs
In this section, I’ll show you a number of ways you can use the DMVs I’ve covered above, beginning with a more refined version of sp_who.
A more refined sp_who or sp_who2
We’ve all used sp_who or sp_who2 at some point; however, I often find myself digging deeper and handcrafting queries myself, either to obtain more information than the procedures provide, or to whittle down the rows. This way, I can focus on the most important activity at the time. Both sp_who and sp_who2 provide a parameter @loginame, which enables you to pull activity for a specific session_id, or for a specific login, or for all active queries (those that are not sleeping, awaiting command, lazy writer, or checkpoint sleep). But I often want to search on other parameters (such as database name or a pattern match on the command) or limit the result set to only those sessions that are blocking or being blocked. The following procedure is a bit lengthy, and tries for the most part to mimic sp_who and sp_who2, but hopefully the code (and the sample calls that follow) will give you some ideas about how to write your own enhanced routines that allow you to dig into an issue without having to try too hard. An example of this is shown in listing 3.
Listing 3. An sp_who2 replacement
USE AB_Utility;
GO
IF OBJECT_ID(N'dbo.AB_who', 'P') IS NOT NULL
DROP PROCEDURE dbo.AB_who;
GO
CREATE PROCEDURE [dbo].[AB_who]
@loginame SYSNAME = NULL,
@ShowBlockersOnly BIT = 0,
@ShowSystemSpids BIT = 0,
@SearchDBName NVARCHAR(255) = N'%',
@SearchHostName VARCHAR(255) = N'%',
@SearchCommand NVARCHAR(255) = N'%'
AS
BEGIN
SET NOCOUNT ON;
WITH list AS
(
SELECT
SPID = s.session_id,
BlockedBy = NULLIF(r.blocking_session_id, 0),
[database] = DB_NAME(COALESCE(r.database_id, sp.[dbid])),
[host_name] = COALESCE(s.[host_name], ''),
ip_address = COALESCE(c.client_net_address, ''),
[program_name] = COALESCE(s.[program_name], ''),
login_name = COALESCE(UPPER(s.login_name), ''),
[status] = UPPER(COALESCE(r.[status], s.[status])),
command = UPPER(COALESCE(r.command, '')),
logical_reads = COALESCE(r.logical_reads, s.logical_reads),
num_reads = COALESCE(r.reads, c.num_reads),
num_writes = COALESCE(r.writes, c.num_writes),
cpu_time = COALESCE(r.cpu_time, s.total_scheduled_time),
row_count = COALESCE(r.row_count, s.row_count),
memory_in_pages = COALESCE(r.granted_query_memory,
s.memory_usage),
s.login_time,
last_request_start_time = COALESCE(
r.start_time, s.last_request_start_time),
request_active = CASE
WHEN UPPER(s.[status]) IN
('SLEEPING', 'BACKGROUND')
THEN 0
ELSE 1
END,
wait_type = COALESCE(r.wait_type, r.last_wait_type, ''),
exec_sql = CASE
WHEN qs.[sql_handle] IS NULL
THEN COALESCE(est.[text], '')
ELSE ''
END,
exec_object = COALESCE(AB_Utility.dbo.AB_GetThreePartName(
est.objectid, est.[dbid]), ''),
exec_statement = AB_Utility.dbo.AB_ParseSQLText(est.[text],
qs.statement_start_offset, qs.statement_end_offset)
FROM
sys.dm_exec_sessions AS s
LEFT OUTER JOIN
sys.sysprocesses AS sp
ON s.session_id = sp.spid
LEFT OUTER JOIN
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
LEFT OUTER JOIN
sys.dm_exec_connections AS c
ON s.session_id = c.session_id
LEFT OUTER JOIN
sys.dm_exec_query_stats AS qs
ON qs.[sql_handle] = COALESCE(
r.[sql_handle], c.most_recent_sql_handle)
OUTER APPLY
sys.dm_exec_sql_text(COALESCE(
r.[sql_handle], c.most_recent_sql_handle,
qs.[sql_handle])) AS est
)
SELECT
*
FROM
list
WHERE
SPID = CASE
WHEN @loginame LIKE '[0-9]%'
THEN CONVERT(INT, @loginame)
ELSE SPID
END
AND SPID > CASE
WHEN @ShowSystemSpids = 1
THEN 0
ELSE 50
END
AND login_name = CASE
WHEN @loginame LIKE '[^0-9]%'
AND UPPER(@loginame) <> 'ACTIVE'
THEN UPPER(@loginame)
ELSE login_name
END
AND request_active = CASE
WHEN UPPER(@loginame) = 'ACTIVE'
THEN 1
ELSE request_active
END
AND UPPER([database]) LIKE UPPER(@SearchDBName)
AND UPPER([host_name]) LIKE UPPER(@SearchHostName)
AND
(
UPPER(exec_statement) LIKE UPPER(@SearchCommand)
OR UPPER(exec_object) LIKE UPPER(@SearchCommand)
OR UPPER(exec_sql) LIKE UPPER(@SearchCommand)
)
AND
(
EXISTS
(
SELECT 1
FROM list b1
WHERE b1.BlockedBy = CASE
WHEN @ShowBlockersOnly = 1
THEN list.SPID
ELSE b1.BlockedBy
END
)
OR ISNUMERIC(CASE
WHEN @ShowBlockersOnly = 1
THEN BlockedBy
ELSE '1'
END) = 1
)
ORDER BY
SPID;
END
GO
-- capture only those sessions that are actively running queries
EXEC AB_Utility.dbo.AB_who active;
-- capture only those sessions logged in as sa
EXEC AB_Utility.dbo.AB_who N'sa';
-- capture only spid 55
EXEC AB_Utility.dbo.AB_who 55;
-- capture only those blocking or being blocked
EXEC AB_Utility.dbo.AB_who @ShowBlockersOnly = 1;
-- include system spids
EXEC AB_Utility.dbo.AB_who @ShowSystemSpids = 1;
-- capture only those in the database AB_Utility
EXEC AB_Utility.dbo.AB_who @SearchDBName = 'AB_Utility';
-- capture only those running from the host machine "GREENLANTERN"
EXEC AB_Utility.dbo.AB_who @SearchHostName = 'GREENLANTERN';
-- capture only those running this procedure
EXEC AB_Utility.dbo.AB_who @SearchCommand = '%ab_who%';
Getting statistics for stored procedures (SQL Server 2008 only)
You can use the table-valued function in listing 4 to find all of the stored procedures that are being called frequently, or are taking a long time, or both.
Listing 4. Table-valued function for procedure statistics
USE AB_Utility;
GO
IF OBJECT_ID(N'dbo.AB_GetProcedureStats', N'IF') IS NOT NULL
DROP FUNCTION dbo.AB_GetProcedureStats;
GO
CREATE FUNCTION dbo.AB_GetProcedureStats
(
@database_name SYSNAME
)
RETURNS TABLE
AS
RETURN
(
SELECT
[name] = AB_Utility.dbo.AB_GetThreePartName
(p.[object_id], p.[database_id]),
u.sqlserver_start_time,
uptime_minutes = u.uptime,
p.execution_count,
executions_per_minute = CONVERT(DECIMAL(15,2),
(p.execution_count * 1.0 / u.uptime)),
max_time_milliseconds = p.max_elapsed_time / 1000,
avg_time_milliseconds = CONVERT(DECIMAL(15,2),
((1.0 * p.total_elapsed_time / 1000) / p.execution_count))
FROM
sys.dm_exec_procedure_stats AS p
CROSS JOIN
AB_Utility.dbo.AB_Uptime() AS u
WHERE
p.[database_id] = (COALESCE(DB_ID(@database_name),
p.[database_id]))
AND p.[database_id] <> 32767 -- resource database
);
GO
If you want to only return procedures that have executed at least 50 times, or only want to include those that take an average of 75 milliseconds or more, you can easily add those filters to the WHERE clause:
SELECT *
FROM dbo.AB_GetProcedureStats('msdb')
WHERE execution_count >= 50
-- WHERE avg_time_milliseconds >= 75
ORDER BY avg_time_milliseconds DESC;
Finding unused stored procedures (SQL Server 2008 only)
I often see the question, “How do I tell which stored procedures are not being used?” You can determine which procedures in a database have not been used recently by checking those that are not represented in sys.dm_exec_procedure_stats. Take note that there are no guarantees here. If you want to be certain, you should use auditing or your own logging methods to determine stored procedure usage. One common problem is that there are stored procedures that are run for monthly or annual reports, and sometimes these haven’t been run since the last time SQL Server was started. Try to make sure that your uptime covers a full business cycle. This can still give you a decent starting list of under-utilized procedures, especially if SQL Server has been running for a long time.
Because this procedure needs to pull data directly from database-specific catalog view sys.procedures, you will need to create it in each database where you want to use it. This is a common scenario, and when I am setting up a new SQL Server instance, I typically create copies of all such utility-related objects in the model database. This way, I know they will be available in all user databases created from that point on. The procedure is shown in listing 5.
Listing 5. Finding unused stored procedures
USE [your_database];
GO
IF OBJECT_ID('dbo.AB_GetUnusedProcedures', N'P') IS NOT NULL
DROP PROCEDURE dbo.AB_GetUnusedProcedures;
GO
CREATE PROCEDURE dbo.AB_GetUnusedProcedures
AS
BEGIN
SET NOCOUNT ON;
SELECT
'These procedures have not been executed in the past '
+ RTRIM(uptime) + ' minutes (the last time SQL started)',
sqlserver_start_time
FROM
AB_Utility.dbo.AB_Uptime();
SELECT
[name] = AB_Utility.dbo.AB_GetTwoPartName
(p.[object_id], DB_ID()),
p.create_date,
p.modify_date
FROM
sys.procedures AS p
LEFT OUTER JOIN
sys.dm_exec_procedure_stats AS ps
ON
p.[object_id] = ps.[object_id]
WHERE
ps.[object_id] IS NULL
ORDER BY
p.[Name];
END
GO
If you want to avoid creating this object in each user database, you can create it in the master database so that it can be called from any user database with the original database context. Note that you must change the prefix to sp_ in order to use this method. For example, you would change the code from listing 5 to the code in listing 6. Note that I also changed the [name] function to return a three-part name; therefore, you can be sure it is being called from the right place.
Listing 6. Making a system stored procedure
USE [master];
GO
IF OBJECT_ID('dbo.sp_AB_GetUnusedProcedures', N'P') IS NOT NULL
DROP PROCEDURE dbo.sp_AB_GetUnusedProcedures;
GO
CREATE PROCEDURE dbo.sp_AB_GetUnusedProcedures
AS
BEGIN
SET NOCOUNT ON;
SELECT
'These procedures have not been executed in the past '
+ RTRIM(uptime) + ' minutes (the last time SQL started)',
sqlserver_start_time
FROM
AB_Utility.dbo.AB_Uptime();
SELECT
[name] = AB_Utility.dbo.AB_GetThreePartName
(p.[object_id], DB_ID()),
p.create_date,
p.modify_date
FROM
sys.procedures AS p
LEFT OUTER JOIN
sys.dm_exec_procedure_stats AS ps
ON
p.[object_id] = ps.[object_id]
WHERE
ps.[object_id] IS NULL
ORDER BY
p.[Name];
END
GO
EXEC dbo.sp_MS_marksystemobject N'dbo.sp_AB_GetUnusedProcedures';
GO
USE [your_database];
GO
EXEC dbo.sp_AB_GetUnusedProcedures;
GO
Warning
Although creating objects in the master database has been a relatively safe and well-known method for years, please proceed with the understanding that you may need to change it later. It is not documented, not supported, and likely to cease working in some future version of SQL Server. The main problem with using undocumented methods is that Microsoft does not need to warn you before they change or remove the functionality; therefore, your next upgrade might end up being a lot more work than you thought.
Finding inefficient and unused indexes
The following query will help you identify indexes in your database that are not used at all, or are used more during maintenance operations than for improving query performance. As with the query to find unused procedures, what you do with this information will rely heavily on how long SQL Server has been up and running. If you restarted SQL Server this morning, then these statistics may not yet represent an adequate sample of your workload. And as with the unused procedure code, you will need to create this object in each relevant database, because it returns metadata from the local catalog view sys.indexes. (If you want to use the system object technique, the changes to the code are similarly simple.) This code is shown in listing 7.
Listing 7. Measuring the usefulness of indexes
USE [your_database];
GO
IF OBJECT_ID('dbo.AB_MeasureIndexUsefulness', N'P') IS NOT NULL
DROP PROCEDURE dbo.AB_MeasureIndexUsefulness;
GO
CREATE PROCEDURE dbo.AB_MeasureIndexUsefulness
AS
BEGIN
SET NOCOUNT ON;
SELECT 'These indexes have collected statistics for the past '
+ RTRIM(uptime) + ' minutes (the last time SQL started)',
sqlserver_start_time
FROM
AB_Utility.dbo.AB_Uptime();
WITH calced AS
(
SELECT
[object_id],
index_id,
reads = user_seeks + user_scans + user_lookups,
writes = user_updates,
perc = CONVERT(DECIMAL(10,2), user_updates * 100.0 /
(user_seeks + user_scans + user_lookups + user_updates))
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[status] = CASE
WHEN reads = 0 AND writes = 0 THEN
'Consider dropping : not used at all'
WHEN reads = 0 AND writes > 0 THEN
'Consider dropping : only writes'
WHEN writes > reads THEN
'Consider dropping : more writes (' +
RTRIM(perc) + '% of activity)'
WHEN reads = writes THEN
'Reads and writes equal'
END,
[table] = AB_Utility.dbo.AB_GetTwoPartName(
c.[object_id], DB_ID()),
[index] = i.Name,
c.reads,
c.writes
FROM
calced AS c
INNER JOIN
sys.indexes AS i
ON
c.[object_id] = i.[object_id]
AND c.index_id = i.index_id
WHERE
c.writes >= c.reads;
END
GO
Note that because the read and write metrics are per operation, not per row, a DML operation that affects 100 rows will only count as one user update in this view.
Finding inefficient queries
The table-valued function in listing 8 will return the top n queries, ordered in descending order by longest average CPU time, longest average elapsed time, highest average reads, highest logical reads, highest writes, or highest number of executions. Because this one query does not rely on database-specific catalog views, it can be created in the utility database and called from anywhere (passing database name, number of rows, and ordering preference). You can also add a WHERE clause to restrict the result set to objects matching a certain naming pattern or queries that executed at least n times.
Listing 8. Finding inefficient queries
USE AB_Utility;
GO
IF OBJECT_ID(N'dbo.AB_GetInefficientQueries', N'IF') IS NOT NULL
DROP FUNCTION dbo.AB_GetInefficientQueries;
GO
CREATE FUNCTION dbo.AB_GetInefficientQueries
(
@database_name SYSNAME,
@number_of_rows INT,
@order_by VARCHAR(15)
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP (@number_of_rows) * FROM
(
SELECT
exec_object = AB_Utility.dbo.AB_GetTwoPartName(
est.objectid, est.[dbid]),
exec_statement = AB_Utility.dbo.AB_ParseSQLText(est.[text],
qs.statement_start_offset, qs.statement_end_offset ),
u.sqlserver_start_time,
uptime_minutes = u.uptime,
execution_count,
first_execution_time = qs.creation_time,
qs.last_execution_time,
avg_cpu_time_milliseconds
= qs.total_worker_time / (1000 * qs.execution_count),
avg_logical_reads
= qs.total_logical_reads / qs.execution_count,
avg_physical_reads
= qs.total_physical_reads / qs.execution_count,
avg_writes
= qs.total_logical_writes / qs.execution_count,
avg_elapsed_time_milliseconds
= qs.total_elapsed_time / (1000 * qs.execution_count)
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.[sql_handle]) AS est
CROSS JOIN
AB_Utility.dbo.AB_Uptime() AS u
WHERE
est.[dbid] = DB_ID(@database_name)
) x
ORDER BY CASE @order_by
WHEN 'cpu time' THEN avg_cpu_time_milliseconds
WHEN 'logical reads' THEN avg_logical_reads
WHEN 'physical reads' THEN avg_physical_reads
WHEN 'writes' THEN avg_writes
WHEN 'elapsed time' THEN avg_elapsed_time_milliseconds
WHEN 'executions' THEN execution_count
END DESC,
exec_object
);
GO
USE [tempdb];
GO
SELECT *
FROM AB_Utility.dbo.AB_GetInefficientQueries
(
'msdb',
50,
'cpu time'
)
-- WHERE exec_object NOT LIKE '%sp_get_composite_job_info%'
-- WHERE execution_count >= 50;
Finding missing indexes
Starting with SQL Server 2005, the database engine started keeping track of indexes that the optimizer would have taken advantage of, if they existed. The missing index DMVs should be used only as a guide, and not as the final authority on how you should change your index structures. (As with other DMVs, the data does not persist between restarts. Also, be careful about relying on data for tables with indexes that have changed recently, as this can also clear out missing index information.) The function in listing 9 will return a slightly more useful output structure to help you determine which tables and indexes you should further investigate for fine tuning. This includes information about how long SQL Server has been up, when the last user seek or scan was for that specific query (because it may represent an ad hoc query outside of your normal workload), and the CREATE INDEX DDL if you wanted to follow through with the suggestion. To use the function, you pass in the database name and the number of rows you want to return.
Listing 9. Finding missing indexes
USE AB_Utility;
GO
IF OBJECT_ID(N'dbo.AB_GetMissingIndexes', N'IF') IS NOT NULL
DROP FUNCTION dbo.AB_GetMissingIndexes
GO
CREATE FUNCTION dbo.AB_GetMissingIndexes
(
@database_name SYSNAME,
@number_of_rows INT
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP (@number_of_rows)
*,
-- must give credit to Tibor Karazsi here:
[statement] = 'CREATE INDEX [<<index name>>]'
+ ' ON ' + [table] + ' ('
+ COALESCE(eq + COALESCE(', ' + iq, ''), iq)
+ ')' + COALESCE(' INCLUDE(' + ic + '),', ';')
FROM
(
SELECT
[table] = AB_Utility.dbo.AB_GetTwoPartName(
d.[object_id], d.database_id),
eq = d.equality_columns,
iq = d.inequality_columns,
ic = d.included_columns,
relative_benefit = (s.user_seeks + s.user_scans)
* (s.avg_total_user_cost * s.avg_user_impact),
s.user_seeks,
s.user_scans,
s.last_user_seek,
s.last_user_scan
FROM
sys.dm_db_missing_index_details AS d
INNER JOIN
sys.dm_db_missing_index_groups AS g
ON d.index_handle = g.index_handle
INNER JOIN
sys.dm_db_missing_index_group_stats AS s
ON g.index_group_handle = s.group_handle
WHERE
d.database_id = DB_ID(@database_name)
) x
CROSS JOIN AB_Utility.dbo.AB_Uptime()
ORDER BY relative_benefit DESC
);
GO
SELECT *
FROM AB_Utility.dbo.AB_GetMissingIndexes
(
'Org00010001',
50
);
DMV categories in SQL Server
Table 1 lists the DMV categories in both SQL Server 2005 and SQL Server 2008. Table 2 lists the new DMV categories in SQL Server 2008.
Table 1. DMV categories in SQL Server 2005 and 2008
|
DMV category |
DMVs |
URL for more information |
|---|---|---|
|
Common Language Runtime (CLR) |
sys.dm_clr_* |
|
|
Database |
sys.dm_db_* |
|
|
Database mirroring |
sys.dm_db_mirroring_* |
|
|
Execution |
sys.dm_exec_* |
|
|
Full-text search |
sys.dm_fts_* |
|
|
Indexes |
sys.dm_db[_missing|_index_* |
|
|
Input/output (I/O) |
sys.dm_io_* |
|
|
Query notifications |
sys.dm_qn_* |
|
|
Replication |
sys.dm_repl_* |
|
|
Service broker |
sys.dm_broker_* |
|
|
SQL Server operating system |
sys.dm_os_* |
|
|
Transactions |
sys.dm_tran_* |
Table 2. New DMV categories in SQL Server 2008
|
URL |
||
|---|---|---|
|
Change data capture |
sys.dm_cdc_* |
|
|
Extended events |
sys.dm_xe_* |
|
|
Object (dependency) |
sys.dm_sql_* |
|
|
Resource governor |
sys.dm_resource_governor_* |
|
|
Security |
sys.dm_[audit|cryptographic|etc]_* |
Summary
Gone are the days of running DBCC commands and system stored procedures over and over again, and keeping links to Profiler and Performance Monitor on every machine’s desktop, when trying to peek into the usage characteristics of our SQL Server instances. I hope I have provided a glimpse of how much power we have been given through DMVs and DMFs, and that I have inspired you to use them more often when observing usage or troubleshooting performance issues.
About the author
Aaron Bertrand is the Senior Data Architect at One to One Interactive, a global marketing agency headquartered in Boston, Massachusetts. At One to One, Aaron is responsible for database design and application architecture. Due to his commitment to the community, shown through blogging at http://www.sqlblog.com, peer-to-peer support on forums and newsgroups, and speaking at user group meetings and code camps, he has been awarded as a Microsoft MVP since 1998. Aaron recently published a technical white paper for Microsoft, detailing how to use the new Resource Governor feature in SQL Server 2008.