Introduction
Microsoft SQL Server 2008 R2 and SP1 and SQL Server 2012 has a new set of DMVs that includes sys.dm_server_memory_dumps, sys.dm_server_registry, sys.dm_server_services, sys.dm_os_windows_info, sys.dm_os_volume_stats and sys.dm_exec_query_stats. These DMVs can be used to return the information about SQL Server configuration and installation, memory dumps related information and information that can be useful to diagnose problems and tune query performance.
In this article, I’ll cover the purpose and use of these new DMVs of SQL Server 2008 R2 SP1 and SQL Server 2012 (DENALI):
1) sys.dm_server_memory_dumps – It contains the infomation about memory dumps files generated by the SQL Server Database Engine. It returns 3 columns listed as below:
| Column Name | Data type | Description | Attribute |
| filename | nvarchar(256) | Memory dump file name and path. | NOT NULL |
| creation_time | datetimeoffset(7) | Date and time the file was created. | NOT NULL |
| size_in_bytes | bigint | Size of the memory dump file in bytes. | NULL |
Usage:
USE [master] GO SELECT * FROM sys.dm_server_memory_dumps
Example:
Currently there are no memory dump files are available but if you see any on your SQL Server then alert your SQL Server DBA, because memory dumps can be serious and could lead to data corrouption and other problems.
Permissions:
Requires VIEW SERVER STATE permission on the server.
MSDN reference: http://technet.microsoft.com/en-us/library/hh204543.aspx
2) sys.dm_server_registry – This DMV contains the configuration and installation information for the current instance of SQL Server that is stored in Windows registry. This DMV returns one row per registry key. It returns 3 columns listed as below:
| Column Name | Data type | Description | Attribute |
| registry_key | nvarchar(256) | Stores name of registry key. | NULL |
| value_name | nvarchar(256) | Stores the name of key value. In Windows Registry Editor this item is shown under Name column. | NULL |
| value_data | sql_variant | Stores value of the key data. In Windows Registry Editor this item is shown under Data column. | NULL |
In old version of SQL Server you can access the Windows registry information via xp..regread. This DMV is use full if you want to audit the configuration of your SQL Server.
Usage:
USE [master] GO SELECT * FROM sys.dm_server_registry
Example:
Permissions:
Requires VIEW SERVER STATE permission on the server.
MSDN reference: http://msdn.microsoft.com/en-us/library/hh204561.aspx
3) sys.dm_server_services – The initial version of this DMV that was released in SQL Server 2008 R2 SP1 only returns information about SQL Server and SQL Agent service. In SQL Server 2012 it also returns information about Full-text service.
This DMV returns one row per service. It returns 11 columns listed as below:
| Column Name | Data type | Description | Attribute | ||||||||||||||||
| servicename | nvarchar(256) | Stores name of the SQL Server, Full-text or SQL Server Agent service. | NOT NULL | ||||||||||||||||
| startup_type | int | Indicates start mode of the SQL Server, Full-text or SQL Server Agent service.
| NULL | ||||||||||||||||
| startup_desc | nvarchar(256) | Describe the start mode of the SQL Server, Full-text or SQL Server Agent service.
| NOT NULL | ||||||||||||||||
| status | int | Indicates the existing status of the SQL Server, Full-text and SQL Agent service.
| NULL | ||||||||||||||||
| status_desc | nvarchar(256) | Describe the existing status of the SQL Server, Full-text and SQL Agent service.
| NOT NULL | ||||||||||||||||
| process_id | int | Stores the process id of the SQL Server, Full-text and SQL Agent service. | NOT NULL | ||||||||||||||||
| last_startup_time | datetimeoffset(7) | Stores the date and time when the SQL Server, Full-text and SQL Agent service was last started. | NULL | ||||||||||||||||
| service_account | nvarchar(256) | Stores the account name of authorized account to control the service. | NOT NULL | ||||||||||||||||
| filename | nvarchar(256) | Stores the full OS path of service executable. | NOT NULL | ||||||||||||||||
| is_clustered | nvarchar(1) | Indicates whether the service is installed as a resource of the cluster server. | NOT NULL | ||||||||||||||||
| cluster_nodename | nvarchar(256) | Stores the names of the cluster nodes. | NULL |
In old versions of SQL Server you can access the same information by accessing the Windows registry information for the service via xp..regread. This DMV is useful if you want to audit the what services are installed as part of the SQL installation.
Usage:
USE [master] GO SELECT * FROM sys.dm_server_services
Example:
Permissions:
Requires VIEW SERVER STATE permission on the server.
MSDN reference: http://msdn.microsoft.com/en-us/library/hh204542.aspx
4) sys.dm_os_windows_info – The DMV returns detailed information about Windows OS version for example the release number, service pack level and language version of SQL Server Windows OS. This DMV returns only one row with four columns listed as below:
| Column Name | Data type | Description | Attribute |
| windows_release | nvarchar(256) | Stores the Windows OS release number. | NOT NULL |
| windows_service_pack_level | nvarchar(256) | Store the service pack level of Windows operating system. | NOT NULL |
| Windows_sku | int | Stores ID of Windows Socket Keeping Unit (SKU) | NULL |
| os_language_version | int | Stores Windows locale identifier (LCID) of the operating system. | NOT NULL |
In old versions of SQL Server you can access the same information by accessing the Windows registry information for the OS via xp..regread.
Usage:
USE [master] GO SELECT * FROM sys.dm_os_windows_info
Example:
Permissions:
Requires VIEW SERVER STATE permission on the server.
MSDN reference: http://msdn.microsoft.com/en-us/library/hh204565.aspx
5) sys.dm_os_volume_stats – The DMV returns Windows OS volume information on which the database files resides. It takes two parameters i.e. database_id (ID of the database) and file_id (ID of the database file). This DMV returns 13 columns listed as below:
| Column Name | Data type | Description | Attribute |
| database_id | int | ID of the database. | NOT NULL |
| file_id | int | ID of the file. | NOT NULL |
| volume_mount_point | nvarchar(512) | Mount point at which the volume is rooted. Can return an empty string. | NOT NULL |
| volume_id | nvarchar(512) | Operating system volume ID. Can return an empty string | NOT NULL |
| logical_volume_name | nvarchar(512) | Logical volume name. Can return an empty string | NOT NULL |
| file_system_type | nvarchar(512) | Type of file system volume (for example, NTFS, FAT, RAW). Can return an empty string | NOT NULL |
| total_bytes | bigint | Total size of the volume in bytes. | NOT NULL |
| available_bytes | bigint | Available free space on the volume in bytes. | NOT NULL |
| supports_compression | bit | Indicates if the volume supports operating system compression. | NOT NULL |
| supports_alternate_streams | bit | Indicates if the volume supports alternate streams. | NOT NULL |
| supports_sparse_files | bit | Indicates if the volume supports sparse files. | NOT NULL |
| is_read_only | bit | Indicates if the volume is currently marked as read only. | NOT NULL |
| is_compressed | bit | Indicates if this volume is currently compressed. | NOT NULL |
In old versions of SQL Server you can access the same information by writing your own CLR procedure by using one of the .NET framework languages.
Usage:
USE [master] GO SELECT * FROM sys.dm_os_volume_stats (database_id, file_id)
Example:
Permissions:
Requires VIEW SERVER STATE permission on the server.
MSDN reference: http://msdn.microsoft.com/en-us/library/hh223223.aspx
6) sys.dm_exec_query_stats – This existing DMV has new columns added that return very useful information. These new columns are listed as below:
total_rows– Returns the total number of rows returned by the querylast_rows– Returns the total number of rows returned by last execution of the query.min_rows– Returns the minimum number of rows query returned over the number of times that the plan has executed since it was last compiled.max_rows– Returns the maximum number of rows query returned over the number of times that the plan has executed since it was last compiled.
Usage:
USE [master] GO SELECT * FROM sys.dm_exec_query_stats
Example:
Permissions:
Requires VIEW SERVER STATE permission on the server.
MSDN reference: http://msdn.microsoft.com/en-us/library/ms189741.aspx
Conclusion
In this article, I’ve covered the new useful DMVs of SQL Server 2008 R2 SP1 and SQL Server 2012 (DENALI). These DMVs help us to manage our SQL Servers efficiently by returning some very useful information. It is difficult to retrieve similar kind of information in older versions of SQL Servers and one need to write lengthy bespoke code to retrieve the similar information returned by these DMVs. I’ve already written some SSRS queries by using these DMVs which details the useful information which our companies auditors needs to audit our our SQL Servers.
