SQL SERVER – Out of the Box – Activity and Performance Reports from SSSMS

SQL Server management Studio 2008 is wonderful tool and has many different features. Many times, an average user does not use them as they are not aware about these features. Today, we will learn one such feature. SSMS comes with many inbuilt performance and activity reports, but we do not use it to the full potential.

Let us see how we can access these standard reports.

Connect to SQL Server Node >> Right Click on it >> Go to Reports >> Click on Standard Reports >> Pick Any Report.

Click to Enlarge

You can see there are many reports, which an average users needs right away, are available there. Let me list all the reports available.

  • Server Dashboard
  • Configuration Changes History
  • Schema Changes History
  • Scheduler Health
  • Memory Consumption
  • Activity – All Blocking Transactions
  • Activity – All Cursors
  • Activity – All Sessions
  • Activity – Top Sessions
  • Activity – Dormant Sessions
  • Activity –  Top Connections
  • Top Transactions by Age
  • Top Transactions by Blocked Transactions Count
  • Top Transactions by Locks Count
  • Performance – Batch Execution Statistics
  • Performance – Object Execution Statistics
  • Performance – Top Queries by Average CPU Time
  • Performance – Top Queries by Average IO
  • Performance – Top Queries by Total CPU Time
  • Performance – Top Queries by Total IO
  • Service Broker Statistics
  • Transactions Log Shipping Status

In fact, when you look at the above list, it is fairly clear that they are very thought out and commonly needed reports that are available in SQL Server 2008. Let us run a couple of reports and observe their result.

Performance – Top Queries by Total CPU Time

Click to Enlarge

Memory Consumption

Click to Enlarge

There are options for custom reports as well, which we can configure. We will learn about them in some other post. Additionally, you can right click on the reports and export in Excel or PDF. I think this tool can really help those who are just looking for some quick details. Does any of you use this feature, or this feature has some limitations and You would like to see more features?

Reference : Pinal Dave (http://blog.SQLAuthority.com)

7 thoughts on “SQL SERVER – Out of the Box – Activity and Performance Reports from SSSMS

  1. Pingback: SQL SERVER – Generate Report for Index Physical Statistics – SSMS Journey to SQL Authority with Pinal Dave

  2. Hi Dave,

    Your this article is really cool, I want to use these reports in my Asp.Net application, but I am unable to find the datasource of these report, could you plz help me to get datasource of these reports and if I can get some help from you for internal Architecture of these reports.



  3. Pingback: SQLAuthority News – Three Posts on Reporting – T-SQL Tuesday #005 Journey to SQL Authority with Pinal Dave

  4. I am interested in using these reports that you mention. However, when bringing up the All Transactions and Object Execution Statistics reports, there is no data, although other reports, such as Index Usage Statistics have data. Auto Create Statistics and Auto Update Statistics are both set to true in the database properties. What else should I check?


  5. Pingback: SQL SERVER – Standard Reports from SQL Server Management Studio – SQL in Sixty Seconds #016 – Video « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s