SQL SERVER – Denali – Three DMVs – sys.dm_server_memory_dumps – sys.dm_server_services – sys.dm_server_registry

I just read a very interesting post by Pinal Dave on 3 new DMVs in Denali.


In his post Pinal is asking the audience to help decipher the purpose of the DMVs. So here is my answer:

There is almost nothing new in this case, except for the fact that the information is available through DMVs.

In the case of Memory Dumps (sys.dm_server_memory_dumps) – these are available in the SQL Server log and in the rotated log files in the SQL Server LOG directory. The use is very simple – monitor this and make sure you alert an administrator, because memory dumps can be serious alerts about corruption and other problems.

In the case of the Services (dm_server_services) this information is available through the Services.msc console and through many other scripting methods. The use is great because the administrator may know if a service is running, under which account and if it will start the next time the server is restarted.

In the case of the Registry (dm_server_registry) – the data is available through several XPs (extended stored procedures), for example EXECUTE master..xp_regread. There are also various scripting methods to access and alter the registry information.


Here is where it all goes downhill, though:

I can see a few problems with these DMVs:

  • the non-transitional nature of the DMVs makes them quite useless – if the data in the DMVs is still ‘disappearing’ after SQL Server instance restart, then it is quite useless to look there for memory dumps and service statistics. After a server restart, it is still better to look in the logs, which are nothing but simple text files on the disk system
  • the worst part of the DMVs is that if the SQL Server Service is not running, then the user is not able to run DMVs. I.e. if you are debugging the Services starts and stops and the SQL Server Service is not running, then you are still going to check the logs and the OS settings, or even the Default Trace might help.
  •  The registry DMV is dangerous – I don’t know what percentage of DBAs are qualified enough to dig and change the registry and how many even would think to back it up and alert the system administrator about it. I am not sure if the dm_server_registry DMV can help the DBA to make changes, but I really hope that it does not. And if it does – the it will definitely take part of my SQL PASS Nordic session on ‘Database hidden disasters and planning against them’
Work on itWell, now...OKGoodGood job! (4 votes, average: 4.25 out of 5)

If you gave a rating less than the maximum, you must leave a comment with suggestions on how to improve the post. :)

Loading ... Loading ...


2 comments to SQL SERVER – Denali – Three DMVs – sys.dm_server_memory_dumps – sys.dm_server_services – sys.dm_server_registry