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_os_windows_info, dm_server_services

New DMVs in Denali