|
|||||||
|
|
|
|||||
|
|
|||||||
Best Practices Analyzer Tool for Microsoft SQL Server 2000
Rule Reference
This document describes the rules included in the Best Practices Analyzer 1.0.
Database Backups
Failed Backups
Master and MSDB Backup
NO_LOG Log Backups
Recovery Model Usage
Reuse of Backup File
Affinity Mask
Allow Updates Enabled
Fiber Mode
Max Server Memory
Priority Boost Enabled
Recovery Interval
Set working set size disabled
Tables without Primary Keys or Unique Constraints
User Object Naming
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 Log File Count
CREATE DATABASE with FOR LOAD
Defaults and Rules
Deprecated Builtin Functions
Non-Ansi Outer Joins
SETUSER usage
String = Expression Aliasing
Use of sp_dboption
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
AVs and Severe Errors
Error Log Size
NULL @@servername
'tempdb' Current Size
Unexpected Shutdowns
User Objects in Master
Object Prefixes
Object Suffixes
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
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
This rule checks if CPU affinity is configured in such a way that not all processors are used. In most cases the affinity mask configuration option provides best performance when set to 0 (the default).
SQL Server processor affinity is a specialized operation; it is recommended that SQL Server processor affinity be used only when necessary. In most cases, the default affinity provides the best performance.
None
affinity mask Option (Administering SQL Server (SQL Server))
This rule checks that updates to the system catalog are not enabled. The allow updates option is used to specify whether direct updates can be made to system tables.
By default, allow updates is disabled (set to 0), so users cannot update system tables through ad hoc updates. Users can update system tables using system stored procedures only. When allow updates is enabled (set to 1), any user who has appropriate permissions can update system tables directly with ad hoc updates and can create stored procedures that update system tables.
Updating fields in system tables can prevent an instance of Microsoft® SQL Server™ from running or can cause data loss. Given that system tables are critical to the operation of SQL Server, enable allow updates only in tightly controlled situations.
None
allow updates Option (Administering SQL Server (SQL Server))
This rule checks whether databases have "Auto Creation of Statistics" and "Auto Update of Statistics" enabled or not.
Though some cases may benefit from having these options off, it is generally recommended to keep them on.
This rule:
None
Setting Database Options (Creating and Maintaining Databases (SQL Server))
Statistical Information (Creating and Maintaining Databases (SQL Server))
This rule checks the NT Event log, flagging any Access Violations (AVs) or Severe Errors within the last X number of days (30 days by default). Errors with severity between 17 and 25 are reported.
Severity levels from 17 through 25 indicate software or hardware errors. The system administrator should be informed whenever problems that generate errors with severity levels 17 and higher occur.
Error Message Severity Levels (Troubleshooting (SQL Server))
This rule checks stored procedures, functions, views and triggers for use of CREATE DATABASE statement specifying the FOR LOAD clause.
FOR LOAD clause has been deprecated and will not be supported in a future release of SQL Server. RESTORE statement provides option to recreate a database.
This rule:
None
This rule checks stored procedures, functions, views and triggers for correct specification of column list on usage of FOR UPDATE clause.
When a cursor is declared with a FOR UPDATE clause, it is recommended to specify a column list. This rule flags such missing column lists. SQL Server can optimize operations based on column list information available. A future release may have tighter requirements on specification of updatable columns.
This rule:
None
This rule checks stored procedures, functions, views and triggers for correct declaration of cursor updatability based on apparent usage.
This rule reports failure when a cursor is declared without a FOR UPDATE clause and updates are done through the cursor, or when a cursor is declared with a FOR UPDATE clause and no updates occur through the cursor.
This rule:
None
This rule checks that each database (except if read-only) is backed up in the last X number of days (30 days by default).
It is recommended that databases that are not read-only be backed up often to minimize loss of critical data incase of failure.
This rule:
Backing Up and Restoring Databases (Administering SQL Server (SQL Server))
This rule checks that all databases on the server are running under the latest compatibility mode.
This rule:
None
sp_dbcmptlevel (Transact-SQL Reference (SQL Server))
This rule checks database growth configuration and available disk space. It checks and reports if any database files are within X% (default is 5%) of reaching their maximum size (if specified).
This rule:
Expanding a Database (Creating and Maintaining Databases (SQL Server))
This rule checks all databases to ensure that data and log files are not on a compressed volume and in an NTFS partition. Database or filegroups that are read-only are not scanned.
Microsoft® SQL Server™ 2000 data and transaction log files must not be placed on compressed file systems or a remote network drive, such as a shared network directory. It is recommended that data and log files are placed in an NTFS partition.
This rule:
None
Creating a Database (Creating and Maintaining Databases (SQL Server))
This rule checks that data and log files are not placed on the same logical drive. This is a general recommendation that helps separate IO for data pages from log operations.
This rule:
None
This rule checks that the database SQL Options are configured properly. SQL Options control ANSI compliance options.
The following database SQL Options should be ON:
The following should be OFF:
Database SQL Options should be configured as recommended to remove deprecated behaviors, be ANSI compliant, and be able to leverage the full feature set (indexed views and indexes on computed columns).
This rule:
None
Setting Database Options (Creating and Maintaining Databases (SQL Server))
This rule checks stored procedures, functions, views and triggers for existence of defaults and rules.
These objects have been deprecated in favor of CHECK constraints and will not be supported in a future release of SQL Server.
This rule:
None
This rule checks stored procedures, functions, views and triggers for use of builtin functions that have been deprecated from SQL Server.
A future release of SQL Server will not support these functions:
This rule:
None
This rule checks stored procedures, triggers, views and functions for use of duplicate full text predicates (CONTAINS or FREETEXT) operating on the same column in the same query.
As an example, the following query:
SELECT col1 FROM dbo.tab1 WHERE CONTAINS(ft_col, 'merry') or CONTAINS (ft_col, 'christmas')
should be rewritten as:
SELECT col1 FROM dbo.tab1 WHERE CONTAINS(ft_col, 'merry OR christmas')
The former query performs two separate scans of the full-text catalog, while the latter does a single pass. Significant performance improvements can be obtained by consolidating full-text predicates.
Note: This rule performs only syntactic analysis. For this reason, it may report false positives by considering two full-text predicates operating on the same column, when the actual case may be that the predicates operate on two different columns from different tables that happen to have the same name.
This rule:
None
Full-Text Search Recommendations
This rule checks if the error log size is too big i.e. greater than 500 MB. The error log in SQL Server 2000 provides complete information about events in SQL Server. A new error log is created each time an instance of SQL Server is started and can sometimes get very large. In such cases, it is recommended that the sp_cycle_errorlog system stored procedure be used to cycle the error log files without having to restart the instance of SQL Server.
None
sp_cycle_errorlog (Transact-SQL Reference (SQL Server))
This rule checks that creation of indexes is done with explicit specification of index type.
Syntax for creating an index is as follows:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name]
ON { tableview} ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]
< index_option > :: =
{ PAD_INDEX |
FILLFACTOR = fillfactor |
IGNORE_DUP_KEY |
DROP_EXISTING |
STATISTICS_NORECOMPUTE |
SORT_IN_TEMPDB
}
It is recommended that the CLUSTERED or NONCLUSTERED specifications be used (as appropriate) when creating an index.
This rule:
None
CREATE INDEX (Transact-SQL Reference (SQL Server))
This rule checks the NT Event Log for failed backup operations within the last X number of days (30 days by default).
Failed backups should be investigated. There may be increased risk of losing critical data incase of failure if backup information is not available at restore time.
Backing Up and Restoring Databases (Administering SQL Server (SQL Server))
This rule checks if Lightweight Pooling is configured in such a way that fibers are turned on. In most cases the lightweight pooling configuration option provides best performance when set to 0 (default).
SQL Server lightweight pooling is a specialized operation; it is recommended that it is used only when strictly necessary. In most cases default value (0) provides the best results.
None
Lightweight Pooling Option (Administering SQL Server (SQL Server))
Using the lightweight pooling Option
This rule checks that the server is optimized for background applications to facilitate performance of full-text services.
Processing time for the full-text search service can be improved by setting up the server to run foreground and background applications with equal priority. SQL Server, which runs as a background application, then runs at equal priority to other applications running in the foreground.
This rule is applicable only for servers with full-text enabled for one or more databases.
None
Configuring Server Tasking (Optimizing Database Performance (SQL Server))
Full-Text Search Recommendations (Troubleshooting (SQL Server))
This rule checks that the BLOB extension column should not be of type char or nchar for tables with full-text enabled on a BLOB column (i.e. image data type). It is recommended that the type for the BLOB extension column be varchar or nvarchar to facilitate full-text search.
The BLOB extension column is the column that contains the document type of the BLOB column and is specified using sp_fulltext_column system stored procedure.
This rule:
None
Building Search Applications for the Web Using Microsoft SQL Server 2000 Full-Text Search
sp_fulltext_column (Transact-SQL Reference (SQL Server))
This rule checks if the server instance has more than 20 catalogs and warns user that there are too many catalogs. For optimal full-text performance it is recommended that the catalog count be kept low. A maximum of 256 full-text catalogs can be created on each server.
This rule:
None
Full-Text Indexes (Creating and Maintaining Databases (SQL Server))
Full-Text Search Recommendations (Troubleshooting (SQL Server))
This rule checks that full-text catalogs are not placed on the same drive as the data. As a best practice Full-Text catalogs should be created on their own physical drive (or drives), if possible. Given the process of building a Full-Text index is relatively I/O intensive (on a high level, it consists of reading data from SQL Server, and then writing the index to the file system), it is recommended to avoid letting the I/O subsystem become a bottleneck.
This rule:
None
Building Search Applications for the Web Using Microsoft SQL Server 2000 Full-Text Search
This rule checks that change tracking is enabled for all tables that have full-text enabled. Change tracking population maintains a record of the rows that have been modified in a system table, and propagates the changes to the full-text index.
This rule:
None
Full-Text Indexing Support (SQL Server Architecture (SQL Server))
Maintaining Full-Text Indexes (Creating and Maintaining Databases (SQL Server))
This rule checks that the property store files (*.ps1 and *.ps2) for MS Search are not larger than 256MB. Performance can be improved by increasing the maximum size of the property store in cases where files have exceeded the configured limit.
This rule:
None
This rule checks whether index fragmentation is too high (over a specified percentage, default is 20%) for indexes with more than 10,000 pages (default value).
As Microsoft SQL Server 2000 maintains indexes to reflect updates to their underlying tables, these indexes can become fragmented. Depending on workload characteristics, this fragmentation can adversely affect workload performance. Depending on deployment environment, defragmentation of indexes can benefit workload performance.
This rule:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
This rule checks objects for use of INSERT statements without explicit specification of target column list.
When inserting into a table or view, it is recommended that the target column_list be explicitly specified. This results in more maintainable code.
This rule:
None
INSERT (Transact-SQL Reference (SQL Server))
This rule checks that user tables (xtype='U') named dbo.sysproperties and dbo.sysfulltextnotify do not exist in specified databases.
SQL Server 2005 has changed the way that these system tables are used. In order to avoid data loss, upgrade of a database containing either of these invalid user tables will be aborted. System tables (xtype='S') of the same name will not cause any problems. If a database contains either of these user tables, they should be renamed or removed prior to upgrading.
This rule:
None
This rule checks to see whether growth policy for log file is adequate. An adequate growth policy will reduce the number of log file growth operations, minimizing the number of virtual log files created. It is recommended that log file growth be set as a percentage or according to the following limits:
When log file is configured for fixed growth, it should be set to grow by at least 5% of the current size of the file or a 64MB increment.
This rule:
None
Physical Database Files and Filegroups (SQL Server Architecture (SQL Server))
This rule checks that the Master and MSDB databases have been backed up in the last X number of days (30 days by default). The Master and MSDB databases contain critical system information and should be backed up often.
System Databases and Data (SQL Server Architecture (SQL Server))
Backing Up and Restoring Databases (Administering SQL Server (SQL Server))
This rule checks that the max server memory option has been set to default (2147483647 MB) unless:
Note: There may still be other cases where setting max server memory may be required, such as when there are multiple instances of SQL Server or the server is hosting other applications.
None
Server Memory Options (Administering SQL Server (SQL Server))
Managing AWE Memory (Administering SQL Server (SQL Server))
Effects of min and max server memory (SQL Server Architecture (SQL Server))
This rule checks that the MSSearch service is running as Local System.
This rule is applicable only for servers with full-text enabled for one or more databases.
None
This rule checks for triggers that may not be firing due to 'nested triggers' configuration option setting.
When 'nested triggers' configuration option is set to 0, any AFTER trigger defined on tables/views updated inside an INSTEAD OF trigger is not fired. This rule:
Depending on application design, the condition identified by this rule may lead to incorrect application semantics.
This rule:
None
This rule checks the NT Event Log for executions of backup with NO_LOG within the last X number of days (30 days by default).
The NO_LOG and TRUNCATE_ONLY clauses of BACKUP remove the inactive part of the log without making a backup copy of it and truncate the log.
BACKUP operations with NO_LOG are discouraged.
Truncating the Transaction Log (SQL Server Architecture (SQL Server))
This rule scans triggers to ensure they SET NOCOUNT to ON at the beginning.
SQL Server sends 'done' token information for each statement that completes execution. Extra tokens may cause unexpected results in application code executing operations that caused the trigger to fire. It is general good design to have NOCOUNT set to ON within a trigger.
This rule:
None
This rule checks for the use of non-ANSI outer joins. (*= and =* syntax)
Joins are specified in the FROM clause in DELETE, SELECT, and UPDATE statements. Syntax is as follows:
[ FROM { < table_source > } [ ,...n ] ]
< table_source > ::=
table_name [ [ AS ] table_alias
] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
| user_defined_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >
< joined_table > ::=
< table_source > < join_type >
< table_source > ON < search_condition >
| < table_source
> CROSS JOIN < table_source >
| [ ( ] < joined_table > [ ) ]
< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ]
[ < join_hint > ]
JOIN
It is recommended that outer joins use the ANSI specified syntax, for example:
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
This rule:
None
FROM (Transact-SQL Reference (SQL Server))
This rule checks that @@SERVERNAME does not return NULL. In some cases @@SERVERNAME becomes NULL and can cause problems.
None
@@SERVERNAME (Transact-SQL Reference (SQL Server))
This rule scans stored procedures, views, functions and triggers to flag use of equality and inequality comparisons involving a NULL constant. These comparisons are undefined when ANSI_NULLS option is set to ON.
It is recommended to set ANSI_NULLS to ON and use the IS keyword to compare against NULL constants.
This rule:
None
Null Comparison Search Conditions
This rule checks that all objects of type X are prefixed with string Y on a specified list of databases. The rule reports all objects of type X in the specified databases that do not have the prefix Y in their names.
This rule is a generic rule to create best practices applicable to individual production environments and enforce custom guidelines.
This rule:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint
V = View
X = Extended stored procedure.
None
This rule checks that all objects of type X are suffixed with string Y on a specified list of databases. The rule reports all objects of type X in the specified databases that do not have the suffix Y in their names.
This rule is a generic rule for the user to create best practices applicable to their specific production environment and guidelines.
This rule:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint
V = View
X = Extended stored procedure.
None
This rule checks for the use of DBCC commands that have been deprecated from SQL Server.
A future release of SQL Server will not support these DBCC commands:
This rule:
None
This rule checks for the use of sp_configure commands that have been deprecated from SQL Server.
A future release of SQL Server will not support these sp_configure commands:
This rule:
None
This rule checks for the use of stored procedures that are obsolete in SQL Server 2005.
A future release of SQL Server will not support these stored procedures:
This rule:
None
This rule checks for the use of system tables that have been deprecated from SQL Server.
A future release of SQL Server will not support these system tables:
This rule:
None
This rule checks stored procedures, functions, views and triggers for use of ORDER BY clause specifying constants as sort columns. Integers are not reported. Support for this syntax has been deprecated and will not be supported in SQL Server 2005 under 90 compatibility level.
As an example, the following syntax is not recommended:
SELECT au_id FROM dbo.authors ORDER BY 'a', null
This rule:
None
This rule checks that the priority boost option is not enabled.
The priority boost option is used to specify whether Microsoft® SQL Server™ should run at a higher scheduling priority than other processes on the same computer.
It is recommended that priority boost option is not enabled. priority boost should be used only on a computer dedicated to SQL Server, and with a symmetric multiprocessor (SMP) configuration.
None
priority boost Option (Administering SQL Server (SQL Server))
This rule checks to see if recovery interval is set too high. Too high a recovery interval can lead to long server startup times. Recovery interval of 0 or 1 is acceptable.
The recovery interval option is used to set the maximum number of minutes per database that Microsoft® SQL Server™ needs to recover databases. This configuration option sets an upper limit on the time it should take to recover each database. The default is 0, indicating automatic configuration by SQL Server.
It is recommended that recovery interval is set to 0 (self-configuring) unless frequent checkpoints are noticeably impairing performance. In such a case, increase the value of recovery interval in small increments.
None
recovery interval Option (Administering SQL Server (SQL Server))
This rule checks each database and warns if recovery mode is full or bulk-logged and the transaction log has not been backed up within the last X number of days (default is 30).
It is recommended that the transaction log for databases using full or bulk-logged recovery models should be backed up to minimize critical data loss incase of any system or server failures.
This rule:
Backing Up and Restoring Databases (Administering SQL Server (SQL Server))
Using Recovery Models (Administering SQL Server (SQL Server))
Selecting a Recovery Model (Administering SQL Server (SQL Server))
This rule scans triggers to ensure they do not send data back to the caller.
Applications that modify tables or views with triggers do not necessarily expect results to be returned as part of the modification operation. For this reason it is not recommended to return results from within triggers. This rule flags as not recommended the use of the following statements within a trigger:
This rule:
None
This rule checks whether the last three backup operations on a database have been performed to the same file specifying that existing media should be overwritten (INIT). If so, warns that there may be potential risk of data loss if backup files are not being copied between backups.
This rule:
None
Backing Up and Restoring Databases (Administering SQL Server (SQL Server))
This rule checks stored procedures and triggers for appropriate scoping of transactions. It is generally recommended to have transactions start and end within the same T-SQL frame.
As an example, if a stored procedure does some work and then calls COMMIT TRAN, callers of such procedure need to be intimate to the transaction semantics of the procedure. A caller unaware of the contract may get spurious error or incorrect isolation semantics.
This rule:
None
This rule checks stored procedures, functions, views and triggers for use of '*' in column lists of SELECT statements. Though use of '*' is convenient, it may lead to less maintainable applications. Changes to table or view definitions may cause errors or changes in performance.
As an example, the following syntax is not recommended:
SELECT * FROM dbo.authors
It is recommended to have an explicit list whenever the column to retrieve are known to the application.
SELECT a.au_id, a.au_lname FROM dbo.authors a
This rule:
Note: BPA does not detect usage of '*' inside inline table-valued functions; however it is still a discouraged use of '*'.
None
This rule checks stored procedures and triggers for use of SET statements setting the following set options:
The following options are recommended to be ON:
The following is recommended to be OFF:
A SET statement that sets one of the above options to a not recommended value is flagged by this rule.
This rule:
None
This rule checks that the set working set size option is not enabled if SQL Server is allowed to use memory dynamically.
The set working set size option is used to reserve physical memory space for Microsoft® SQL Server™ that is equal to the server memory setting. The server memory setting is configured automatically by SQL Server based on workload and available resources. It will vary dynamically between min server memory and max server memory. Setting set working set size prevents the operating system from swapping out SQL Server pages though they could be used by another process when SQL Server is idle.
It is recommended that the set working set size is not set if SQL Server is configured to use memory dynamically.
None
set working set size Option (Administering SQL Server (SQL Server))
Server Memory Options (Administering SQL Server (SQL Server))
This rule checks stored procedures, functions, views and triggers for use of SETUSER statement. In general, SETUSER is not recommended. Support for this statement has been deprecated and will not be supported in a future release of SQL Server.
This rule:
None
This rule checks stored procedures, functions, views and triggers for use of column aliasing where the name of the expression uses a string value. It is recommended to use quoted identifiers instead. String aliases will not be supported in a future release of SQL Server.
As an example, the following syntax is not recommended:
SELECT 'alias_for_col'=au_id+au_id FROM dbo.authors
Recommended alternatives are:
SELECT au_id+au_id as "alias_for_col" FROM dbo.authors
SELECT au_id+au_id as alias_for_col FROM dbo.authors
SELECT au_id+au_id as [alias_for_col] FROM dbo.authors
This rule:
None
This rule checks that a schema called 'sys' has not been defined.
SQL Server 2005 has rearchitected its system catalog to be better organized and to expose metadata information in a consistent way, without forcing users to see and understand implementation details of the catalog itself. As part of this effort, all system objects have been moved to a newly reserved 'sys' schema. As such, existing databases with 'sys' schema defined in them will not be able to upgrade to SQL Server 2005 without first renaming such schema.
This rule:
None
This rule scans databases to ensure that all tables have a primary key defined or have a column with a Unique Constraint defined. Tables that don’t have a Primary Key defined or a column with a unique constraint defined fail the scan. Only tables that fail the scan are reported.
This rule:
None
PRIMARY KEY Constraints (Creating and Maintaining Databases (SQL Server))
Creating and Modifying PRIMARY KEY Constraints (Creating and Maintaining Databases (SQL Server))
This rule checks stored procedures and triggers for usages of temporary tables that may be replaced by use of table variables.
This rule will report procedures where a temporary table is created, no CREATE INDEX is issued on it, and it is dropped, all in the same procedure. These cases may consider using table variables instead and potentially observe fewer recompilations.
Note that if large data volumes will be inserted in the temporary table it may still be preferred to use temporary tables due to parallel execution restrictions and statistics maintenance.
This rule:
None
Frequently Asked Questions - SQL Server 2000 - Table Variables
This rule checks that 'tempdb' is not currently significantly (10x times by default) larger than the set default size.
If 'tempdb' is usually significantly larger than the default size, then the default size should be increased so that 'tempdb' doesn't have to grow at runtime every time the server is restarted.
This rule checks that each full-text indexed table should have a timestamp column to allow for incremental population.
Incremental population only adjusts index entries for rows added, deleted, or modified after the last population. This feature requires that the indexed table have a column of the timestamp data type. Requests for incremental populations on tables without timestamp columns result in a full population operation.
This rule:
None
Full-Text Indexing Support (SQL Server Architecture (SQL Server))
This rule checks stored procedures, functions, views and triggers for usages of TOP in queries without an ORDER BY clause.
This rule will report use of queries where the TOP clause is specified without an ORDER BY clause in the query. It is generally recommended to specify sort criteria when using TOP clause. Otherwise, the results produced will be plan dependent and may lead to undesired behavior.
This rule:
None
Limiting Result Sets Using TOP and PERCENT
This rule checks the NT Event Log and flags any unexpected shutdowns that might have occurred within the last X number of days (30 days by default).
A possible unexpected shutdown is reported if a server start, indicated by the following message:
17162: SQL Server is starting at priority class...
is not preceded by a valid server stop message:
17148: SQL Server is terminating due to 'stop' request from Service Control Manager.
17147: SQL Server terminating because of system shutdown.
This rule checks stored procedures, functions, views and triggers for use of schema qualified names when referencing tables and views.
Unless specified otherwise, all Transact-SQL references to the name of a database object can be a four-part name in the form:
[
server_name.[database_name].[schema_name].
| database_name.[schema_name].
| schema_name.
]
]
When referencing a specific object, it is not required to specify the server, database, and owner (schema) for SQL Server to identify the object. However, it is recommended that schema name be specified to identify a table or view inside a stored procedure, function, view or trigger.
When SQL Server looks up a table/view without a schema qualification, it first searches the default schema and then the 'dbo' schema. The default schema corresponds to the current user for ad-hoc batches, and corresponds to the schema of a stored procedure when inside one. In either case, SQL Server incurs an additional runtime cost to verify schema binding of unqualified objects. Applications are more maintainable and may observe a slight performance improvement if object references are schema qualified.
This rule:
This rule does not check for
None
Using Identifiers as Object Names (Accessing and Changing Relational Data (SQL Server))
Transact-SQL Syntax Conventions (Transact-SQL Reference (SQL Server))
This rule checks stored procedures, functions, views and triggers for use of sp_dboption procedure.
This stored procedure has been deprecated in favor of ALTER DATABASE statement and will not be supported in a future release of SQL Server.
This rule:
None
sp_dboption (Transact-SQL Reference (SQL Server))
This rule checks for user objects with any of the following prefixes: sp_, xp_, or fn_. Though this practice is supported, it is recommended that the prefixes not be used to avoid name clashes with Microsoft shipped objects:
This rule:
None
System Databases and Data (SQL Server Architecture (SQL Server))
This rule checks to see if there are any user objects in master database. The master database is reserved for SQL Server internal use and it is recommended that user objects not be created in master database.
This rule does not report extended stored procedures or startup procedures, given that master is the only database where they can be created.
None
This rule checks to see if there are too many virtual log files for each database on the server.
Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. Too many virtual log files can slow down backup/restore and database startup. It is recommended that the virtual log file count should be less than 200.
This rule:
None
Virtual Log Files (Creating and Maintaining Databases (SQL Server))
Shrinking the Transaction Log (SQL Server Architecture (SQL Server))
This rule checks stored procedures, triggers, views and functions for use of table hints without the WITH keyword.
It is recommended that hints be specified using the WITH keyword.
SQL Server 2005 will not support the use of more than one hints in a T-SQL statement unless the WITH keyword is specified.
As an example, the following syntax is not supported in SQL Server 2005:
SELECT au_id FROM dbo.authors (UPDLOCK, PAGLOCK)
The recommended approach is to use:
SELECT au_id FROM dbo.authors WITH (UPDLOCK, PAGLOCK)
Queries specifying a single hint will work in SQL Server 2005, though it is still recommended to use the WITH keyword.
This rule:
None
Hints (Accessing and Changing Relational Data (SQL Server))
Copyright © 2004 Microsoft Corporation. All rights reserved.