New DMVs in Denali

  • Article

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