Activity Monitor in SQL Server 2012 SP1

In SQL Server 2012 Service Pack 1 few changes were made in Activity Monitor. We had to stop using PerformanceCounterCategory class to get information about number of logical CPUs on the remote machine and switched to using more accurate information available via WMI (Win32_ComputerSystem.NumberOfLogicalProcessors if you’re looking for technical details).

Unfortunately it caused problems for existing customers in few very specific situations. Few bugs were reported by our customers:

In all cases problems were seen when running SSMS Activity Monitor from SQL Server 2012 SP1 trying to get CPU utilization graph from the remote servers running SQL Server 2008 R2 on Windows Server 2003. We didn’t see any reports when connecting to more recent version of SQL Server and more recent version of Windows Server.

There are two different errors you might see: “Access Denied” or “Invalid Query”.

If you are experiencing "Access Denied" errors you need to enable DCOM rights on the remote machine to allow WMI connections from the machine you’re running SSMS on. Here are the steps to do this:

  1. Click Start, click Run, type DCOMCNFG, and then click OK.
  2. In the Component Services dialog box, expand Component Services, expand Computers and then right-click My Computer and click Properties.
  3. In the My Computer Properties dialog box, click the COM Security tab.
  4. Under Launch and Activation Permissions, click Edit Limits.
  5. In the Launch Permission dialog box, select your user and group in the Group or user names box. In the Allow column under Permissions for User, select Remote Launch and select Remote Activation, and then click OK.
  6. Under Access Permissions, click Edit Limits and give Remote Access to your user.
  7. Go to DCOM Config(Expand My Computer), find "Windows Management Instrumentation", right-click and select Properties.
  8. In the Security tab, click on Edit under Launch and Activation Permissions, and give your user Remote Launch and Remote Activation. 

For more information, see Securing a Remote WMI Connection

If you’re seeing “Invalid Query” error message most likely your remote machine doesn’t have updated WMI objects. This can be easily fixed by installing the following hotfix (on the remote machine): 

Comments (3)

  1. Lynn Teska says:

    I get the access denied error, despite the Windows 2008 R2 server and not 2003.

    The SQL server is 2008 (R1).

    SQL 2008 tools activity monitor is fine, just not the 2012 sp1 version.

  2. Pam B says:

    Thanks! This fixed our issue.

  3. Pam Burks says:

    Thanks for the post. This fixed our issue in Windows 2012/SQL 2012 SP1.

Skip to main content