• Login
• Become a member
• RSS
• Part of the TechTarget network

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/

.

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

All Rights Reserved,Copyright 2005 - 2013, TechTarget