Share via

Service status watcher in SQL Server Management Studio – How it works

  • Article

Have you ever wondered about the mechanism using which SQL Server Management Studio(SSMS) - Object Explorer shows the service status for SQL Server and SQL Agent service? We recently worked with a customer on a issue related to this and thought that this might be useful information to share out. So here it is.

Here is a screenshot of what we are discussing in this post:

image

You will see the service status through the Green and Red color arrow icons present next to the service name.

All of the magic to populate the information happens through WMI layer. When you launch SSMS and connect to a SQL Server, the Object Explorer window performs a lot of initializations. One of them involves getting the service information for the two services of interest from the machine where SQL Server is running. In order to get this information, Object Explorer connects to the WMI namespace \\TOKENLEAKSERVER\root\cimv2 and performs various WMI queries. In this scenario, I am launching SSMS from a remote machine named TOKENLEAKCLIENT and connecting a SQL Server named TOKENLEAKSERVER.

First the Object Explorer extracts information about the two services of interest from the WMI provider CIMWin32 using calls similar to the following:

Provider::GetObject - Win32_Service.Name=""MSSQLSERVER""

Provider::GetObject - Win32_Service.Name=""SQLSERVERAGENT""

After this, it sets up a notification to get state change information using the ManagementEventWatcher classes from System.Management. The notification query used is of the format:

IWbemServices::ExecNotificationQuery - select * from __InstanceModificationEvent within 10 where TargetInstance isa 'Win32_Service'

This essentially allows the Object Explorer to receive service status information every 10 seconds. Internally this will show up as the following query executed every 10 seconds under the wmiprvse.exe process that has the cimwin32.dll provider loaded:

IWbemServices::ExecQuery - select * from Win32_Service

This allows the Object Explorer to get service state change information at frequent intervals.

The polling interval of 10 comes from the default value used by Object Explorer. You have the flexibility to change this polling interval using the following configuration:

On 64-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\100\Tools\Shell => The PollingInterval DWORD should be set to value x.

On 32-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Shell => The PollingInterval DWORD should be set to value x.

The value x will correspond to the PollingInterval in seconds. If you set it to a value of zero, then no polling would occur and Object Explorer will not be able to obtain the service status information. Exercise appropriate caution when modifying registry values.

 

For all of the above mechanics to work, the Windows account launching SSMS need to have appropriate permissions to the cimv2 namespace in WMI. You will notice that by default “Authenticated Users” do not have the remote access to this namespace. Only Administrators group has this permission.

image

So, if you do not have the required permissions, you will see the following status information in the Object Explorer of SSMS.

image

If you have several SQL DBA’s connecting to the same server remotely via SSMS, then every one of these clients will perform these service polling at the frequency of the default polling interval (10 seconds). You might notice that the wmiprvse.exe and lsass.exe consume some resources to satisfy these requests.

 

While troubleshooting this problem, we also came across the Enterprise Hotfix Rollup for Windows Server 2008 R2. Close to 90 fixes and improvements are present in this rollup. It looks like a mini-service pack! And it contains WMI related fixes as well.

You can observe all the WMI activity I mentioned above using the WMI Tracing.

Thanks & regards

Suresh B. Kandoth

Sr. Escalation Engineer, SQL Server

Comments

  • Anonymous
    August 21, 2013
    You uncovered the secrets of the status icons of the Object Explorer! Thanks =)

  • Anonymous
    September 30, 2013
    Well Designed blog, I hope you keep adding such useful blog posts with videos, I bookmarked it :). www.24x7servermanagement.com

  • Anonymous
    April 24, 2014
    I tried adding Remote Enabled for authenticated user. Still the status does not show green to me.  SQL Server 2012 on Windows 2008 R2 when I remotely connect to SQLDB thru SQL server Management Studio.

  • Anonymous
    April 25, 2017
    Disabling via Registry does not work on SSMS 2016.PollingInterval seems to be ignored.