Enabling & Using Activity Monitor in SQL SERVER 2005/2008

Today i am desperate need of using Activity Monitor in SQL SERVER 2008 to find out slow running Query and Lock and Wait times to find blocking queries. When I open my sql server managment studio i did not find activity monitor and after some search in tools and menus i am unable to find any table that enables activity monitor because it is quite different from 2005. 

Now i have found a very good link that describe me how to enable and what type of information i can get through it. I am sharing this on my blog here. New Features in SSMS 2008

Activity Monitor

In SQL Server 2005, it was easy to find and start the Activity Monitor. You just opened up the Management object in SSMS and double-clicked on Activity Monitor, and you could instantly view process information. In SQL Server 2008, they have made the  Activity Monitor a little harder to find, but once you have learned where it is, you will probably be very impressed with all the new features that have been added.

To start Activity Monitor in SQL Server 2008 SSMS, right-click on the SQL Server name you want to monitor, and then click on Activity Monitor. The following screen appears:

Figure 1: The Activity Monitor has had a radical facelift, and feature boost.

Immediately, you can see that Activity Monitor looks entirely different than in SQL Server 2005. The first things that jump out at you are the four graphs. These display % Processor time (of the SQL Server process “sqlserv ” spread over all of the available CPUs, not for the entire server), Waiting tasks, Database I/O, and Batch Requests/sec. In the past when you needed this information, you had to use System Monitor or some other tool. Now, if your server is behaving strangely and you need a quick overview of what is happening, you can get it directly from Activity Monitor.

Below the graphs you will find four additional windows of information, the first of which (Processes) is shown in Figure 2:

Figure 2: This looks a little more familiar. Here, we see all of the active SPIDS.

When you open the Processes window, you see the SPIDs that you are so familiar with from the SQL Server 2005 SSMS Activity Monitor. Now, not only can you see the SPIDS, and sort them, but you can also filter on them using the drop-down boxes at the top of each column. And if you right-click on any of the SPIDs, you can choose to automatically launch Profiler, which will begin a trace on the SPID in question. This makes it very easy to begin a Profiler analysis of any SPID that you want to investigate.

Next, we move on to the Resource Waits window, shown in Figure 3:

Figure 3: We can see the current wait states of active threads.

The ‘Resource Waits’ screen provides a snapshot of key resource waits occurring on the server, thereby helping you to identify potential trouble with your SQL Server. Resource waits measure the amount of time a worker thread has to wait until it can gain access to the resources on the server that it needs, such as memory or CPU. A high resource wait time might indicate a resource bottleneck. As with Processes, you can sort and filter on any column.

The third window is Data File I/O, as shown in Figure 4:

Figure 4: Use the Data File I/O screen to identify databases with heavy I/O activity.

If you suspect that a particular database is being heavily hit with disk I/O, you can quickly find out by using the Data File I/O screen. You can sort and filter on any column with this or the other related screens.

The final screen is "Recent Expensive Queries":

Figure 5: Want to know what your most expensive queries are? Find out here.

If you are having performance problems due to resource-intensive queries, then the Recent Expensive Queries window will show you the most recent expensive queries (those currently in cache), allowing you to sort or filter them by any column, making it easy to identify problem queries. If you right-click any of the queries, you have the option of displaying the entire query (not just the small part of the query you see in the window) and you also have the option of displaying a graphical execution plan of the query.

Another feature that you might miss, if you are not careful, it the use of Tool Tips throughout all the screens of the Activity Monitor. If you move the cursor on top of almost any text on the Activity Monitor screen, a Tool Tip will appear, providing you with useful information on what you are seeing. Most of the data displayed in the Activity Monitor are from DMVs. Many of the Tool Tips even tell you the name of the DMV used to return the data you are viewing.

When you first lay hands on a copy of SQL Server 2008, you should start by trying out the new Activity Monitor. I guarantee it will make it much easier for you to quickly get a high-level perspective on what might be ailing your SQL Server. Once you know the big picture, then you can use other tools, such as DMVs, Profiler, or System Monitor, to drill down for more details.