DMV: sys.dm_server_services

Microsoft SQL Server 2008 R2 SP1 and SQL Server 2012 has a new set of DMVs. One of them is “sys.dm_server_services” which  return a whole host of important information relating to the SQL Server services that includs;

• Service Name
• Startup Type
• Startup_Type_Desc
• Status
• Status_Desc
• Process_id
• Last Startup Time
• Service Account,
• File Name
• Is_Clustered
• Cluster NodeName

Open up a new query window, and run the following select statement.

SELECT 
	process_id, servicename, status_desc, startup_type_desc,
	last_startup_time, service_account, is_clustered, 
	cluster_nodename, [filename]
FROM sys.dm_server_services WITH (NOLOCK);

When you run the above query, it is going to show below information.

I hope you enjoyed the DMV. It is a very useful DMV to have a quick view of the SQL Server services.

Reference: http://msdn.microsoft.com/en-us/library/hh204542.aspx