SQL Server Reference Guide

In previous versions of Microsoft’s SQL Server management tools, such as Enterprise Manager for SQL Server 2000 and lower and SQL Server Management Studio for SQL Server 2005 and higher, the Activity monitor was a small, not-very-useful tool. It displayed some textual and columnar information about the processes running on the Instance of SQL Server that you were connected to.

The reason that people didn’t value Activity Monitor a great deal is that this graphical tool mimicked the information you could get from system tables and views, and in the case of SQL Server 2005 and higher, the Dynamic Management Views. Its primary graphical values were that you could re-arrange columns, click to sort, and right-click to kill a process. It didn’t even allow you to see the entire query underneath the activity.

In SQL Server 2008, this tool is completely re-written. It has many new features that you’ll find will help you quickly locate problems, and as an added bonus it will guide you to other tools that will help you solve those problems. In this overview I’ll give you a quick tour of the screens you have access to with this tool, and how you can use it to evaluate what is happening on your system.

To start the tool, open SQL Server Management Studio (SSMS) and then right-click the Instance name and select “Activity Monitor” from the menu that appears. There’s also a icon in the first icon bar, all the way to the right you can click on to start it.

Figure 1

When to use Activity Monitor

If you’re having an immediate issue with your servers, the Activity Monitor is a great tool to start with. It will help you quickly identify issues that are happening on the system real-time.

Of course, you should be following the entire Performance Tuning process that I’ve previously outlined so that you can quickly identify (or even prevent) performance issues, but even so the Activity Monitor is very useful for these immediate issues.

One note here — the new Activity Monitor works well against SQL Server 2008 and 2005, but since it relies heavily on the enhanced information you can get from Dynamic Management Views, it does not work against SQL Server 2000. At the time of this writing, SQL Server 2000 is out of mainline support anyway, so you should upgrade those systems as soon as possible anyway.

The Activity Monitor in SSMS 2008 is made up of a series of “Bands”, which are darker bars you see there on the screen. If a Band is open, it is actively taking queries against the Instance you’re watching. If it is closed, it is not taking data for that band.

The activity that Activity Monitor is watching isn’t saved anywhere — it’s just recording the numbers as they happen, which is why it is best suited to immediate monitoring. And you can’t easily send the output anywhere, although you can press CTRL-C to copy the selected values (but not the headings) of the information.

You can also right-click the first Band I’ll explain and select the “Refresh Interval”, or how often it makes the queries for the performance data. I recommend that you set it to a very low value (like 1 second) for testing, but longer (Like 30 seconds or more) for production systems. You certainly don’t want to bury the system to find out what is wrong with it!

Another note — these tools use a mix of SQL Server queries, Dynamic Management Views (DMVs) and Windows Management Instrumentation (WMI) queries to get their data. If a column is empty or a graph is dark gray that means the account you’re using doesn’t have rights to run that query.

The Overview Band

The first Band I’ll explain is the Overview.

Figure 2

This is a set of four graphs that show the SQL Server Instance’s use of four key areas:

• Processor Time
• Waiting Tasks
• Database IO
• Batch Requests

The processor time shown here is a percentage of the time that SQL Server is exercising the processor — not the entire CPU itself. What that means is that while SQL Server might not be using a lot of processor, the Windows Operating system might have other tasks that are. Also, if you tied out only one processor to SQL Server on a dual-processor box, you need to take that into account as well.

Waiting tasks are the SQL Server operations that are waiting on a resource to do their work. There is an entire school of thought about using wait information for Performance Tuning, and it has merit. Basically this school of thought says that if your system isn’t waiting on anything, it is working as fast as it can.

The Database I/O graph shows how often the system is hitting the storage subsystem. It’s not an exact or comprehensive measurement, but it can quickly show you that this is where a lot of activity is going on. You want to avoid hitting the drive as much as you can, since this physical component is one of the slowest in the system.

Batch Requests show general SQL Server query activity, although it’s only one of the measurements in this area.

You can generally correlate various graphs for educated guesses about what is going on with your system. For instance, suppose that you see the Batch Requests and Database I/O graphs showing a great deal of activity, while the Processor and Waiting Tasks graphs stay low. This might indicate a large Insert, Update or Delete process that is running on the system. I’ve seen the Database I/O graph high while the Waiting Tasks graph is high as well, and it turned out to be an incorrect Clustered Index. Someone had created one of those on a table where a lot of non-unique values were entered, so whenever a new data change happened, the entire index was being physically recreated. That caused a huge amount of I/O, even for small Batches.

