SQL Server 2008's Dynamic Duo: Management Views and Functions

Global Knowledge | Knowledge Centre | White Papers | Microsoft | SQL Server 2008's Dynamic Duo:

SQL Server 2008's Dynamic Duo: Management Views and Functions

Author: Jeff Peters

Abstract

The dynamic management views and functions, which were introduced in SQL 2005, have increased in number and provide improved function in SQL 2008. Taking advantage of these new objects gives you quick and easy access to in-depth information on nearly every aspect of SQL's internal workings. Their uses include, at a minimum, getting performance data, index usage statistics, and object dependency information. This white paper will explore a few of their capabilities and give examples to show how they can ease the workload of a database administrator. They are well worth the investment in time and effort.

Where Are They?

Take a look at the full list of these objects. They all reside in the sys schema, and the following query will identify them for us. Open a new query window in Management Studio, and type this in (using the AdventureWorks sample database):

USE AdventureWorks
SELECT * FROM sys.all_objects
WHERE [Type] IN ('V', 'TF', 'IF')
AND [Name] LIKE '%dm!_%' ESCAPE '!'
ORDER BY [Name]

This list of dynamic management objects (DMOs) (Figure 1) gives you some ideas as to what they can show just by looking at their names. They provide plenty of invaluable information that, while mostly available in SQL versions prior to 2005, would have taken comparably enormous amounts of effort and script writing to get.

DMOs can be organized most easily by their scope, which determines whether they are meant to provide data at the level of the server or a particular database. Most DMOs can also be categorized by their area of usefulness with a few examples being: Database Mirroring, Indexes, and the SQL Operating System. DMOs made their debut in SQL 2005, but many new ones are found only in SQL 2008.

Troubleshooting with DMOs

One of the primary uses of DMOs is to gain immediate access to performance data. This priceless information then can be used to troubleshoot everything from problematic processes to limited free space in the tempdb database. Take a look at those two specific examples to see how sys.dm_os_workers and sys.dm_db_file_ space_usage can help.

sys.dm_os_workers

This DMO monitors worker processes and tells us if those processes are experiencing any problems. Worker processes handle requests to execute some action on the data. If they are delayed, stuck, or fatally disrupted, you can easily have problems such as CPU red-lining occur. So how do we check on this? Type this in:

SELECT is_sick,
is_fatal_exception,
is_in_cc_exception
FROM sys.dm_os_workers

A value of 1 in the is_sick or is_fatal_exception columns will give an obvious clue as to that particular worker's status. The is_in_cc_exception column is helpful in that a value of 1 will identify non-SQL exceptions; this often points to a CLR process gone awry. Other available columns contain information about the amount of time the process has been running and/or waiting, the severity of the last exception it encountered, and I/Os used by or pending for the process.

sys.dm_db_file_space_usage

Tempdb is one of the usual suspects when looking for bottlenecks. Keeping track of the free space within it helps to avoid obvious performance problems. Tempdb can fill quickly with sorting operations, cursors, hash joins, temporary tables both local and global, and more. The sys.dm_db_space_usage view will reveal exactly how much space is being used and in what way.

Type this in:

SELECT SUM(unallocated_extent_page_count),
(SUM(unallocated_extent_page_count)/128)
AS [MB of Free Space]
FROM sys.dm_db_file_space_usage

Index Maintenance

Creating indexes is one of the easiest ways to increase performance in joins, sorts, and just about every other operation performed on a table. Unnecessary indexes, however, are often overlooked, taking up hard drive space and requiring processing power for maintenance. Poorly maintained indexes will have reduced performance due to fragmentation requiring reorganization or rebuilding to return them to their original efficiency. DMOs can help with both of these problems.

sys.dm_db_index_usage_stats

This view can return information about how many times a particular index has been used, when it was last accessed, and even the specific way in which the index has been used, such as lookups, seeks, or scans. It does not generate the information, but rather accesses data that SQL caches. Be aware that this cache resets during any reboots or database closures, so this is not a fully cumulative total that will always survive from viewing to viewing.

Here is the SQL statement:

SELECT object_id, index_id, user_seeks,
User_scans, user_lookups,
last_user_seek, last_user_scan, last_user_lookup
FROM sys.dm_db_index_usage_stats

The only confusion that may arise from using this view is that the object_id and index_id columns give only identifiers instead of naming the actual tables.

This confusion can be cleared up by joining the view to the sys.objects and sys.indexes system views to resolve things such as object names and index types.