Performance Analysis Using SQL Server 2008 Activity Monitor Tool

By: Ashish Kumar Mehta | Read Comments (4) | Print Ashish has been contributing to the MSSQLTips.com community since 2009 with over 60 tips. Related Tips: More

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

• Take the time to review these new tools in SQL Server 2008
• Read these other tips related to SQL Server 2008
• Identify SQL Server 2008 Performance Issues using
  • Performance Data Collection and Warehouse Feature of SQL Server 2008
  • Performance Data Collection and Warehouse Feature of SQL Server 2008 – Part 2

Related Tips: More | Become a paid author

Last Update: 1/12/2010

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

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.

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 *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. SQL Servers keeping you up at night? Contact the Edgewood SQL Server consultants for a Health Check. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. The SQL Server Security THREAT - It’s Closer Than You Think