Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 46 of 361 Next >

Monitoring - SQL Server 2005 Dynamic Views and Functions

Last updated Mar 28, 2003.

SQL Server 2005 provides several ways to monitor your server for troubleshooting, processing load, performance tuning and more. In previous SQL Server versions, you had several Views, Functions and Stored Procedures that you could call to show you a great deal about what is going on in your server. SQL Server 2005 includes those same methods, but adds some interesting new ones that present even more information. In this tutorial I'll focus on a few SQL Server 2005 Dynamic Views and Functions that you can use to monitor your server, and display the text from a user's query.

Dynamic Management Views often rely on calls to a set of Dynamic Link Libraries that return information directly from the SQL Server engine. That's because they are designed to answer a different set of questions than static views or information recorded in the master database tables. Dynamic Management Views and Functions are designed to show information about the activity on the system. Since this information changes rapidly, it's more effective to ask the engine what is happening than for the engine to store that kind of data in tables to be queried.

Microsoft divides these Dynamic Management Views and Functions into multiple categories of things you can find out about:

Common Language Runtime
Database Mirroring
Database
Execution
Full-Text Search
I/O
Index
Query Notifications
Replication
Service Broker
SQL Operating System
Transactions

All of the Dynamic Management Views and Functions are owned by the sys schema. You can easily get a list of all of them by running the following query:

USE master
GO
SELECT name 
FROM sys.all_objects
WHERE name LIKE ’dm%’
ORDER BY name
GO

You can also review Books Online to find out more information about the Dynamic Management Views (DMV) and Functions. The category I use most often is the new SQL Operating System DM Views. The SQL OS layer manages SQL Server-specific operating system resources. I use these views to help me find bottlenecks or to explain what I'm seeing on the server from a system resource perspective.

The best view of system resources is available in the Windows System Monitor objects and counters. SQL Server 2005 provides a DMV with many of the SQL Server-related objects, instances and counters using the dm_os_performance_counters View. This DMV shows the object, instance and counter name, along with the value of the counter. It also shows a counter type, which is tied to the kind of output the counter shows. I use the following query to give me a quick snapshot of activity on my system:

SELECT object_name
, counter_name
, instance_name
, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name 
 IN(’Buffer cache hit ratio’
 , ’User Connections’
 , ’Transactions’
 , ’Average Wait Time (ms)’
 , ’Transactions/sec’
 , ’Errors/sec’
 , ’Target Server Memory (KB)’
 , ’Total Server Memory (KB)’
 )
ORDER BY object_name, counter_name
GO

I run this query every five minutes for a twenty-four hour period once a week on my servers, storing the results in a table. I then have a history of the activity on my system. There are many more objects and counters available in this DMV, depending on what you're looking for. Once I've located any issues that warrant further investigation, I query all of the counter_name items in that particular object_name, along with any that directly affect it.

Once I've located the area I'm interested in, I need to know which request is causing the issue. Another DMV, called dm_exec_sessions, shows the same kind of output you can find in SQL Server 2000's Enterprise Manager display for Activity. There's a lot of great information in here, including locks and blocking, but I'm normally after the information to show me who is using the most system resources. I want to eliminate the system activity generated by the server itself, so I set the value for the session_id to be higher than 51:

SELECT session_id
 , login_name
 , cpu_time
 , memory_usage
 , status
FROM
 sys.dm_exec_sessions
WHERE
 session_id >= 51
GO

From there, I have a good idea of the sessions that are causing the server's issues. I can now ask what the user is running, based on that connection identifier using a combination of the dm_exec_requests DMV, the dm_exec_sql_text() Dynamic Management Function, and the CROSS APPLY T-SQL enhancement:

SELECT T.text
FROM  sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
WHERE r.session_id = 57
GO

There's a lot going on here, so let's take a closer look. In the first line I've used an alias (the T part) to reference a column that exists in both the table and the function. If I don't give it a name, I get an error. I'll show you where the T comes from in a moment.

In the FROM line I first use the DMV called sys.dm_exec_requests that stores the information regarding what is running on my system. At the end of it I alias the table name with the letter R. From then on, whenever the system sees an "R." it will know I mean this table. The problem is that the text within the table isn't everything I'm looking for – I need to use a function to find that, which is on the next line. The sys.dm_exec_sql_text() function will show me the text I want, but it needs the sql_handle variable, which is stored in the sys.dm_exec_requests table. So I have one thing that depends on another – I need a join to put them together. But I can't join a table with a function – not without some help.

That's where the CROSS APPLY statement comes into play. The CROSS APPLY function allows me to reference the table by the function. I give this the T alias I mentioned earlier.

These DM Views are secured, since they might show information you'd rather not have available to everyone. You'll need to grant the VIEW SERVER STATE permission for server-level and VIEW DATABASE STATE permissions for database-level Dynamic Management Views and Functions. As always, it's usually best to create a Role with a descriptive name and grant the permissions to that.

Informit Articles and Sample Chapters

My friends over at Scalability Experts have written a great book on SQL Server 2005 that covers some of the other management functions. You can get it in our bookstore here.