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:
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.
So, if you do not have the required permissions, you will see the following status information in the Object Explorer of SSMS.
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