Manage Learn to apply best practices and optimize your operations.

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:

SELECT
  bucketid AS BucketID,
  usecounts AS UseCounts,
  size_in_bytes AS Size
FROM
  sys.dm_exec_cached_plans
WHERE
  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:

SELECT
  session_id AS SessionID,
  connect_time AS StartTime,
  DATEDIFF(minute, connect_time, GETDATE()) AS MinConnected
FROM
  sys.dm_exec_connections;

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;
GO
SELECT
  OBJECT_NAME(object_id) AS ObjectName,
  execution_count AS ExecCount,
  total_physical_reads AS PhysReads
FROM
  sys.dm_exec_procedure_stats
WHERE
  database_id = DB_ID('ReportServer$SQLSRV2008')
  AND execution_count > 10
ORDER BY
  ObjectName;


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:

SELECT
  thread_address AS ThreadAddress,
  creation_time AS CreatedTime,
  stack_bytes_committed BytesCommitted,
  stack_bytes_used AS BytesUsed
FROM
  sys.dm_os_threads
WHERE
  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:

SELECT
  physical_memory_in_bytes AS PhysMemory,
  virtual_memory_in_bytes AS VirtMemory,
  stack_size_in_bytes AS StackSize,
  max_workers_count AS WorkersCount
FROM
  sys.dm_os_sys_info;

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.

ABOUT THE AUTHOR
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/

.

This was last published in February 2011

Dig Deeper on Microsoft SQL Server 2008

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close