Although you can’t troubleshoot your whole system using just these four graphs, I have been able to quickly zero in on an issue using them.

The Processes Band

The Processes Band is the first of the textual-based, columnar outputs.

Figure 3

This band shows you information about the processes running on the SQL Server Instance. Rather than run down each column (I’ll point you to a resource for that at the end of this tutorial) I’ll explain how this band and the other text-based bands work, what you can do with a few of its features, and when you should use it.

To sort by any column, click it’s name one time. I use this a great deal with the “Head Blockers” column in this band, to show me the start of a blocking chain.

You can click any of the columns that shows a down-facing arrow icon and filter the column on one or more values. I use this to quickly isolate problems or connections in one database, or by a single user.

You can click and hold any column and drag it to a new position. That lets you arrange the view any way you would like. No, unfortunately this doesn’t “stick” in a certain order, meaning that if you come out of Activity Monitor and then open it again, it will be back to factory defaults.

You can click any row and press CTRL-C to copy that row to memory. No, it isn’t formatted well, and no, you can’t select multiple rows and no, you can’t output the graph or data in any other way that I’m aware of. But you really don’t need to — there are other methods for that as I’ll explain in a moment.

Here’s a handy tip: If you’re interested in knowing how the tool gets this information for text Band column, just rest your mouse over it. The tooltip will show you the scalar value its using or the view or function it uses to calculate the data.

Most of these Bands come with a set of right-click actions on a row. For the Processes Band you get three: Details, Kill Process and Trace Process in SQL Server Profiler.

Clicking on Details will bring up a small box with most (but not always all) of the query in it. Clicking on Kill Process will attempt to stop the process dead in its tracks — not always a good idea, but an essential requirement from time to time. And clicking on the Trace Process option is my favorite — this option jumps you right into SQL Server Profiler, and filters on exactly what that query is doing. This is a far better option than an output feature on this Band (which I’d still like to have, by the way) and can show you a plethora of information about this transaction. Very handy.

The Resource Waits Band

The Resource Waits Band shows the wait status and types for the SQL Server operations on an Instance.

Figure 4

The same rules for sorting, filtering and ordering apply here, but there are no right-click actions. This band is most useful (to me, anyway) in finding which element on my server is experiencing the most wait time, so I sort by Cumulative Wait Time.

The Data File I/O Band

One of the most useful parts of this tool in my experience is the Data File I/O Band.

Figure 5

Here you can see the Read and Write operations, by database and even by database file. It’s that last part that I find very useful. You can also see the Response time by file, which is great information when you’re testing a file layout schema.

Once again, no right-click actions here.

The Recent Expensive Queries Band

By far the most useful, and ironically most expensive, Band is the Recent Expensive Queries Band.

Figure 6

This Band shows just what the title says, and is a great way to find your top ten queries in a testing environment, or locating the main problem queries in a database. Once again you can sort, filter and order, and hover over the column names.

But it’s the right-click actions that really set this Band as the highest feature for me. When you right-click a query, you get two options: Edit Query Text and Show Execution Plan.

Selecting Edit Query Text brings all of the query — the entire thing — into a query window. Here you have Intellisense (2008 systems only), the Database Tuning Advisor, the new SQL Server 2008 Debugger, and more. I normally copy this query (even if it is 2005) and use it on my test system (which is 2008) so that I can find and tune issues. One tip here — remember to set the proper database context! You’ll probably be in “master” when it starts.

The second option is equally powerful. It will extract the graphical Query Plan from the query, even if it is on another server, even if the query is no longer running, and show it to you. You can debug exactly what the query is doing to ask yourself “why did it take that path?”

If you use this simple tool and evaluate just your top queries by Plan, I/O and process, you can almost always improve your system’s speed by a measurable factor. How awesome is that.

InformIT Articles and Sample Chapters

While this tool is great for immediate performance tuning efforts, you need to have a more comprehensive plan. Check out the start of this article series in the IT Management Reference Guide to learn more.

Books and eBooks

SQL Performance Tuning is a great place to start if you’re not sure how to tune SQL statments.

Online Resources

The documentation for all the columns on the text Bands is here: http://msdn.microsoft.com/en-us/library/cc879320.aspx.