When trying to open Activity Monitor in SQL Server 2008 R2, you get this error:
The Activity Monitor is unable to execute queries against server [SQL Server Name].
Activity Monitor for this instance will be placed into a paused state.
Use the context menu in the overview pane to resume the Activity Monitor.
Could not find stored procedure ‘#am_get_querystats’
SQL Server can’t find the #am_get_querystats stored procedure because it isn’t there. It’s not there because you’ve got a Server 2005 database and you are querying it with SQL Server Management Studio 2008 R2.
To fix this, either upgrade the databases to SQL Server 2008 R2 version (Compatibility level 100) or use SQL Server Management Studio 2005 to monitor the activity of the SQL server.