Microsoft released its ‘Best Practices Analyser Tool’ end of May. Though this tool has been in beta since late last year, it has entered the market softly, without any hype or fuss. This is certainly not the ‘Microsoft way’ – and it makes me wonder why, because this is a great little tool and its FREE!!!. You can download it from Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0

For those of you who didn’t know about it – this is a graphical tool which scans through your database and verifies it against a set of pre-determined rules. For the most part the rules relate to administration functions and its going to be invaluable aid to a DBA’s arsenal.

Just to see what the BPA can do, I have run this against a few production databases and a few development databases I have access to and the results were definitely worth the time.

I discovered for instance, that a crucial table at a client site did not have either a primary key or a unique key constraint on it. I’m pretty sure this table at some point did have a unique constraint, but somewhere over the months and many schema changes, somehow it got dropped – and no one noticed because everyone was so sure it existed. This is just the sort of thing the BPA does so well – nothing earth shattering in terms of knowledge – we all know tables should have primary keys, but the BPA provides us a way to monitor databases effortlessly.

Some database maintenance checks are nice to have – for instance the Index fragmentation, I currently have a stored procedure on a monthly schedule to do this – BPA I guess just makes it easier. Log file growth, virtual log file count – nothing we can’t do ourselves but this just makes it so easy to monitor my databases.

Many of the rules in BPA pertain to Backup and Recovery options, this can be an eye opener sometimes. There are always backup plans in place for production databases – but I was embarrassed to find out when our development databases were last backed up.

I also appreciate the SQL Server 2005 Readiness and depreciation check – it’s nice to know much ahead of time what wont work.

As always, I have a wish list (don’t we all).

• I would love to be able to create my own rules.
• The ability to setup and schedule the scripts to run at pre-determined times and frequencies  would be nice. I have mixed feelings about this however – The best practice analyzer does have a command line interface, and with a bit of work, I can script this out for myself without having Microsoft have a cookie cutter solution for me. I’m probably in the minority when I say this, but I think it’s important that software engineers and DBA’s  have the capability to code for themselves, without always looking to Microsoft for a point and click interface.
• Can we have a ‘SQL Standard Edition’ compatibility check? So many of us develop on SQL developer edition and deploy to SQL standard edition. I would love to know, what is not supported in standard edition.
• I would love to be run this as a background service, keeping a track of database objects being accessed. I currently need to run a profiler trace for this. This not only has a considerable performance impact but profiler traces are not fun to read. It would be helpful to know which objects are now archaic
• Again if it can run as a background service, I want to know what indexes / statistics the optimizer is currently using. I want to know if I have unused indexes, ones which were scripted for a different time and now with application changes are not being used.

All things considered – I love the best practice analyzer – it’s a great little tool, and has already pointed out a number of little problems which if I had to do manually would take me a long long time.

by Poonam Lall