#0234 – SQL Server – Installation DMVs – dm_server_*


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

image


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

image


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



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

3 thoughts on “#0234 – SQL Server – Installation DMVs – dm_server_*

  1. Patrick Lambin

    It is always a pleasure for me to congratulate somebody who is writing a clear and useful post on a topic i am fond of
    I hope that your next post will be as good as this one

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s