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

March 25, 2010

SQL Server management Studio 2008 is a 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 reports 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.

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

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

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

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

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

Memory Consumption

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

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 (https://blog.sqlauthority.com)

SQL Reports, SQL Server, SQL Server Management Studio

SQL SERVER – MAXDOP Settings to Limit Query to Run on Specific CPU

SQL SERVER – Generate Report for Index Physical Statistics – SSMS