Manage Learn to apply best practices and optimize your operations.

Gaining insight with SQL Server dynamic management views, Part 2

Dynamic management views, a feature in SQL Server 2005 and newer, let users say goodbye to complex queries for retrieving database information. In the first of this two-part series, have a look at a sampling of SQL Server DMVs and the metadata they call up.

Dynamic management views (DMVs) are a SQL Server feature providing detailed database and system information, as...

I discussed in the first of this two-part article, and I focused on DMVs specific to one or more databases. Another type of DMV works at the server level and provides 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.

 

This was last published in February 2011

Dig Deeper on SQL Server Migration Strategies and Planning

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