For a few months, I have been testing the new  SQL Server 2012 (codename Denali) and I was very surprised from the DMVs of this version. Dynamic management views (DMVs) return server state information that can be used to monitor the health of a server instance, diagnose problems and tune performance. I will focus on some DMVs which are very interesting for a DBA.

My article is based on a msdn web site reference. At the end of each chapter, you will find a msdn reference for more details on the DMV.

sys.dm_exec_query_stats

dm_exec_query_stats is not a new view but this view adds a few columns very important :

  • total_rows : Total number of rows returned by the query
  • last_rows : Number of rows returned by the last execution of the query
  • min_rows : Minimum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled.
  • max_rows : Maximum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled.

Example:

01-dm_exec_query_stats

Msdn reference: http://msdn.microsoft.com/en-us/library/ms189741(v=SQL.110).aspx

sys.dm_os_volume_stats

dm_os_volume_stats is a view for the operating system’s volume information.
This view exists on the version 2008 R2 but was not well known.

Two arguments are necessary:

  • ID of the database
  • ID of the file

To find the 2 arguments, you can use:

SELECT * FROM sys.master_files;

Example:

02-dm_os_volume_stats

Msdn reference : http://msdn.microsoft.com/en-us/library/hh223223.aspx

sys.dm_os_windows_info

This view returns all information on Windows operation system like release, service pack, and language version. Like dm_os_volume_stats, this view exist on SQL Server 2008 R2.

Example:

03-dm_os_windows_info
Msdn reference: http://msdn.microsoft.com/en-us/library/hh204565.aspx

sys.dm_server_services

This view returns information about your SQL Server services in the current instance. With this DMV you will just get information on the SQL Server Engine, the SQL Server Agent, and the SQL full-text daemon. I hope that we will see information  on SSAS, SSIS, and SSRS in the future.
Like the 2 precedent views, it is available on SQL Server 2008 R2.

Example:
04-dm_server_services

Msdn reference: http://msdn.microsoft.com/en-us/library/hh204565.aspx

sys.dm_server_registry

This view return information on the registry configuration of SQL Server’s current instance.
You will get all information on registry keys. For me, it’s a good feature for audit the configuration server. It’s running on SQL Server 2008 R2.

Example:
05-dm_server_registry

Msdn reference: http://msdn.microsoft.com/en-us/library/hh204561.aspx

sys.dm_server_memory_dumps

This view returns all memory dump file that are  generated. I have not dumped files on my example but you can see whitch colunms are returned.
It is running on SQL Server 2008 R2.

Example:
06-dm_server_memory_dumps
Msdn reference: http://technet.microsoft.com/en-us/library/hh204543.aspx

Conclusion

The majority of these new views announced for SQL Server 2012 are already part of  SQL Server 2008 R2.
But it is good to know that these features exist. I found them quite interesting and have included them in my SQL Server audit tool.:-D


Thumbnail [60x60]
by
Stéphane Haby