Performance Analysis Using SQL Server 2008 Activity Monitor Tool


By:   |   Updated: 2010-01-12   |   Comments (8)   |   Related: More > Monitoring


A Beginners Guide to SQL Server Monitoring

Free MSSQLTips Webinar: A Beginners Guide to SQL Server Monitoring

Monitoring is more than a little complicated. You might need to know what is going on right now, what was going on while you were at lunch, or any of a dozen trends over the past year. You want to measure how things are performing, but you also need to know when things are going wrong - jobs failing, deadlocks, and timeouts are just some of the things we need to check on sooner rather than later.


Problem

While looking through the new features and improvements in SQL Server 2008 Management Studio (SSMS) we found a potentially interesting one called Activity Monitor. Database developers and Database Administrators can use Activity Monitor to get a quick overview of an SQL Server 2008 system performance. Can you give us a detailed explanation of how we go about using Activity Monitor?

Solution

The Activity Monitor which is available in SQL Server 2008 Management Studio is a great tool which can be used by database developers and administrators to get a quick overview of SQL Server 2008 system performance. The Activity Monitor tool in the previous version of SQL Server used to display information related to Processes, Lock by Objects and Locks by Process. There are many enhancements in Activity Monitor in SQL Server 2008 like a graphical display of Processor Time, Waiting Tasks, Database I/O's, Batch Requests, Processes, Resource Waits, Data File I/O's and also information about the most expensive queries. However, to view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.

Different Ways to Open up Activity Monitor in SQL Server 2008 are mentioned below:

Open up Activity Monitor Using Object Explorer

In Object Explorer, right click the SQL Server 2008 Instance and select Activity Monitory from the drop down list as shown in the snippet below.


Open up Activity Monitor in SQL Server 2008 Management Studio

In the SQL Server 2008 Management Studio's toolbar, click Activity Monitor as shown in the snippet below.


Open up Activity Monitor When SQL Server 2008 Management Studio Starts

In the Tools menu, click Options. In the Options dialog box expand Environment and then select the General tab. In the "At Startup" drop down you need to chose "Open Object Explorer and Activity Monitor" option from the drop down list and click OK. In order to see this change you need to close and reopen SQL Server 2008 Management Studio.


Overview of SQL Server 2008 Activity Monitor

In SQL Server 2008, Activity Monitor is divided into five sections namely Overview, Processes, Resource Waits, Data File I/O and Recent Expensive Queries. We will cover each section in detail.

Overview: - This shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.

Processes: - This shows the list of all the active users which are connected to SQL Server Database Engine. Here, you can right click any of the Session IDs which you think are problematic and can run a SQL Server Profiler Trace to capture all its activities; you can also see the Session Details or can even KILL a process.

Resource Waits: - This provides valuable information with respect to a thread which is waiting for a key resource such as Memory, CPU and Network etc on an instance of SQL Server. This helps database administrators indentify potential bottlenecks with respect to Memory, CPU, Network I/O etc.

Data File I/O: - This displays disk level I/O information related to all the data and log files of user and system databases. Database administrators can quickly identify databases which are performing badly due to disk bottlenecks.

Recent Expensive Queries: - In this section, database administrators can quickly identify poorly performing queries in an SQL Server Instance. With the feature, which I like the most, you can right click any of the problematic queries and choose the "Edit Query Text" option to edit the query. Moreover, you can also see the execution plan of the poorly performing query by right clicking the problematic query and by selecting the option "Show Execution Plan".

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips


Article Last Updated: 2010-01-12

Comments For This Article




Friday, March 21, 2014 - 6:24:35 PM - Joe Back To Top (29833)

In Activity Monitor - Resource Waits, if a thread is waiting for a key resource such as Memory, what would cause such a wait?


Friday, October 4, 2013 - 8:08:35 PM - SQLDBA Back To Top (27047)

Already cpu is utilization 99 % ,then is feasible to use activity monitor  ?


Monday, March 18, 2013 - 4:18:34 PM - Artur Back To Top (22852)

NICE!!! thanks


Wednesday, October 24, 2012 - 6:26:50 PM - Andres Back To Top (20077)

How can you know the standards of these statistics? Need to know the impact of several queries I am running and this is a great tool, but wouldn't know how bad are my queries since I don't see a standard to compare them.


Friday, May 18, 2012 - 11:27:37 PM - Alok Back To Top (17545)

For a rank newcomer like me, this is an amazing place to gain knowledge.


Wednesday, January 11, 2012 - 11:37:38 AM - Ashish Kumar Mehta Back To Top (15613)

Hi Venkatarao & Flip,

You are most welcome. Keep visiting MSSQLTips for more learning.

Cheers
Ashish Kumar Mehta
MSSQLTips - Author


Monday, January 9, 2012 - 6:10:06 PM - Flip Back To Top (15572)
No qeustion this is the place to get this info, thanks y'all.

Monday, January 10, 2011 - 8:05:16 AM - Venkatarao Back To Top (12551)

Really good and very helpful



download














get free sql tips
agree to terms