SQL Server MVP Deep Dives 2, Chapter 12
For this chapter Pawel Potasinski has done exactly what the title suggests. He lays out the steps to create a simple performance dashboard for SQL Server. He specified the version in his chapter title, but he clarifies that these steps can be “implemented on SQL Server 2005 and later (where CLR, DMV’s and SSRS are available).” That one sentence sort of gives away his whole strategy. He shows how to use a Common Language Runtime (CLR) function to get at operating system performance counters. He then combines this with SQL Server Performance counters from the dm_os_perfornamce_coutners DMV and uses a SSRS report to display them both.
Unfortunately I did not see as much value in this chapter as some of the others. It does show a simple way of getting at performance data and I had fun playing with CLR again after many years (much easier with VS 2012 than it was back in 2003), but there are so many tools available now that can monitor SQL Server it is hard to justify building your own. There are even some free/open source monitoring tools out there now that do what Pawel is proposing (I am specifically thinking of Stack Overflow’s Opserver, which was put up on GitHub last month).
If you are not familiar with the mechanics of performance monitoring for your SQL Server I do see value in stepping through this chapter as it will connect some dots. And I did create the CLR function he outlines in the chapter, but I will be honest and say that I never got it to run. I decided not to jump through all of the SAFE/UNSAFE/EXTERNAL_ACCESS hoops needed to run the function. CLR has always been a tool of last resort for me and I haven’t used it in years. Pawel does a good job of listing the steps you will need to make this work, but if you aren’t familiar with CLR you can read up on it on this TechNet page.
Pawel ends his chapter with some ideas to improve the solution and I could see taking that list and building a pretty nice little tool. You can even use his code from the Manning website as a jumping off point. Of course you can also just download some of the similar tools that already exist.
Chapter Twelve SQL Server MVP:
Pawel Potasinski (Blog|Twitter) is a former SQL Server Most Valuable Professional (MVP) working as a senior consultant for ABC Data IT Consulting. He’s been working with SQL Server since 2000. His current focuses are data warehousing, ETL processes, performance troubleshooting, and dynamic code generation. He’s held Microsoft Certified Trainer (MCT) certification since 2004. In 2007, he founded the Polish SQL Server User Group (PLSSUG).