SQL SERVER – Introduction to SQL Server Management Studio Reports

I get a ton of questions over mail and one of the most theme is always around Performance tuning and troubleshooting. To add to this confusion, even my chat window mostly involve around talking about some performance problem with SQL Server. Let me tell you some of these common themes or questions:

1. How to improve performance?
2. How do I know problematic queries?
3. What is consuming my space in HDD?
4. How to find out CPU hogging queries?
5. Who dropped my tables / database?
6. Who is consuming my memory on this SQL Server?
7. When was the backups / restores happen on this Database?
8. I am can see a lot of IO activity on my production server with 50 databases, I don’t know who is causing the IO now?
9. What is the Index usage statistics?

And this list keeps going on and on. For most of the queries there are my pluralsight courses on performance that you can watch or there are tons of scripts over this blog itself that can help you answer a number of above queries. I will refrain from linking all of these in this one blog but we will look at a different handy utilities available with SQL Server Management Studio out-of-the-box.

In this era of obsessed enterprises, the CxO are always wanting to look at the health of their systems in new and innovative ways via Dashboards and reports. This is sometimes like driving a vehicle, you know the health of the vehicle by watching its dashboard for speed, fuel, rpm or any other warning indicators. So to run a business the executives rely on these reports from their teams. In a similar way, a lot of DBA’s just wish they were empowered with such capabilities for their day-today lives too. Lesser known to them is that since SQL Server 2005, such reports have been added out-of-box to them at their fingertips.

Reports: Where to find them?

There are a number of reports available with SQL Server Management Studio and they can be found at two locations:

1. On Server Node -> Right Click -> Select Reports -> Standard Reports
2. On a specific Database -> Right Click -> Select Reports -> Standard Reports

In this example, we will be using a SQL Server 2014 instance. Under the Server node we can see close to 23 different reports.

Under the Database node, we can find close to 18 different reports to work with.

Administrators Reports Center

Now that we have got to know these reports existing on SSMS. It is worthwhile to explore some of them. This series will be to explore these hidden gems and how one can use them effectively in their day-today lives.

All the reports have some use and help administrators solve basic questions like the one asked before. The simplest I can think today is around the questions of database:

1. What is my database disk usage
2. Disk usage by Tables, Partitions
3. What are the transactions blocking and much more.

Recently a Developer caught me at the local SQL Server Bangalore UG and asked if they can modify the same. Interestingly, these reports are not stored as plain vanilla .rdlc files but in some other internal formats. Having said that, it is always possible to create our own custom .rdlc files and use the same with SSMS. In this blog series, we will look at functionality of the majority reports that a DBA must be aware and how we can maximize our time by being productive with these reports. Hope you will enjoy this journey with me.

PS: If you have been using these reports in your environment, can you let me know the reports that you use most often and why?

Reference: Pinal Dave (https://blog.sqlauthority.com)