More on DMV changes in SQL 2008 R2 SP1

SQLOS Team — Thu, 12 May 2011 00:46:00 GMT

Following our last post by Mehmet about new support and troubleshooting DMV’s, here are additional details on supportability DMV changes in SQL 2008 R2 SP1 and how you might use them. These changes are also available in “Denali”, the upcoming major release of SQL Server. With SQL 2008 R2 SP1 coming up soon, please give them a try and let us know what you think.

We’ll take a peek at these DMV changes as well as talk about in what circumstances these DMVs would come in handy. Note that all DMVs/DMF documented below require VIEW SERVER STATE permission.

1. Extended sys.dm_exec_query_stats with 4 new columns (total/last/min/max_row)

sys.dm_exec_query_stats is a very widely used DMV that provides useful information in analyzing query performance. To troubleshoot long running queries, it is also helpful to have total/min/max/last row counts information in order to separate queries that are simply returning a large number of rows from those problematic ones due to, say, a missing index or a bad query plan.

2. sys.dm_os_volume_stats(f.database_id, f.file_id)

This is a new DMF (Dynamic Management Function) that helps check the free space on the partitions the SQL server instance resides on. The catalog view sys.database_files provides stats such as size per database file, however, without information about free space on the partition, the information is less actionable – a database file cannot autogrow even when the size is small if there is not sufficient space left on the partition.

For example, you can use the following T-SQL statements to get the stats for the current database.

select database_id, f.file_id, total_bytes, available_bytes from sys.database_files as f cross apply sys.dm_os_volume_stats(DB_ID(), f.file_id)

DMF definition:

Column	Type	Description
database_id	int, not null	ID of the database
file_id	int, not null	ID of the file
volume_mount_point	nvarchar(512)	Mount point at which the volume is rooted
volume_id	nvarchar(512)	OS volume ID
logical_volume_name	nvarchar(512)	Logical volume name
file_system_type	nvarchar(512)	Type of file system volume (e.g., NTFS, FAT, RAW)
total_bytes	bigint	Total size in bytes of the volume
available_bytes	bigint	Available free space on the volume
supports_compression	bit	Does this volume support OS compression?
supports_alternate_streams	bit	Does this volume support alternate streams
supports_sparse_files	bit	Does this volume support sparse files?
is_read_only	bit	Is this volume currently marked read_only?
is_compressed	bit	Is this volume currently compressed?

3. sys.dm_os_windows_info

This new DMV provides information on the OS the SQL Server instance is running on, specifically including the following:

Windows Release
Windows Service Pack Level
Windows SKU
OS language version

DMV definition:

Column name	Column type
windows_release	nvarchar (256)
windows_service_pack_level	nvarchar(256)
windows_sku	int
os_language_version	int

Without this DMV, it is very difficult to get such information without calling into Windows APIs.

It’s also worth pointing out that a related change we made to existing DMV sys.dm_os_sys_info is to add two new columns (virtual_machine_type, virtual_machine_type_desc) to provide information when the SQL Server instance is running in a virtual machine environment.

4. sys.dm_server_registry

This DMV provides registry key information related to the overall configuration/installation of the SQL Server instance.

DMV definition:

Column name	Column type
registry_key	nvarchar (256)
value_name	Nvarchar (256)
value_data	sql_variant

The following groups of registry keys are covered in this DMV:

1) To help find out what SQL Server services are available on the host machine

HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\ObjectName
HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath
HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\Start

2) To help find out what SQL Agent services are available on the host machine

HKLM\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT\ObjectName
HKLM\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT\ImagePath
HKLM\SYSTEM\CurrentControlSet\Services\ SQLSERVERAGENT\Start
HKLM\SYSTEM\CurrentControlSet\Services\ SQLSERVERAGENT\DependOnService

3) To find out the current version of SQL Engine

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ CurrentVersion

4) To help with instance detectability, etc. the following registry keys are helpful

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ Parameters

5) To help troubleshoot connectivity issues (intermittent connections, high latency, etc.) that may be a result of misconfiguration

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ AdminConnection\TCP
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Np
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Sm
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ TCP\ IP1… IPAll
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Via

6) To help troubleshoot application issues due to incorrect settings in SQLServerAgent

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\ErrorLoggingLevel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\JobHistoryMaxRows
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\JobHistoryMaxRowsPerJob
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\WorkingDirectory

5. sys.dm_server_services

This DMV provides information about three services related to SQL Server, namely SQL Server, SQL Agent and Fulltext (only available as of Denali). The DMV includes the following information about the current SQL instance:

Column name	Column type
service	nvarchar (256)
startup_type	int
startup_type_desc	nvarchar(256)
status	int
status_desc	nvarchar(256)
process_id	nvarchar (256)
last_startup_time	datetimeoffset
service_account	nvarchar (256)
filename	nvarchar (256)
is_clustered	nchar
cluster_nodename	nvarchar (256)

6. sys.dm_server_memory_dumps

This DMV provides information on memory dumps that have been generated as a result of a recent crash, for instance. Dump type may be minidump, all-thread dump or full dump.

DMV definition:

Column name	Column type
filename	nvarchar (256)
size_in_bytes	bigint
creation_time	datetimeoffset

Thanks for reading this far. We’d love to hear your feedback once you get a chance to try them out.

-Xin Jin

Originally posted at http://blogs.msdn.com/b/sqlosteam/

New DMVs in Denali

SQLOS Team — Thu, 05 May 2011 18:41:00 GMT

This article is about some of the new DMV’s that are newly added to next version of SQL Server. These are also available SQL Server 2008 R2 SP1. They are very helpful to get information about the host operating system that the SQL Server runs on, status of the server and even the memory dumps that are created by the server and registry keys that the server uses.

To read all mentioned DMV’s the user must have VIEW SERVER STATE permission.

sys.dm_os_windows_info: This DMV returns the OS related information to the user. It is good to know the properties of hosting server.

Columns:

windows_release:
windows_service_pack
windows_sku
os_language_version

sys.dm_server_registry: This DMV returns some of the registry keys that the server uses.

Columns:

registry key
value_name
value_data

sys.dm_server_services: This DMV provides useful information about the SQL Server, SQL Agent and SQL FullText service related with the running server. This DMV is very helpful to track the servers status and startup configuration.

Columns:

service_name
startup type
startup_type_desc
status
status_type_desc
process_id
last_startup_time
service_account
filename
is_clustered
cluster_nodename

sys.dm_server_memory_dumps: This DMV returns the dumps that are generated by the server.

Columns:

filename:
size_in_bytes
creation_time

If you need more detailed information on any of those, please reply to this post or send email

Originally posted at http://blogs.msdn.com/b/sqlosteam/

SQLOS Team : dm_server_registry, dm_server_services, dm_server_memory_dumps

More on DMV changes in SQL 2008 R2 SP1

New DMVs in Denali