SQL PASS: All the Magic Knobs – Tools

  • Article

SQL PASS: All the Magic Knobs – Tools

In my All the Magic Knobs talk at #SQLPASS 2011 I discussed some easy ways to determine if you’re using some of the performance magic for SQL Server. When you have many consolidated, non-tier 1 databases you don’t have a lot of control over, the best way to tune is to provide a solid, performant infrastructure through low effort, high impact choices. The same steps help in your tier 1 environments as well. The quickest way to see how close you are to that standard is to run one of our automated health checks. They check the SQL instance itself and some of the most important Windows settings that help SQL Server operate optimally.

SQL Best Practices Analyzer (BPA) is available for SQL Server 2000, 2005, and 2008/2008 R2. It is an add-in to the Microsoft Baseline Configuration Analyzer (MBCA). Both the MBCA and the SQL BPA are free. You can run the BPA locally or remotely and you can find plenty of sample scripts to run it against multiple instances. You choose your schedule for execution and you can either review the output after each execution manually or write your own program to alert you to what you consider the most serious items.

The System Center Advisor (SCA) is at this point still in pre-release. Licensing details will be available after release, for now you can download a free trial. It works for SQL Server 2008 and newer on Windows 2008 and newer. SCA runs on a schedule and sends alerts when a registered instance is not configured as advised. What it checks can change dynamically as PSS finds new important items.

Several companies, including Microsoft through our Premier Field Engineering (PFE) team, offer various health checks that include knowledge sharing and additional advice to help you decide if, how, when, and where to implement the recommendations.

Of course, you have to actually implement the recommendations to get the benefit; the tools listed above don’t do any remediation on their own. While that should go without saying, in my experience known recommendations often go unimplemented until some problem they would have prevented pops up.

For more SQL Server best practices see some of my other blogs: https://blogs.msdn.com/b/cindygross/archive/tags/best+practices/