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:
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:
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):
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:
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_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:
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:
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/