About one year ago I had a Microsoft Premier Field Engineer come out to perform a health assessment on our SQL environment, what Microsoft calls their Risk and Health Assessment Program (RAP). It was included in our Premier support contract but was worth every penny it would have cost if we’d had to pay for it. The assessment lasted for three days and was performed on one instance of SQL Server 2005. The Risk Assessment and Diagnostic Tool that is used does a good job of giving you a baseline of where you stand in terms of server and instance configuration as well as the usual documentation stuff. This tool is left with you and you are able to continue using it for the next 18 months to track your progress in addressing the issues found.
What I really found beneficial about the visit, though, was getting introduced to SQL Nexus and PAL. I’d heard of SQLDiag before, but I did not know there was a tool out there for turning SQLDiag’s output into meaningful and easy to understand information. SQLDiag is the tool MS Support Engineers use when you call up Microsoft for help with your databases, or so I’m told anyway. SQL Nexus is a CodePlex project you can use to configure and then interpret the SQLDiag and PSSDiag tools. You need to download all three of these tools. The Microsoft engineer took care of all the setup. The purpose of these tools is to help with performance troubleshooting of SQL instances. PAL stands for Performance Analysis of Logs Tool and works with the MS Performance Monitor counters to give you server level performance information. PAL is also a CodePlex project. Bottom line is if you’re in need of some really good and completely free performance testing tools, you definitely need to check these two out.
On the whole, I was extremely pleased with how the engagement went. I found the Microsoft engineer very knowledgeable and approachable. The performance tools have had a big impact on my daily DBA work. Because I support a lot of third party apps on my database server there was little I could do about many of the problems flagged by the Risk Assessment and Diagnostics Tool, but at least those problems are now documented. If you have a Premier support contract with Microsoft I definitely recommend having a SQL RAP done.
Now that I’m running SQL 2012 in addition to SQL 2005 I’m going to have to look at what new options are available out of the box that might make SQL Nexus obsolete. More on that in the future.