New DMV’s in SQL Server 2011 aka Denali

Denali CTP3 is already released and we have new DMV’s accompanied with this version which will be very useful for us. I’m going to cover the DMV that are all generic and not to a specific feature of Denali, I’ll will cover those DMV’s while writing article on that feature. There are around 8 DMV’s which I’m going to discuss it now.Below are the list of DMV’s that I’m talking about

  • sys.dm_os_cluster_properties
  • sys.dm_os_server_diagnostics_log_configurations
  • sys.dm_os_windows_info*
  • sys.dm_server_memory_dumps*
  • sys.dm_server_registry*
  • sys.dm_server_services*
  • sys.dm_tcp_listener_states
  • sys.dm_db_log_space_usage

*Available in SQL Server 2008 R2 SP1 also

Let’s discuss all these in detail

sys.dm_os_cluster_properties

This DMV will help you in obtaining cluster properties of that instance, it will be returning only one row. If you run a select on this DMV on an standalone box no rows are returned. For more information on the values that are returned by this DMV can be found at http://technet.microsoft.com/en-us/library/gg471591%28SQL.110%29.aspx. Based on this analysis you can set the values for cluster.

sys.dm_os_server_diagnostics_log_configurations

This DMV will give you cluster log file related information. It will return one row with current configuration for the SQL Server failover cluster diagnostic log.
Where  is_enabled – states whether logging is turned on or not. Values will be 1 or 0
path – Path where the log files should be stored
max_size – Maximum size of the log file in MB
max_files – Maximum number of files that can be stored in the path before recycling.

sys.dm_os_windows_info

I don’t need to provide more description on this DMV as the name itself will give you an clear idea. Yes this DMV will give you information above the operation system, it’s service pack level, default language and it’s SKU ID value.

sys.dm_server_memory_dumps

This DMV gives you information about memory dumps generated in the server. It will give you the path, size and creation time of the memory dump so that it will be easy for the DBA to get the memory dump.

sys.dm_server_registry

Are you tired using xp_reg_read extended procedure? If yes then this wonderful DMV will replace it with all necessary registry information related to that instance. It’s really very easy to obtain all those registry information related to that instance with just select on this DMV. You can get almost all the info from registry.

sys.dm_server_services

This piece of DMV is helpful to DBA’s. I really heard from my DBA’s that they are not able to get SQL Server agent status properly using any T-SQL when they connected to SQL. Now it’s time, MS has developed this DMV. You can get excellent information about SQL Server services, very easy to obtain the status and property of the services.

sys.dm_tcp_listener_states

Not sure on which port does SQL Server listens? Do you require the port information from SQL? Now it’s very easy to get the TCP port details with this DMV. It will give you the details about all the ports that SQL Server is listening too.

sys.dm_db_log_space_usage

Name of the DMV itself is self descriptive! Yes this DMV will give you information related to the log file usage. You will be getting similar information when you retrieve the data using DBCC SQLPERF(‘logspace’). However this DBCC command will give you output for all the databases where as this DMV will give you output for the specific database as shown below


There are lot more DMV’s are there in Denali which is specific to a new feature added to denali, we will discuss those DMV’s in future articles. Hope these DMV’s will ease your work now!!

One thought on “New DMV’s in SQL Server 2011 aka Denali”

  1. Pingback: DotNetShoutout

Leave a Reply

Your email address will not be published. Required fields are marked *

*