Review of SQL Server 2005 Best Practices Analyzer

Microsoft released an updated version of the SQL Server 2005 Best Practices Analyzer (BPA) on January 18, 2008. I installed in on my workstation, and tried scanning a few of the servers that I manage.

One thing I discovered is that if you do a detailed scan, it takes quite a while to complete (probably since it is scanning Windows Event Logs and SQL Error Logs). The results seem pretty heavily skewed towards security issues as opposed to performance, which is probably a good thing. One issue I noted was that the BPA claimed that I did not have the correct number of TempDB data files on one of my servers (the rule is one TempDB data file per CPU to reduce allocation contention). This was on a quad processor, dual-core machine, which means that I should have eight TempDB data files (which I do). Now I am curious as to whether the BPA just counts sockets or cores.

All in all, it is a useful tool that is easy to use. I still wish it would do static code analysis of T-SQL code like the old SQL Server 2000 BPA did.


Technorati Tags:
This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s