Thursday, July 05, 2007
SQL Server 2005 BPA (Best Practices Analyzer) Is Here
I have to say - for all the bugs and problems running SQL 2005 that I've experienced (and that have frustrated me and you), Microsoft has just been amazing about putting these helpful tools together for the system and making them available for free.I remember back 6-7 years ago when we had no tools or any special downloads and we had to write the tools ourselves (which led to lucrative consulting projects though) but today, I can save the time and just help the client with the built-in tools from MSFT.
The SQL Server 2005 Best Practices Analyzer (July 2007 edition) is the SQL 2005 version of the BPA (Best Practices Analyzer). Microsoft, IIRC, originally started using the BPAs with SQL Server back in SQL 2000 and has since expanded them to the ASP.NET BPA, the Exchange BPA and more (click here for a full list).
Links:
Thoughts on Running the SQL 2005 BPA:
This isn't just an extension of the old SQL 2000 BPA; this new implementation takes full advantage of SQL Server 2005's features by scanning for (and making suggestions for correcting/amending) SSIS implementations and more. On my machine (which has SQL 2005, SSIS and SSRS), it made no suggestions for improving SSRS but it did make a few SSIS-based suggestions. Check out the screenshot below for an example of the report listing:
There are a few interesting bits:
- "Unexpected System Failures on Host" - I don't really remember this happening but this is MSFT's way of telling me that my server has had an expected shutdown. If you read the "Tell me more about this setting", it says I should move my SQL Server to a more stable system lol
- "Authentication Mode Violation" - This is a fear-mongering way of saying that MSFT thinks I should be running my SQL Server in Windows Authentication mode instead of Mixed Mode...
- "SQL Login Password Policy Violation by login [sa]" - I don't have the password policy option checked for my sa account...
- "Sysdtslog90 Table in [msdb]" - See? This is what I mean when I say that the new BPA is integrated tightly with SSIS. If you read the docs, MSFT says, "Integration Services package logging can generate a large volume of verbose log entries. You may experience decreased performance if you log to a system database such as master or msdb. A good practice is to create a separate database for Integration Services logging."
One thing: there is no legend regarding the icons on the Report page. Does a blue circle with an exclamation generally imply danger? No - but it's the way the BPA shows you a recommended Best Practice. The yellow triangle with the exclamation is pretty standard - it's a Warning - so you will want to review anything that comes back with a yellow exclamation. The BPA uses Exception-Based reporting so you won't see a nice green circle with a checkmark if you've done something right; you only get notified if you've screwed up. Here's the legend of icons in the report as best I can tell (based on the icons I saw in my reports):
- Best Practices: Blue circle with exclamation
- Warnings: Yellow triangle with exclamation
I looked in the help file for a full listing of the icons but saw nothing. There's a page called "Types of Output Information" that lists all of the options but no icons. Here's the list:
- Best Practice
- Warning
- Error
- Non-Default Configuration
- Recent Change
- Baseline Mismatch (requires a baseline scan to be run first)
- Information
Also included in the download is a .chm (a help file) that is packed with great suggestions, explanations of the settings, and good insider info about SQL Server 2005.
Recommendations For Using the SQL Server 2005 Best Practices Analyzer:
Do:
- Take recent backups and run DBCC CHECKDB on all databases before running the BPA otherwise you get cluttered reports
- If you aren't running CHECKDB periodically, it will tell you. To quickly set this up, just use either a Database Maintenance plan (the Check Integrity Task) or SSIS and schedule once every week or two weeks (or whatever)
- Run this 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. You'd be surprised at how often DBAs focus on the production server and don't secure/maintain the dev servers nearly as much.
- Schedule periodic BPA 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. Be aware that monitoring/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 didn'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.
System Requirements:
- Modern day OS
- SQL Server 2005 Client Tools must be installed
- They list that the .NET Framework is a requirement but they were a requirement to install the SQL 2005 tools so it's a bit redundant...
Links:
