CREATE BLOG dbo.SqlServer

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:

• The Download page featuring the July 2007 BPA
• SQLBPASetup.msi direct link

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:

1. "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
2. "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...
3. "SQL Login Password Policy Violation by login [sa]" - I don't have the password policy option checked for my sa account...
4. "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:

• The Download page featuring the July 2007 BPA
• SQLBPASetup.msi direct link

Like Reporting Services? Like SQL Server? Want to view a whole bunch of your SQL Server performance data with Reporting Services-style reports? Then you'll want to check out SQL DMVStats, a 1.0-generation open source project up on CodePlex that was just released a few days ago by Tom Davidson & Sanjay Mishra from the SQL Server Customer Advisory Best Practices Team.

I've been playing with it for about a day and I dig it. It reminds me of the SQL Server 2005 Performance Dashboard reports that I'm so fond of (link here) but there are some signficant differences. For one, the Performance Dashboard reports is more of a pull-based reporting; i.e., you must request the report and then you can view the data afterwards. With the SQL DMVStats reporting, they use a series of jobs to track system performance and a database to store this information therefore trend analysis and time-based analysis are easy to accomplish. I think of the Performance Dashboard reports as for ad-hoc reporting and I might think of the SQL DMVStats as trend analysis; sort of along the lines of the SQL Server Health and History tool (the SQLH2).

So what can you use SQL DMVStats for? Well, here's the introduction text:

Microsoft SQL Server 2005 provides Dynamic Management Views (DMVs) to expose valuable information that you can use for performance analysis. DMVstats 1.0 is an application can collect, analyze and report on SQL Server 2005 DMV performance data. DMVstats does not support Microsoft SQL Server 2000 and earlier versions.

In the docs, it is listed as a "Performance DataWarehouse"

You do have to be aware that:

1. This is first generation stuff here so, if you install it and it crashes your machine, tough cookies...
2. The default sample rate is 20 seconds therefore there is some overhead associated with running it.
   
3. The default snapshot interval is every five minutes

I could write out much more here but really I'd be just reproducing the really, really good documentation they included. Go ahead - check it out (on a test server): SQL DMVStats. Tip for first time CodePlex users: to download the file, you need to visit a particular release's page. Look at the top right corner of the page for version 1.0...

BTW, I've had a few issues here and there that I haven't worked out (such as "@object_id is not a parameter for procedure sp_GetDatabaseObjectIndexes." when I'm trying to view the index info) but I suspect it's due to my installing and playing rather than RTFM'ing!

Thanks for the cool tool :)