New Activity Monitor – What you should know about the Refresh Interval

Now that you know where to find the Activity Monitor in SQL Server 2008 Management Studio, here are some things you should know about how the refresh interval works that isn’t completely documented.

Unlike the previous version of the Activity Monitor (AM), the new AM starts up with a 10 second refresh interval. That doesn’t necessarily mean that each of the four panes: Overview, Processes, Resource Waits, Data File I/O, and Recent Expensive Queries all refresh at the specified interval. First, if all panes are closed, the AM issues no queries against the backend. AM issues its queries only for the open panes. Each pane has a separate minimum refresh value so that the server doesn’t get saturated with queries – especially with a refresh interval of 1 second. Here are the minimum refresh intervals for each of the panes.

• Overview – 1 second
• Processes – 4 seconds – this provides little time window for selecting a SPID so that you can right click and take an action
• Resource Waits – 1 second
• Data File I/O – 3 seconds – this query consumes a few more resources that for wait stats.
• Recent Expensive Queries – 15 seconds – this is even more expensive.

By using these minimum refresh levels with the default refresh interval, with all panes open, the AM will consume less than 5% of your system resources desktop class PC running SQL Server. Please keep in mind, if you have more than one client attached to the database instance running AM, each client will be running the same set of queries.

Check out SQL Server 2008 Books Online – Activity Monitor for the most up to date help.

Bookmark this on Delicious