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 http://www.microsoft.com/downloads/details.aspx?familyid=b352eb1f-d3ca-44ee-893e-9e07339c1f22&displaylang=en. 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.