Chapter 12. Build your own SQL Server 2008 performance dashboard

 

Pawel Potasinski

Have you ever seen any of the fantastic applications for monitoring performance of database systems? How wonderful your life as a DBA would be if you could sit in your chair, looking at a big screen full of information showing how your server and databases are performing and, what’s even more important, how to react when an issue occurs. This is why powerful tools for monitoring SQL Server databases are popular and worth their price.

But what if the budget doesn’t allow you to buy any additional software and you have to rely only on the information that SQL Server itself gives you? The purpose of this chapter is to give you some ideas on how to use SQL Server features, like Common Language Runtime (CLR), dynamic management views (DMVs), and SQL Server Reporting Services (SSRS), to create your own performance dashboard that will prove helpful in your everyday DBA work.

The approach proposed in this chapter can be implemented in SQL Server 2005 and later (where CLR, DMVs, and SSRS are available).

DMVs as the source of performance-related information

I think that Books Online, the official SQL Server documentation, contains the best definition of the DMVs: “Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.” DMVs are a great source of performance-related information.

Using SQLCLR to get the performance counter values

Sample solution for performance monitoring

Use Reporting Services for performance monitoring

Some ideas to improve the solution

Summary

About the author