Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Performance Analysis Using SQL Server 2008 Activity Monitor Tool

MSSQLTips author Ashish Kumar Mehta By:   |   Read Comments (8)   |   Related Tips: More > Monitoring

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



Last Update: 1/12/2010


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
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, March 21, 2014 - 6:24:35 PM - Joe Read The Tip

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 04, 2013 - 8:08:35 PM - SQLDBA Read The Tip

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


Monday, March 18, 2013 - 4:18:34 PM - Artur Read The Tip

NICE!!! thanks


Wednesday, October 24, 2012 - 6:26:50 PM - Andres Read The Tip

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 Read The Tip

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 Read The Tip

Hi Venkatarao & Flip,

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

Cheers
Ashish Kumar Mehta
MSSQLTips - Author


Monday, January 09, 2012 - 6:10:06 PM - Flip Read The Tip
No qeustion this is the place to get this info, thanks y'all.

Monday, January 10, 2011 - 8:05:16 AM - Venkatarao Read The Tip

Really good and very helpful




 
Sponsor Information