Built in SQL Server Agent Performance Reports in SQL Server 2005


By:   |   Updated: 2010-05-21   |   Comments (4)   |   Related: More > SQL Server Agent


Problem

Finding a good reporting mechanism for your SQL Server Agent can be tedious and time consuming. You can either write your own reporting application or choose a third-party solution. You may also have to install an instance of Reporting Services in your environment, depending on the needs of the application. SQL Server 2005 includes a number of built-in reports to assist you in troubleshooting and measuring performance. In this tip I will cover the Built in SQL Server Performance Reports related to SQL Server Agent within SQL Server 2005.

Solution

As part of the installation of SQL Server 2005 a number of performance related reports are installed. To get to these reports open the SQL Server Management Studio (SSMS) and connect to a SQL Server 2005 instance. If you don't have an instance of Reporting Services installed then the icon will be disabled. You can download SQL Server 2005 Performance Dashboard Reports which can be used to monitor and resolve performance problems on your SQL Server 2005 database server from the following link. To know more about Built-In Database Engine Performance Reports in SQL Server 2005 you can check the following link.

Once connected to the SQL Server Database Engine in SQL Server 2005, click on the SQL Server Agent and then the Reports button in the Summary section as shown in the snippet below.

Once connected to the SQL Server Database Engine in SQL Server 2005, click on the SQL Server Agent and then the Reports button in the Summary section

There are basically two built in SQL Server Agent reports, the name and usage of their reports are mentioned in the below table.

Report name Description
Top Steps Execution History This report provides details on the success and failure of execution of individual job steps over the last 7 days as recorded in MSDB.
Top Jobs This report identified individual jobs that may be of interest for a variety of reasons.

Lets us go through each of these reports in details to understand how they can be used.

Job Steps Execution History Report

You can select Top Steps Execution History report from the summary section to quickly understand how many total executions the job had in the last 7 days, number of times the job failed, the average run time durations, retry attempts etc. This is an easy way to understand how each job is performing in each instance of SQL Server.

an easy way to understand how each job is performing in each instance of SQL Server

Top Jobs Report

You can select Top Jobs report from the summary section to quickly understand overall job execution status in an instance of SQL Server 2005. In this report you can also check the 20 most frequently executed jobs, 20 most frequently failing jobs and the 20 slowest jobs.

select Top Jobs report from the summary section to quickly understand overall job execution status in an instance of SQL Server 2005

You can expand the 20 Most Frequently Executed Jobs and the 20 Most Frequently Failing Jobs to get the complete list of frequently executing and failing jobs respectively as shown in the snippet below.

expand the 20 Most Frequently Executed Jobs and the 20 Most Frequently Failing Jobs

Moreover, you can expand the 20 Slowest Jobs in the Top Jobs report to get the list of the slowest jobs as shown in the snippet below.

 you can expand the 20 Slowest Jobs in the Top Jobs report to get the list of the slowest jobs
Next Steps


Last Updated: 2010-05-21


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





Comments For This Article




Monday, January 23, 2012 - 9:34:19 AM - Ankit Shah Back To Top (15727)

Good things to know about sql agent.Is there any way that we can email out this history report ?


Thursday, February 24, 2011 - 8:06:12 AM - Jose Back To Top (13028)

Guys,

The article does a good description of the report built-in capacities but some informations were a bit wrong:

You will find the reports described in the article if you click right on

Instance Name -> Reports ->Standard Reports, to have server scoped reports

Database Name -> Reports ->Standard Reports, to have database scoped reports.

SQL Server Agent  -> Reports ->Standard Reports, to see the 2 reports described in the article.

Reports may vary depending you are using SQL Server 2005 or 2008. Note than the reports would not run for databases with a compatibility level inferior to 90 even if they are hosted on a SQL Server 2005 or 2008.


Thursday, January 20, 2011 - 1:58:49 PM - David Shink Back To Top (12647)

Ashish:

       I am an Oracle DBA who has responsibility for several, mostly small, SQL Server databases.  I just came across this post from last June.  Like DavidB, I could not find the Summary panel, but found the reports you spoke of by right clicking on the SQL Server Agent.  I found this to be one of the most helpful posts I have seen.  I have had a little exposure to SQL Server since version 4.2, but have never been involved enough to learn all I would like to know.  I cannot call myself a novice, but this post really helped.

                                                              David Shink

 


Friday, May 21, 2010 - 8:34:19 AM - DavidB Back To Top (5415)

Thanks for the tip.

 The only trouble I had in using the reports was finding them on SSMS (9.00.4035). My Summary panel is named Object Explorer Details and there is no report button. Instead I right clicked on SQL Server agent in the Object Explorer panel and selected Reports > Standard Reports which shows the two reports you mentioned. These reports are also available on SQL Server 2008 SSMS



download





Recommended Reading

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

SQL Server Agent Job Schedule Reporting

Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables








get free sql tips
agree to terms


Learn more about SQL Server tools