On the server level with SQL Server dynamic management views

The pantheon of dynamic management views (DMVs) extends beyond the database-related views covered in the first of two articles focusing on this SQL Server feature. Learn about DMVs that operate at the server level and provide information about a SQL Server instance and the system supporting it.

Dynamic management views are a SQL Server feature providing detailed database and system information. The first category of DMVs, as I discussed in the first article of this two-part series, “Gain insight with SQL Server dynamic management views,” is specific to one or more databases.  Another type of DMV works at the server level and returns information about a SQL Server instance and the system that supports it.

For example, the sys.dm_exec_cached_plans DMV returns details about cached or currently executing query plans. In the following SELECT statement, I retrieve details about those plans on my system that are greater than 400,000 bytes:

  bucketid AS BucketID,
  usecounts AS UseCounts,
  size_in_bytes AS Size
  size_in_bytes > 400000;

Notice that the statement retrieves the bucket ID, use count and size of each plan, as shown in the following results:

BucketID UseCounts Size
4577 12 1122304
5914 2 450560
9824 1 598016
2523 5 434176

Here I have only four plans over 400,000 bytes currently cached or executing on my system. (Again, I’ve had SQL Server running for only a short time.)

The next server-related DMV we’ll look at is sys.dm_exec_connections, which returns information about the connections established on my instance of SQL Server. In the following SELECT statement, I retrieve the session ID and connect time for each connection:

  session_id AS SessionID,
  connect_time AS StartTime,
  DATEDIFF(minute, connect_time, GETDATE()) AS MinConnected

In this statement, I also calculate the number of minutes that have passed since the connection was established. The following results show the information returned by the sys.dm_exec_connections DMV:

SessionID StartTime MinConnected
51 2011-01-23 08:44:52.890 136
52 2011-01-23 10:45:22.560 15
53 2011-01-23 10:55:21.577 5
56 2011-01-23 08:50:37.140 130

Now let’s look at the sys.dm_exec_procedure_stats DMV, which returns performance statistics on cached stored procedures. In the following example, I return stored procedure information on the ReportServer$SQLSRV2008 database (installed if you have SQL Server Reporting Services configured on your system):

USE ReportServer$SQLSRV2008;
  OBJECT_NAME(object_id) AS ObjectName,
  execution_count AS ExecCount,
  total_physical_reads AS PhysReads
  database_id = DB_ID('ReportServer$SQLSRV2008')
  AND execution_count > 10

In this case, I retrieve only those rows that have an execution count greater than 10. As the following results show, the statement returns the name of the stored procedure, the execution count and the total physical reads:

ObjectName ExecCount PhysReads
GetDBVersion 722 0
GetMyRunningJobs 48 0

In the next example, I use the sys.dm_os_threads to return information about the operating system threads that are running under the SQL Server process:

  thread_address AS ThreadAddress,
  creation_time AS CreatedTime,
  stack_bytes_committed BytesCommitted,
  stack_bytes_used AS BytesUsed
  stack_bytes_used > 10000;

The statement returns the thread address, creation time, number of bytes committed, and number of bytes used. The statement also limits the results to those rows whose used bytes exceed 10,000. The following results show that only two threads exceed the specified size:

ThreadAddress CreatedTime BytesCommitted BytesUsed
0x7FFDEE28 2011-01-23 08:44:22.000 516096 11280
0x7FFA2E28 2011-01-23 08:44:25.127 516096 24320

The last server-related DMV we’ll look at is sys.dm_os_sys_info. The view returns a wide range of information about the computer on which SQL Server is running. This information can be useful for determining what resources are available to SQL Server, as shown in the following example:

  physical_memory_in_bytes AS PhysMemory,
  virtual_memory_in_bytes AS VirtMemory,
  stack_size_in_bytes AS StackSize,
  max_workers_count AS WorkersCount

The following results show the information returned by this statement:

PhysMemory VirtMemory StackSize WorkersCount
536330240 2147352576 520192 256

As you can see, I’ve used the sys.dm_os_sys_info DMV to retrieve the amount of physical and virtual memory configured on the system, as well as the stack size and maximum worker count.

Working with SQL Server dynamic management views

Of course, there are many more DMVs than those I’ve shown in this two-part article. You can use this SQL Server feature to retrieve information about change data capture, I/O-related activities, Common Language Runtime, database mirroring and numerous other categories. Be sure to check out “Dynamic Management Views and Functions (Transact-SQL)” in SQL Server Books Online. The topic divides DMVs into specific categories. 

For each category, you can link to a list of those types of DMVs, and for each DMV in that category, you can link to the topic specific to that DMV. Whenever you reference a DMV in your statements, first review its topic to understand the type of information that the view returns and to see a description of the view’s columns. You’ll soon realize that what I’ve shown you here is only the tip of the iceberg.

Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation Find more information at http://rhsheldon.com/


Dig Deeper on Microsoft SQL Server 2008