SQL Server 2000 Best Practices Analyzer Tool 1.0 by Microsoft

reviewed on 9/10/04 by Mark Wehmhoefer

Bottom line

This free SQL Server 2000 database management tool from Microsoft should be run on a regular basis on your development and production databases to verify the implementation of common Best Practices (as defined by Microsoft). Nothing more embarrassing than having database problems reported by the clients and your internal staff evaluators.

10 minute product review

Setup and installation was as simple as running an exe and accepting the defaults.

I was impressed by the quick execution time to evaluate a database even when 400 plus tables were checked.

Very lengthy list of database recommendations can be generated.

Sample recommendations found on an older development database

Cursor Usage T-SQL Rule Information - One or more objects have inappropriate cursor updatability information. SQL Server can improve performance given the appropriate updatability information. A future release of SQL Server may have stricter requirements on this information.

Database Backups Backup and Recovery Rule Information - One or more databases were found without a recent backup.

Database SQL Options Database Administration Rule Information - Database SQL Options should be configured as recommended to remove deprecated behaviors, be ANSI compliant, and be able to leverage the full feature set.

Database File Placement Database Administration Rule Information - It is recommended that data and log files be kept on separate drives.

NULL comparisons T-SQL Rule Information - One or more objects has an expression involving equality or inequality comparison against NULL value! It is recommended to use use IS or NOT IS as per the ANSI standard when testing for NULL values.

String = Expression Aliasing Deprecation Rule Information - One or more objects is using a string value as alias for an expressions! It is recommended to use quoted identifiers instead. A future version of SQL Server will not support this type of aliasing.

INSERT Column List T-SQL Rule Information - One or more object contains an INSERT statement without explicit specification of target column list.! It is generally recommended to explicitly specify the column list target of the INSERT operation.

Non-Ansi Outer Joins Deprecation Rule Information - One or more object is specifying outer joins by using non-ansi syntax.! It is generally recommended to use ANSI style JOIN clause syntax. A future version of SQL Server will not support *= or =* syntax.

Tables without PKs or UQs Database Design Rule Information - One or more databases failed the scan. It is a recommended best practice that all tables have a Primary Key or at least a Unique Constraint on a column defined.

Deprecated Builtin Functions Deprecation Rule Information - One or more objects has calls to deprecated builtin functions! A future release of SQL Server will not support this builtin function.

Use of Schema Qualified Tables/Views T-SQL Rule Information - One or more objects are referencing tables/views without specifying a schema! Performance and predictability of the application may be improved by specifying schema names.

Set working set size disabled Configuration Options Rule Information - 'set working set size' configuration is enabled and SQL Server is configured for dynamic memory management. 'set working set size' option should not be enabled if SQL Server is allowed to use memory dynamically.

SET Options T-SQL Rule Information - One or more objects is setting SET options to values that are not recommended! Some of the non recommended behaviors will not be supported in a future version of SQL Server.

SELECT * T-SQL Rule Information - One or more objects is using SELECT * syntax! It is recommended to provide explicit column lists rather than relying on expansion of '*'. A future version of SQL Server will disallow usage of '*' in functions and views.

Temp Table Usage T-SQL Rule Information - One or more objects have temp table usage that may benefit from table variable replacement! Use of table variables instead of temp tables can reduce number of recompilations.

TOP without ORDER BY T-SQL Rule Information - One or more objects has statements using TOP without specifying an ORDER BY clause! It is generally recommended to use specify ORDER BY clause when using TOP. Otherwise, results will be plan dependent.

NOCOUNT Option in Triggers T-SQL Rule Information - One or more triggers is either setting NOCOUNT to OFF or missing NOCOUNT setting! It is generally recommended to explicitly set NOCOUNT option to ON at the beginning of a trigger.

Results in Triggers T-SQL Rule Information - One or more triggers is sending information back to the caller.! It is generally recommended that triggers not send information to the caller. SELECT (without INTO and assignment), FETCH (without assignment) and PRINT statements send results to the client.

User Objects in Master General Administration Rule Information - One or more user objects were found in 'master' database. 'master' database is reserved for SQL Server internal use and it is recommended that user objects not be created in it.

User Object Naming Database Design Rule Information - Though the practice is supported, to avoid name clashes with system objects it is recommended that user object not have 'sp_', 'xp_' and 'fn_' as name prefixes.

Improvements I would like to see

Report detail could list specific occurrences of the variances found during the evaluation.

Report detail could count the number of the variances found during the evaluation.

Free download

You can download a fully-functional, free copy from Microsoft's website.

----------------------------------------

Mark Wehmhoefer is a full-time Data Base Administrator with a Fortune 50 healthcare company. He is the webmaster for several websites including: SQL Server Directory, MCDBA Directory and Front Page Help.

Mark has earned these Microsoft certifications: MCDBA, MCSE and MCSE + I. Mark has worked with Microsoft SQL Server since version 4.2 and has worked in various capacities in Information Technologies since 1974.