SQL Server 2005 introduced Dynamic Management Views(DMVs). Technically, there are both views & functions–DMVs & DMFs, but colloquially they’re often simply referred to as just “DMVs”. DMVs simply return information about the state of the server or database and can be used for monitoring and troubleshooting of server/database health & performance.
New DMVs are added all the time. Today, let’s take a look at one of those useful DMVs.
sys.dm_server_services
Way back in SQL Server 2008R2 SP1 (Released 2011-07-11), Microsoft introduced the sys.dm_server_services DMV. This is not only an under-appreciated DMV, but one that isn’t all that well-known. Access to this DMV is covered by the VIEW SERVER STATE permission, and Microsoft Docs describe this DMV as:
Returns information about the SQL Server, Full-Text, SQL Server Launchpad service (SQL Server 2017+), and SQL Server Agent services in the current instance of SQL Server. Use this dynamic management view to report status information about these services.
That’s not really the best elevator pitch for the DMV–lets look at the actual data that the DMV includes so that we can better understand it:
SELECT ServiceName = servicename,
StartupType = startup_type_desc,
ServiceStatus = status_desc,
StartupTime = last_startup_time,
ServiceAccount = service_account,
IsIFIEnabled = instant_file_initialization_enabled
FROM sys.dm_server_services;
Check out that column list! Not only is the service account listed there, but also the startup time, and whether Instant File Initialization is enabled.
ServiceName StartupType ServiceStatus StartupTime ServiceAccount IsIFIEnabled ------------------------------- ----------- ------------- ---------------------------------- -------------------------- ------------ SQL Server (MSSQLSERVER) Automatic Running 2020-04-04 10:15:34.3603420 -04:00 NT Service\MSSQLSERVER Y SQL Server Agent (MSSQLSERVER) Manual Stopped NULL NT Service\SQLSERVERAGENT N
This DMV was about five years old before I knew it existed. That means I was doing all sorts of unnecessary gymnastics to check the service account, or to check when the instance was last started (SELECT create_date FROM sys.databases WHERE name = 'tempdb';). In the years since I learned about sys.dm_server_services, it’s become one of my favorite DMVs.
Is it weird that I have “favorite DMVs”?