Best Practices Analyzer - 23 Mar 2004

If you're a new SQL Server administrator or a part-time DBA who isn't familiar with SQL Server, you'll find Microsoft's new administration tool, the SQL Server 2000 Best Practices Analyzer, invaluable. The product is available for download at The Best Practices Analyzer uses a rule reference to check for implementation of common best practices, most relating to usage and administration. The rules include more than 70 best practices and guidelines that Microsoft provides for managing and operating your system. You can also define your own set of best-practices rules. Here are my seven favorite rules that the Best Practices Analyzer checks for.

7. Database Backups

Performing regular backups is crucial for recovering your systems. By default, this rule checks whether your latest database backup is more recent than 30 days. You can customize the default value to your organization's needs.

6. Failed Backup

This rule determines whether any of your database backups have failed by checking the Windows NT Event log. A backup failure can significantly impair your ability to restore any lost data. By default, this rule checks for backup failures within the past 30 days.

5. Index Fragmentation

The Best Practices Analyzer uses this rule to test for high levels of index fragmentation. By default, the rule tests for fragmentation levels higher than 20 percent for indexes that have more than 10,000 pages. As SQL Server maintains indexes, they can become fragmented and degrade performance. You can defragment an index by using the DBCC INDEXDEFRAG or DBCC REINDEX commands to recreate the index.

4. INSERT Column List

This rule checks for INSERT statements that don't explicitly define the columns being inserted. Further, it checks the INSERT statement usage in stored procedures, functions, triggers, and views.

3. SELECT Statements

Microsoft recommends using explicit column lists for all SELECT statements. This rule checks for column lists in SELECT statements inside stored procedures, functions, triggers, and views.

2. Database File Placement

Placing data (.mdf) and log (.ldf) files on the same drive will significantly degrade your system's performance. This rule ensures that data and log files for each of your databases are placed on different drives.

1. Recovery Model Usage

The Best Practices Analyzer checks for recent database log backups for all databases that are using the Full or Bulk-Logged Recovery model. Backing up the transaction log is vital to minimizing data loss in the event of system failure. By default, this rule makes sure that the log has been backed up within the past 30 days.

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.