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:
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
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.
Thanks! This fixed our issue.
Thanks for the post. This fixed our issue in Windows 2012/SQL 2012 SP1.