Best Practices Analyzer - 23 May 2007

Best Practices Analyzer - 23 May 2007

Find and fix potential SQL Server 2005 problems

To help database professionals strengthen SQL Server, the SQL Server Development Team introduced the SQL Server Best Practices Analyzer (BPA), a tool that checks your SQL Server for common best practices and when it finds something outside of the norm, helps you ensure that you're implementing the conventional wisdom on SQL Server configurations. In its first release (with SQL Server 2000), BPA checked more than 70 parameters on a SQL Server instance and in individual databases. The Microsoft SQL Server Manageability team, led by program manager Paul Mestemaker, has introduced many improvements in the SQL Server 2005 BPA. The tool includes a predefined list of SQL Server 2005 recommendations and best practices, which it compares against the settings in use in the current database environment, pointing out potential problems as it finds them. In addition, the SQL Server 2005 BPA includes the following upgrades:

Better rules. The SQL Server customer support team was directly involved in developing new rules that address common SQL Server 2005 problems. The rules address the relational engine and security settings as well as settings in SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS). In addition, a feedback component lets users report new findings that Microsoft can channel back to active BPA users through an auto-update feature.

Better XML integration. You can save the results of BPA scans as XML (instead of only to a SQL Server repository). Plus, you can save scan setup settings as XML and later reuse them through the GUI or command-line interface.

Better reporting. A new pivot feature in the scan report makes it easier to see problems and remedies. New search filters make it easy to find specific elements of a report and greatly simplifies scans of large, multi-server environments. The scan report also includes more information. For each potential problem, BPA provides an article describing how the problem was detected, background information about the problem, how to remedy the problem, and where to get more information.

Better BPA UI. You can set BPA to automatically run during low-utilization times. You set up automated scans through XML or the new command-line interface, SQLBPACmd.exe, which offers granular control of which rules to scan. SQLBPACmd. exe is automatically installed in your client's <drive>\Program Files\SQL Server Best Practices Analyzer directory. Check the product documentation for full details about how to use the BPA command-line interface.

Note that the T-SQL code-checking best practices have been removed from this version of BPA. Microsoft has given no official word about whether similar code-checking features will appear in another tool, such as a Visual Studio plug-in.

For each server that the tool scans, BPA will consume 5MB–10MB of RAM, use 50–75 percent of CPU, and require 0.5MB–2MB of disk space to store XML data. Therefore, I recommend running BPA on a client against a target server rather than on the server that you're scanning.


BENEFITS: Checks best practices and helps you implement them.


  • Windows Server 2003, Windows Vista, or Windows XP OS
  • SQL Server 2005 Client Tools
  • .NET Framework 2.0 (ships with Visual Studio 2005 or you can download the .NET Framework SDK 2.0)

HOW TO GET IT: At the time this article was written, the BPA February CTP was available.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.