Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

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     



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

Really good and very helpful


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.

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


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, 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.


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

NICE!!! thanks


Friday, October 04, 2013 - 8:08:35 PM - SQLDBA Read The Tip

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


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?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.