img by Coba

Recommendations For Using the SQL Server 2005 Best Practices Analyzer

The SQL 2005 BPA can be found here: SQL Server 2005 Best Practices Analyzer

Do:

  • Take full, differential, and/or log backups (whichever is appropriate) before running the analyzer (so you don't get warnings/information messages that clutter the report)
  • Prior to running the BPA, run DBCC CHECKDB commands on your DBs (if you don't, you'll again get cluttered reports) 
  • Install on a test server first just to get used to the output and understand everything
  • Run this on your test and development servers as well as your production systems. It's funny to me how DBAs lock down production servers and leave dev/test servers wide open.
  • Schedule recurring scans and compare to your baseline

Don't:

  • Don't run this on a production machine in the middle of the work day - run it at off-peak times. It's the Uncertainty Principle: watching changes behavior. Running the Best Practices Analyzer will have an effect on performance. In the docs, MSFT says that the BPA will use 50 to 75 percent CPU capacity while the instance of SQL Server is being scanned. I don't see nearly this much on my servers (more like 10%-15%) but since they say it, I'm concerned about running it in the middle of the day.
  • Don't run this at the same time that system maintenance jobs are being run. You don't want the BPA and your DBCC CHECKDB job to compete for system resources.
authors
scott whigham
grant moyle
chad weaver