SQL Server Best Practice Analyzer

I took the SQL Server 2000 Best Practices Analyzer Beta for a spin this evening in a virtual PC. The download and install worked without problems. The installation needs to create a database to store rules and report results – the default name is sqlbpa.

After registering a SQL Server to analyze I went to the Best Practices screen, where I selected “Create Best Practice” and began to look through the available rules. Rules fall into the following categories:

• Backup and Recovery
• Configuration Options
• Database Design
• Database Administration
• Deprecation
• Full-Text
• General Administration
• Generic
• T-SQL
• Yukon Readiness

Each category contains a set of rules, many of which you can parameterize. For example, you can check to see if databases have successfully backed up in the last X days (where X defaults to 30, but you can enter your own value).

Here are some of the interesting rules you can run:

Index fragmentation: you can specify the maximum value for fragmentation, and the minimum number of pages an index must have to be included in the scan.

File placement: ensures data and log files are not on the same drive.

Unexpected shutdowns: looks in the NT event log and flags any unexpected shutdowns.

Object Prefixes / Object Suffixes: enforces naming standards. You can select a prefix / suffix for each object type, although it is not clear to me how to setup more than one type of object to scan. Perhaps this is a beta issue.

NULL comparisons: scans stored procedures, views, triggers, and functions to find equality and inequality comparison with NULL constants.

Temp table usage: looks for opportunities to replace temp tables with table variables.

Once I had a set of best practices defined I could move them into the execution area and scan one more or SQL servers. Reports are saved into the database for viewing through the UI.

This looks like an extremely productive tool for watching databases both in development and production. There are managed code assemblies in the bin directory, and possibly room for extensions, as a quick peek in the BPA database shows assembly names. Perhaps custom rules can be put into a custom assembly and registered in the database also.

Definitely going to add this tool to the toolkit.