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:
- Click Start, click Run, type DCOMCNFG, and then click OK.
- In the Component Services dialog box, expand Component Services, expand Computers and then right-click My Computer and click Properties.
- In the My Computer Properties dialog box, click the COM Security tab.
- Under Launch and Activation Permissions, click Edit Limits.
- 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.
- Under Access Permissions, click Edit Limits and give Remote Access to your user.
- Go to DCOM Config(Expand My Computer), find “Windows Management Instrumentation”, right-click and select Properties.
- 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 http://msdn.microsoft.com/en-us/library/windows/desktop/aa393266(v=vs.85).aspx
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): http://support.microsoft.com/kb/932370