SQL Server Best Practices Analyzer

ToolKit

LANGUAGES: Any

ASP.NET VERSIONS: 1.0 | 1.1

SQL Server Best Practices Analyzer

Optimize Your Database for Performance and Efficiency

By Ken McNamee

I recently wrote about FxCop, a tool used to analyze .NET assemblies and provide advice on improving their performance and security (among other things). In this article I'll introduce another tool that provides a similar service, except it targets SQL Server 2000 databases instead of .NET assemblies. The SQL Server Best Practices Analyzer (SQLBPA) is a very powerful - and free - tool from Microsoft that contains a repository of best practices rules that can be easily configured to check one or more databases for compliance to these rules. In addition, SQLBPA provides commendable reporting and filtering capabilities, so you can quickly find potential issues with your database.

Using SQLBPA

The first thing that might strike you after installing SQLBPA is the realization that it actually requires its own SQL Server database to store settings and do its job. This is a marked difference from FxCop, which uses the concept of locally saved project files and doesn't require a database. However, the SQLBPA database should be seen as one of its strengths, because, being a database, it has built-in capabilities for the storage, exporting, and reporting of compliance data. In fact, SQLBPA even provides some pre-canned reports that can be easily integrated into SQL Server Reporting Services.

Unlike FxCop, SQLBPA requires a couple of steps before the analysis can begin. First, you must set up one or more SQL Server instances to which SQLBPA will connect. Second, there is the concept of a Best Practice Group, which is simply the name of the SQL Server instance(s) and the collection of rules against which you would like to check compliance. The Best Practice Group screen allows you to create new groups and bind them to a SQL Server instance for the analysis engine to execute. The SQLBPA is flexible enough so that you can set up a simple analysis of a local database, or a highly complex analysis of multiple databases on multiple servers, each with its own collection of rules to check.

Compliance Reporting

The analysis begins once you've selected the best practice group(s) to execute. Be aware that this can take several minutes for even a small, local database. Obviously this will take much longer for a more complex analysis, so you may want to set up your best practice groups to run on a schedule during a down period such as the middle of the night. To make this easier, SQLBPA comes with a command-line tool that can be executed from a scheduler. The only drawback to this is that - at this time - the command-line tool cannot use SQL authentication; it must use Windows authentication.

SQLBPA has decent reporting and filtering capabilities from within the client. Once the analysis has finished, you'll be presented with a screen similar to that shown in Figure 1, which is clean and easy to navigate. The first step I normally take is to filter only on the items that fall under non or partial compliance. From here I can view details about the rule that was violated, and about the specific portion of the database that caused the violation, as you can see in Figure 2. The information given is very good, making SQLBPA as much a learning tool as a problem detector.

Figure 1: The SQLBPA Compliance Report screen provides a clean and easy-to-navigate user interface to view the results of a rules compliance scan.

Figure 2: The SQLBPA Scan Details dialog box provides helpful information about the specific portion of the database that caused a rule compliance violation.

Prepare for the Future

SQL Server 2005 is going to be a major upgrade to the SQL Server family, and that's probably an understatement. There will be many new features and changes to the old features that most ASP.NET developers are probably not going to have the time to completely digest and understand. Therefore, one very useful SQLBPA feature is the ability to check a database to see whether it's ready for the upgrade to SQL Server 2005. Some issues that it checks for are the use of deprecated DBCC commands, obsolete stored procedures, and obsolete system tables.

Conclusion

I think it's fair to assume that most ASP.NET developers - myself included - are probably not fully qualified SQL Server DBAs. Therefore, a tool like SQLBPA can go a long way toward assisting us in illuminating and fixing potential problems with our databases, before they become real headaches several months into development (or even into production). However, SQLBPA's advice shouldn't be taken as gospel. Using a best practice recommendation is not always the best course of action. A best practice is merely an approach that works best for most people in most situations. You may find your particular development project requires an unorthodox approach that violates a SQLBPA rule. This is where good old human decision making comes in. That being said, SQLBPA is an incredibly useful tool, and should make a valuable addition to every SQL Server developer's toolkit.

Resources

Ken McNamee is a Senior Software Developer with Vertigo Software, Inc., a leading provider of software development and consulting services on the Microsoft platform. Prior to this, he led a team of developers in re-architecting the Home Shopping Network's e-commerce site, http://www.HSN.com, to 100% ASP.NET with C#. Readers can contact him at [email protected].

Comments

Plain text