Performance Analysis Using SQL Server 2008 Activity Monitor Tool

I was going through one by one new and enhanced features of SSMS 2008.I come across Activity monitor which I felt greatly improved in comparison to previous version.

Activity monitor is a tool to monitor and get quick review of SQL SERVER system performance for Database developers and Database Administrators.

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 but in SSMS 2008 it is greatly enhanced. Improvements like a graphical display of processor time, waiting tasks, Database I/O’s, Batch requests, Processes, Resource Waits, Data file I/O’s, Most expensive queries etc.

To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.

Enable Activity Monitor.

Go Tools>Options >Environment>General> At startup

Select drop down option ‘Open object Explorer and Activity Monitor’

Start Activity Monitor

Two ways you can start activity monitor.

1.       Open Object Explorer, right click the SQL Server 2008 Instance and select Activity Monitory from the drop down list.

2.       In toolbar select Icon for Activity monitor.

When you ‘Activity Monitor’ you will find tabs –

1.       Overview

2.       Processes

3.       Resource Waits

4.       Data file I/O

5.       Recent Activity Files

I will explain each briefly with figures.

1.       Overview –  It  will display four graphical window

                                             I.             Processor Time (%)

                                           II.            Waiting tasks

                                         III.            Database I/O (MB/sec)

                                         IV.            Batch Request /sec.

2.      Processes:   This will show you all active users connected to server database. Here you can see the session Id, user processes, login, Database, task status, command, Application Name, wait time (sec), wait Resources etc.

You can right click on session id you will get three options

Details

Kill Process

Trace process in SQL Profiler

 3.       Resource Waits:

It provides information about thread which is waiting for a key resource such as Memory, CPU and Network etc on an instance of SQL Server. It helps DBAs in indentify potential bottlenecks with respect to Memory, CPU, and Network I/O etc.

4.       Data File I/O:

It displays disk level I/O information related to all the data and log files of user and system databases. DBAs can quickly identify databases which are performing badly due to disk bottlenecks.

5.       Recent Expensive Query:

DBAs can quickly identify queries, which are running slow in an SQL Server Instance.

Here you have two options

                                                        I.            Edit Query Text

                                                      II.            Show execution plan

You can right click any of the queries and choose the "Edit Query Text" option to edit the query and you can also see the execution plan of a query by right clicking on the query and by selecting the option “Show Execution Plan".