I personally believe that no matter how much you are knowledgeable in the technical arena, if you are not adopting best practices you will be lost in one day. We as IT professionals best practices have become order of the day. To achieve this, there are many tools around us which will reduce the inconsistency. Today I will discuss about the practices tool for SQL Server professionals. That tool is SQL Best Practices Analyzer.
Introduction
Shown above is the best practice Analyzer icon and the About information dialog, which prevents me from having to write 5 lines of specs.
What it Does ?
In simple terms what it does is give you the warnings for the best practices that can be adopted if you are not following them. It has divided the best practices into 10 categories, shown below in the table. For each category there are the specific best practices or rules which you can select to analyze.
| Group |
Best Practices (Rules) |
| Backup & Recovery |
Database Backups Failed Backup Events
Master and MSDB Backup
NO_LOG log backups
Recovery Model Usage
Reuse of back file |
| Configuration options |
Affinity Mask Allow Updates Enabled
Fiber Mode
Max Server Memory
Priority Boost Enabled
Recovery Interval
Set Working set size disabled |
| Database Design |
Tables without Primary Keys or Unique Constraints User
Object Naming |
| Database Administration |
Auto Creation of Statistics Database Compatibility Level
Database Disk Space
Database File Compression
Database File Placement
Database SQL Options
Index Fragmentation
Log file Growth
Virtual File Growth |
| Deprecation |
CREATE DATABASE with FOR LOAD Defaults and Rules
Deprecated Builtin Functions
Non-Ansi Outer Joins
SETUSER usage
String = Expression Aliasing
Use of sp_dboption |
| Full-Text |
Duplicate Full-Text predicates Full-Text Change Tracking
Enabled
Full-Text Background Services Optimized
Full Text BLOB Extension Type
Full Text Catalog Count
Full Text Catalog Placement
Full Text Property Store Size
MSSearch Service Account
Timestamp Column for Full-Text |
| General Administration |
AVs and Severe Errors Error Log Size
NULL @@servername
'tempdb' Current Size
Unexpected Shutdowns
User Objects in Master |
| Generic |
Object Prefixes Object Suffixes |
| T-SQL |
Cursor FOR UPDATE column list Cursor Usage
Explicit Index Creation
INSERT Column List
Nested Triggers Configuration
NOCOUNT Option in Triggers
NULL Comparisons
Results in Triggers
Scoping of Transactions
SELECT *
SET Options
Temp Table Usage
TOP without ORDER BY
Use of Schema Qualified Tables/Views |
| SQL Server 2005 readiness |
Invalid User Tables Obsolete DBCC Commands
Obsolete sp_configure Commands
Obsolete Stored Procedures
Obsolete System Tables
ORDER BY with constants
'Sys' User Schema
WITH Hint Specification |
Installation
You can download this free tool from
Microsoft. It will create a database called sqlbpa in your SQL Server while installing the tool. This database will be used to save the configurations of your best practices.
Creating a Best Practice Group
Above is the screen in which you create a best practices (BP) group. You can create BP for the all the servers which are registered on your PC. For one BP group, many rules can be selected from one or more categories. Just clicking the rule will take you to the description relevant to that rule.
Executing the Group
You can execute one or more groups at once. After creating BP groups you will be taken to the list of BP groups. You have an option of filtering them according to your requirements. Then select the groups that you want to execute by double clicking on them.
After selecting the BP groups Scan SQL Server Instances will allow you to execute the groups by means of a wizard and will give you an output something like this.
I don't think you will need an explanation after this. It is so simple.
Conclusion
Definitely this is a simple and handy tool to have in your toolkit. However as always the case, use this tool as a slave but not as a master. Any warnings should be taken into consideration before jumping into change them.
I didn't go into deep to discuss each an every rule which is available in the tool. Nevertheless, If you need me to discuss some more about some specific rule drop me an email to dineshasanka@ieee.org .
