Explicit
EX0001 : Identify objects used in the script.
EX0002 : Get object dependencies - Objects on which database objects depend.
EX0003 : Get object dependencies - Objects that depend on other database objects.
EX0004 : Find identifier references inside the T-SQL script.
EX0005 : Check script for data modifying statements - INSERT,UPDATE,DELETE or EXECUTE.
EX0006 : Identify possible missing Foreign Keys.
EX0007 : List all DML and DDL triggers in current database.
EX0008 : Generate CREATE INDEX statements for all columns included in foreign keys.
EX0009 : Consider adding proper comment block before each database object create statement.
EX0010 : Identify missing indexes using dynamic management views information.
EX0011 : Identify inefficient indexes using dynamic management views information.
EX0012 : Displays memory usage information for the current database.
EX0013 : Identify fragmented indexes that need rebuilding or reindexing.
EX0014 : List the last execution status of all available SQL Server jobs.
EX0015 : Find Best Clustered Index
EX0016 : Generate DROP INDEX statements for inefficient indexes using dynamic management views information.
EX0017 : Generate ALTER INDEX statements for rebuilding or reorganizing fragmented indexes.
EX0018 : Analyze execution plan and check for high cost operations.
The Explicit Rules group is internal a group and is used for containing rules that to be available directly in the SQL Enlight->Static nalysis menu.
By default the group is not enabled and it is not shown in Settings->Analysis tab and in the Analysis Rule Selector dialog.
Note: When adding new rules to the Explicit Rules gorup, the rules will appear in the SQL Enlight->Static Analysis menu just after the SSMS or Visula Studio is restarted.
Rule: EX0001
Message
Identify objects used in the script.
Description
The rule outputs description for the idenrifiers found in the analyzed T-SQL code.
Parameters
|
Name |
Description |
|---|---|
| IngoreObjectsList | Comma separated list of database object names to ignore during the identifier lookup. Separators: ',','|' or ';'. |
| IgnoreUnidentified | Specify if the not resolved identifiers will to be shown in results list. Accepted values: 'yes' or 'no' |
Category
Additional Information
None
Rule: EX0002
Message
Get object dependencies - Objects on which database objects depend.
Description
The rule lists all objects that a given database object depends on. The rule uses the information from the SQL Server sys.dependencies database view.
Parameters
|
Name |
Description |
|---|---|
| ObjectName | Provide object name in order to get results only filter results for specific object or leave the parameter empty to get resuls for all available objects. |
Category
Additional Information
None
Rule: EX0003
Message
Get object dependencies - Objects that depend on other database objects.
Description
The rule lists all objects that depend on given database object. The rule uses the information from the SQL Server sys.dependencies database view.
Parameters
|
Name |
Description |
|---|---|
| ObjectName | Provide object name in order to get results only filter results for specific object or leave the parameter empty to get resuls for all available objects. |
Category
Additional Information
None
Rule: EX0004
Message
Find identifier references inside the T-SQL script.
Description
The rule lists all objects that depend on given database object. The rule uses the information from the SQL Server sys.dependencies database view.
Parameters
|
Name |
Description |
|---|---|
| IdentifierName | Identifier name. The parameter is required. |
| CaseSensitive | Specify if the identifier name is case sensitive. |
Category
Additional Information
None
Rule: EX0005
Message
Check script for data modifying statements - INSERT,UPDATE,DELETE or EXECUTE.
Description
Check script for data modifying statements - INSERT,UPDATE,DELETE or EXECUTE.
Note: Modification of table variable is ignored by the rule.
Category
Additional Information
None
Rule: EX0006
Message
Identify possible missing Foreign Keys.
Description
The rule checks database tables for columns which do not have a foreign key reference, but match by name and data type to a primary key of a different table. The rule checks only for single columns and assumes that the referencing columns has the same name as the primary key column name.
Category
Additional Information
None
Rule: EX0007
Message
List all DML and DDL triggers in current database.
Description
The rule lists all triggers in the current database.
Parameters
|
Name |
Description |
|---|---|
| IncludeEvents | Speecify whether to include trigger events in the rule message. |
Category
Additional Information
None
Rule: EX0008
Message
Generate CREATE INDEX statements for all columns included in foreign keys.
Description
The rule checks for not indexed foreign keys in the current database and generated the appropriate CREATE INDEX statements.
Parameters
|
Name |
Description |
|---|---|
| MinimumRowsCount | Include only tables that have more than the specified minimum number of rows. |
Category
Additional Information
None
Rule: EX0009
Message
Consider adding proper comment block before each database object create statement.
Description
The rule checks the T-SQL script for missing or incomplete header block above the procedures, triggers, views and functions creation statements.
The rule parameters provide standard regular expression templates for matching the specific comment block elements (Author, Create Date and etc.).
Parameters
|
Name |
Description |
|---|---|
| AuthorLineTemplate | Regular expression to match the Author line in the header block. |
| CreatedDateLineTemplate | Regular expression to match the Create Date line in the header block. |
| UpdatedDateLineTemplate | Regular expression to match the Update Date line in the header block. |
| UpdatedByLineTemplate | Regular expression to match the Update By line in the header block. |
| DescriptionLiteTemplate | Regular expression to match the Description line in the header block. |
Category
Additional Information
None
Rule: EX0010
Message
Identify missing indexes using dynamic management views information.
Description
The rule displays missing indexes information by using the SQL Server Dynamic Management Views.
The missing indexes information is based on statistics gathered since the last time SQL Server instance was started.
The query used by the rule is based on the article 'Getting Indexing Suggestions In Transact SQL' (http://whitneyweaver.com/post/Getting-Indexing-Suggestions-In-Transact-SQL.aspx).
For more information on SQL Server Dynamic Management Views:
http://msdn.microsoft.com/en-us/library/ms187974.aspx
Category
Additional Information
None
Rule: EX0011
Message
Identify inefficient indexes using dynamic management views information.
Description
The rule checks for indexes that haven't been used recently and are possible candidates to be dropped by using the SQL Server Dynamic Management Views.
The unused indexes information is based on statistics gathered since the last time SQL Server instance was started.
For more information on SQL Server Dynamic Management Views:
http://msdn.microsoft.com/en-us/library/ms188755.aspx
http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use
Parameters
|
Name |
Description |
|---|---|
| IndexEfficiencyMininumPercent | The parameter specifies the efficiency of the index since the last server restart. The index efficiency is calculated based on the reads per writes. |
| IndexMinimumNumberOfRows | Indexes covering less rows than specified by this parameter will be ignored. |
Category
Additional Information
None
Rule: EX0012
Message
Displays memory usage information for the current database.
Description
The rule displays memory usage information for the current database using the dynamic management view sys.dm_os_buffer_descriptors.
For more information:
http://msdn.microsoft.com/en-us/library/ms173442.aspx
Category
Additional Information
None
Rule: EX0013
Message
Identify fragmented indexes that need rebuilding or reindexing.
Description
Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
You can remedy index fragmentation by either reorganizing an index or by rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or on a single partition of an index.
For more information, see http://go.microsoft.com/fwlink/?linkid=31012.
Parameters
|
Name |
Description |
|---|---|
| MaximumAllowedFragmentation | Maximum allowed fragmentation for an index that to be ignored by the rule. |
| MinimumPagesCount | Minimum number of pages an index must have in order to be considered by the rule. |
| MaximumFragmentationForReorgainizeIndex | Maximum fragmentation percent below which index reorganization will be suggested by the rule. Above this specified percent, rebuilding of the index will be suggested. |
Category
Additional Information
None
Rule: EX0014
Message
List the last execution status of all available SQL Server jobs.
Description
The rule displays the last status of all SQL Server jobs on the current server.
Parameters
|
Name |
Description |
|---|---|
| FilterJobsWithStatus | The parameter specifies whether the rule to list only the jobs with specified status. |
Category
Additional Information
None
Rule: EX0015
Message
Find Best Clustered Index
Description
The rule uses the SQL Server Dynamic Management Views to identify the non-clustered indexes that are candidates to become clustered indexes. The non-clustered indexes are considered better compared to the existing clustered indexes if the number of user seeks on those indexes is greater than the number of lookups on the related to the table clustered index.
For more information:
http://sqlserverplanet.com/indexes/choosing-the-best-clustered-index/
http://sqlserverplanet.com/dmv-queries/find-best-clustered-index/
Category
Additional Information
None
Rule: EX0016
Message
Generate DROP INDEX statements for inefficient indexes using dynamic management views information.
Description
The rule generates DROP INDEX statements for all indexes that haven't been used recently and are possible candidates to be dropped by using the SQL Server Dynamic Management Views.
The unused indexes information is based on statistics gathered since the last time SQL Server instance was started.
For more information on SQL Server Dynamic Management Views:
http://msdn.microsoft.com/en-us/library/ms188755.aspx
http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use
Parameters
|
Name |
Description |
|---|---|
| IndexEfficiencyMininumPercent | The parameter specifies the efficiency of the index since the last server restart. The index efficiency is calculated based on the reads per writes. |
| IndexMinimumNumberOfRows | Indexes covering less rows than specified by this parameter will be ignored. |
Category
Additional Information
None
Rule: EX0017
Message
Generate ALTER INDEX statements for rebuilding or reorganizing fragmented indexes.
Description
The rule identifies fragmented indexes and generates ALTER INDEX statements for rebuilding or reorganizing those indexes.
Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
You can remedy index fragmentation by either reorganizing an index or by rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or on a single partition of an index.
For more information:
http://go.microsoft.com/fwlink/?linkid=31012
Parameters
|
Name |
Description |
|---|---|
| MaximumAllowedFragmentation | Maximum allowed fragmentation for an index that to be ignored by the rule. |
| MinimumPagesCount | Minimum number of pages an index must have in order to be considered by the rule. |
| MaximumFragmentationForReorgainizeIndex | Maximum fragmentation percent below which index reorganization will be suggested by the rule. Above this specified percent, rebuilding of the index will be suggested. |
Category
Additional Information
None
Rule: EX0018
Message
Analyze execution plan and check for high cost operations.
Description
The rule generates execution plan of the statements and check the plan for missing indexes and operations that have high relative to the statement cost.
The supported operations are: Bookmark Lookups (RID Lookup and Key Lookup), Table Scan, Index Scan, Hash Match and Sort.
These should be considered for reviewing as the reasons for each of these operations often may have negative impact on the specific query.
The analysis rule also checks for missing indexes that have high impact on a specific query and are found during query plan generation.
The impact for each of the supported operations can be configured using the rule parameters.
Parameters
|
Name |
Description |
|---|---|
| CheckForTableScanOperatorsCost | The parameter specifies the minimum cost in percent of the 'Table Scan' operator in order it to be included in the results. Value of equal or greater of 100 will exclude the operator from the results. |
| CheckForIndexScanOperatorsCost | The parameter specifies the minimum cost in percent of the 'Index Scan' operator in order it to be included in the results. Value of equal or greater of 100 will exclude the operator from the results. |
| CheckForBookmarkLookupOperatorsCost | The parameter specifies the minimum cost in percent of the Bookmark Lookup (Key Lookup, RID Lookup) operators in order to be included in the results. Value of equal or greater of 100 will exclude the operator from the results. |
| CheckForSortOperatorsCost | The parameter specifies the minimum cost in percent of the 'Sort' operator in order it to be included in the results. Value of equal or greater of 100 will exclude the operator from the results. |
| CheckForHashMatchOperatorsCost | The parameter specifies the minimum cost in percent of the 'Hash Match' operator in order it to be included in the results.Value of equal or greater of 100 will exclude the operator from the results. |
| CheckForOperationsWithCost | The parameter specifies the minimum cost in percent of any of the available operator in order to be included in the results.Value of equal or greater of 100 will exclude the operator from the results. |
Category
Additional Information
None