Of late, the changes made to the Catalog Views & Dynamic Management Views (DMVs) in SQL Server 2012 have attracted my attention. One of the major factors in this attraction is the rate at which new DMVs and changes to existing DMVs in Microsoft SQL Server are being rolled out. DMVs are some of the the most powerful features of Microsoft SQL Server. The list of DVMs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

In the post today, I will be discussing the DMVs related to the SQL Server installation introduced new to SQL Server 2008 R2/2012:

• sys.dm_server_services
  
• sys.dm_server_registry
  
• sys.dm_server_memory_dumps

SQL Server 2008 R2/2012 Installation DMVs

sys.dm_server_services

I have written about this DMV in the past as well. The DMV that provides information about the SQL Server and the SQL Server Agent services in the current instance of SQL Server is sys.dm_server_services. Along with information about service status, service account, process_id and clustered information, this DMV also provides information about when the SQL Server and the SQL Server Agent services were last started up!

SELECT * FROM sys.dm_server_services;

On my test system, the output of this query is shown in the screen-shot below, and consists of:

1. Service Name
   
2. Startup Type (enumeration)
   
3. Startup Description
   
4. Status (enumeration)
   
5. Status description
   
6. Process ID
   
7. Last Startup Time
   
8. Service Account
   
9. Service File Name & startup parameter
   
10. Is instance clustered?
    
11. If clustered, the cluster node name is also present

sys.dm_server_registry

For a given SQL Server instance, this DMV returns one row for each configuration and installation related registry key. For example, the following query fetches the current version of the given SQL Server instance from the registry.

SELECT dsr.registry_key,

       dsr.value_name,

       dsr.value_data

FROM sys.dm_server_registry AS dsr

WHERE dsr.value_name = ‘CurrentVersion’;

The output consists of the following three columns:

• Registry Key
  
• Value Name
  
• Value data

sys.dm_server_memory_dumps

This DMV returns one row for each memory dump file generated by the SQL Server Database Engine. This can be useful in troubleshooting potential issues with the SQL Server installation and/or operation. No records returned by this DMV is the ideal scenario.

SELECT smd.filename,

       smd.creation_time,

       smd.size_in_bytes

FROM sys.dm_server_memory_dumps AS smd;

In my environment, no records were returned by the DMV because my SQL Server has not yet crashed. However, the following columns are available for the results to be displayed in:

• File Name
  
• File creation time
  
• File Size in Bytes

Permissions

All the three DMVs require that the user must have at least VIEW SERVER STATE permissions. This is because they not only expose the details about the SQL server, they also reach out into the operating system of the host to read the registry.

Further Reading

• sys.dm_server_services – http://msdn.microsoft.com/en-us/library/hh204542.aspx
  
• sys.dm_server_registry – http://msdn.microsoft.com/en-us/library/hh204561.aspx
  
• sys.dm_server_memory_dumps – http://msdn.microsoft.com/en-us/library/hh204543.aspx
  
• Using sys.dm_server_services to know when was the SQL Server instance restarted? – http://bit.ly/UilzuG

Until we meet next time,

Be courteous. Drive responsibly.