Microsoft SQL Server: Server-related dynamic management views in SQL Server 2008 SP1 , “2012" and Above

There are three new server-related DMVs introduced in SQL Server 2008 R2 Service Pack 1 and SQL Server "2012".

1. sys.dm_server_services:

- For SQL Server 2008 R2 SP1, it contains information about SQL Server and SQL Server Agent services for the current instance.

- For "Denali", it returns information about SQL Server, Full-Text Search and SQL Server Agent services for the current instance. Let’s use below query on both:

SELECT servicename          AS [Name],

       startup_type_desc    AS [Type],

       status_desc          AS [Status],

       process_id           AS [ProcessID],

       service_account      AS [Startup Account]

FROM   sys.dm_server_services

Result Set for SQL Server 2008 R2 SP1:

Name                           Type       Status         ProcessID   Startup Account

————————–     ——-    ——         ———   —————

SQL Server (MSSQLSERVER)       Automatic  Running        2492        .\sqladmin

SQL Server Agent (MSSQLSERVER) Manual     Stopped        NULL        .\sqladmin

(2 row(s) affected)

Result Set for "Denali":

Name                       Type    Status   ProcessID  Startup Account

————————– ——  ——   ———  —————

SQL Server (DENALI)        Manual  Running  5176       .\sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .\sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .\sqladmin

(3 row(s) affected)

Ah!, for "Denali" it’s returning two entries for SQL Server Agent, and not returning the Full-Text Search service, (it is installed), a possible "Denali" bug (Anyone else got the same issue?).

2. sys.dm_server_registry:

- For both SQL Server 2008 R2 SP1 and "Denali" – this view returns configuration and installation information, such as host machine or network configuration for the current instance, i.e. information we used to fetch using xp_instance_regred for an instance, Let’s execute below query on both:

SELECT registry_key, value_name, value_data

FROM   sys.dm_server_registry

WHERE  value_name = N'CurrentVersion'

Result Set for SQL Server 2008 R2 SP1:

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLM\Software\…\MSSQLServer\CurrentVersion   CurrentVersion       10.50.2500.0

(1 row(s) affected)

Result Set for "2012":

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLM\Software\…\MSSQLServer\CurrentVersion    CurrentVersion       11.0.1440.19

(1 row(s) affected)

3. sys.dm_server_memory_dumps:

This view returns a list of memory dump files generated by the SQL Server Database Engine. It returns below information:

i) filename – File name and physical path of the memory dump file,

ii) creation_time – date and time the memory dump file creation,

iii) size_in_bytes – memory dump file size in bytes.