Performance Rules

Introduction

This analysis group contains performance related analysis rules.

Performance Rules

Related Topics

SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL

SA0004 : Variable assigned but value never used

SA0007 : Pattern starting with ‘%’ in LIKE predicate

SA0009 : Consider using a table variable instead temporary table

SA0013 : Avoid returning results in triggers

SA0016 : Use of very small variable length type (size 1 or 2)

SA0017 : SET NOCOUNT ON option in stored procedures and triggers

SA0023 : Avoid using not equal operator (<>,!=) in the WHERE clause

SA0024 : Local cursor not closed

SA0025 : Local cursor not explicitly deallocated

SA0026 : Local cursor variable not explicitly deallocated

SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause

SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan

SA0030 : Output parameter never assigned

SA0032 : Avoid using NOT IN predicate in the WHERE clause

SA0033 : Do not use the GROUP BY clause without an aggregate function

SA0040 : Consider moving the column reference to one side of the comparison operator in order to use the column index

SA0044 : Consider creating indexes on all columns included in foreign keys

SA0045 : Consider updating statistics as they appear outdated and may mislead the query optimizer

SA0046 : Consider creating statistics on all composite index columns

SA0047 : Consider indexing the column as it is used in a WHERE clause or JOIN condition

SA0048 : Table does not have a primary key or unique key

SA0048B : The table is created without a a primary key

SA0049 : Table does not have a clustered index

SA0049B : The table is created without a clustered index

SA0054 : Avoid modification of parameters in a stored procedure prior to use in a query

SA0055 : Consider indexing the columns referenced by IN predicates in order to avoid table scans

EX0010 : Identify missing indexes using dynamic management views information

EX0011 : Identify inefficient indexes using dynamic management views information

EX0013 : Identify fragmented indexes that need rebuilding or re-indexing

EX0018 : Analyze execution plan and check for high cost operations

SA0077 : Avoid executing dynamic code using EXECUTE statement

SA0123 : Consider replacing the OUTER JOIN with EXISTS

SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause

SA0128 : Avoid using correlated subqueries. Consider using JOIN instead

SA0134 : Do not interleave DML with DDL statements. Group DDL statements at the beginning of procedures followed by DML statements

SA0135 : Found filtering columns wrapped inside User-Defined Function call

SA0149 : Consider using RECOMPILE query hint instead of WITH RECOMPILE option

SA0154 : Constraint is not trusted

SA0154B : Constraint not checked and left not trusted

SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index

SA0195 : Duplicate statistics must be removed

SA0237 : Ordering of the result set before inserting it into a table is pointless

SA0238 : The user-defined function appearing in the query filter can cause performance problems

SA0239 : Setting the FORCEPLAN option to ON is not recommended

SA0242 : COUNT aggregate function used instead of EXISTS

SA0245 : Do not use ORDER BY to order the result set in view or inline table-valued function

SA0270 : A filtered index created with the IS NULL predicate is not used in SQL Server

SA0272 : SELECT statement without row limiting conditions