Click or drag to resize

Analysis Rule

SQL Enlight analysis rules are basically XSL templates which are applied on the SQML generated from the T-SQL code or on the XML generated from the database schema.

This topic contains the following sections:

Types

Context rules

The Context Only rules can be used to only analyze current database schema. This kind of rules does not depend on the analyzed T-SQL code, but only on the current connection analysis context. The Analysis Context XML can be referenced in the rule expression using the following XSLT variables:

  • $context - references the root Context XML element.

  • $server - references the Server XML element.

  • $database - references the Database XML element.

Important for the Context Only analysis rules is that they are evaluated only once at the end of the analysis process.

Candidates for Context Only rules are all the analysis rules which do not refer the SQML, but only use the current analysis context.

Batch rules

The Batch analysis rules also have access to the analysis context, but are focused on the T-SQL code its SQML representation. The batch rules are applied separately for each of the analyzed T-SQL batches.

For specific types of checks might be convenient to have separate implementations for the both rule types in order to cover both database schema and T-SQL code violations.

Example of such rule which support both context and batch analysis is rule SA0043.

The rule has one implementation - SA0043A that checks the current database schema for usage of the obsolete data types and another implementation - SA0043A which checks the T-SQL code for usage of those types.

Explicit rules

The explicit rules are not a separate rule type, but just rules added to the Explicit Rules group.

This group is meant to store all the rules which are meant to be executed separately, because the results they produce generally (but not necessarily) are more informational than just indicating defects or showing possible problems.

Dynamic rules

Dynamic rules are the rules which use direct queries to the underlying database to retrieve analysis data and generate results.

This kind of rules mainly process and output the server state information returned by the SQL Server dynamic management views and functions.

The information can be used to monitor the health of a server instance, diagnose problems, and tune performance.

Variables

The variables $v-rulename, $v-ruledescription, $v-ruleseverity are initialized when the rule is generated and hold respectively the rule name, description and severity.

The following variables are also available inside the analysis rule:

  • $context – holds reference to the analysis context node set

  • $server – references the server node in the analysis context

  • $server-name – current server name

  • $database – references the node of the current database on the current server

  • $database-name – the name of the current context database

  • $server-case-sensitive – is of boolean type and is true when the server collation is case sensitive

  • $database-case-sensitive – is of boolean type and is true when database has default case sensitive collation

  • $parameters – holds node-set of Param nodes for accessing rule parameter values

Parameters

A parameter value can be referenced inside the analysis rule using the $parameters variable.

Here is an example of setting parameter's value in a XSLT variable inside analysis rule expression:

XSLT
1<xsl:variable name="RuleSuppressMark" select="$parameters/Param[@Name='RuleSuppressMark']/text()"/>
Expression

Analysis rule expression can be defined using the standard XSLT syntax,but with some restrictions to the allowed XSLT elements.

The restrictions are meant to disallow output results which are not valid according the SQL Enlight analysis engine.

Almost all elements of the XSLT standard syntax are allowed except elements which can directly manipulate the result data like:

xsl:call-template, xsl:value-of, xsl:element, xsl:attribute and etc.

These elements are allowed as child of: xsl:varaiable, xsl:with-param and xsl:param.

Additional to the standard XSLT and XPath functions, SQL Enlight provides several extension functions which can be used inside the rule expression. See XSLT Extension Functions for list of supported extensions.

Simple expression which matches all statements in the current batch can be defined like this:

XSLT
 1<xsl:for-each select="/*[1]/g:batch//g:statement">
 2<!-- Return rule violation information by calling the "output-message" template. -->
 3<xsl:call-template name="output-message">
 4<xsl:with-param name="line" select="@se:sline"/>
 5<xsl:with-param name="column" select="@se:scol"/>
 6<xsl:with-param name="msg" select="$v-rulename"/>
 7<xsl:with-param name="desc" select="concat($v-rulename,' : ',$v-ruledescription)"/>
 8<xsl:with-param name="near" select="text()"/>
 9<xsl:with-param name="type" select="$v-ruleseverity"/>
10</xsl:call-template>
11</xsl:for-each>

The call to the output-message writes the rule details as well as the source string of the rule violation.

Example

Below is an example of complete analysis rule expression checks FROM clauses for not specified NOLOCK table hint.

XSLT
1<!-- Get rule parameters. -->
2<xsl:variable name="RuleSuppressMark" select="$parameters/Param[@Name='RuleSuppressMark']/text()"/>

Select all FROM clauses in the batch.

XSLT
1<xsl:for-each select="$batch//k:from[parent::k:select]">
2  <xsl:variable name="from-clause" select="."/>

Generate unique id which to be used for restricting the scope of the FROM clause and identifying the parent FROM clause of given table source.

XSLT
1<xsl:variable name="from-clause-id" select="generate-id($from-clause)"/>

Select table source container elements.

XSLT
1<xsl:variable name="table-source-containers" select="$from-clause/g:commalist 
2                                                  |  $from-clause//k:join"/>

Select and iterate the single or multipart identifiers which are direct children of the table source containers.

XSLT
 1<xsl:for-each select="$table-source-containers/*[self::pu:dot or self::i:* or self::k:as/*[not(self::cmt:*)][1]/self::*[self::i:* or self::pu:dot]]">
 2  <xsl:variable name="table-source" select="."/>
 3
 4  <!-- If the table is aliased, the hints are under the AS keyword element, otherwise the hints are under the identifier element. -->
 5  <xsl:variable name="table-hint-target" select="$table-source[not(self::pu:dot)]
 6                                             | $table-source/self::pu:dot/descendant-or-self::pu:dot[not(pu:dot)][1]/i:*[last()]"/>
 7
 8  <!-- Check for existence each possible way of setting a table hint. -->
 9  <xsl:if test="$table-hint-target[not($table-hint-target/g:brackets/g:commalist/i:common[str2:compare(@name,'nolock',true())=0] or 
10                $table-hint-target/g:brackets/g:commalist/g:expression/i:common[str2:compare(@name,'nolock',true())=0] or             
11                $table-hint-target/g:brackets/g:commalist/pr:hint[@name='nolock'] or
12                $table-hint-target/k:with/g:brackets/g:commalist/pr:hint[@name='nolock'] )]">
13
14    <xsl:variable name="table-identifier" select="$table-hint-target/self::k:as/*[not(self::cmt:*)][1]/self::*[self::i:* or self::pu:dot]
15                      | $table-hint-target/self::i:*"/>

The hint was not matched and we return rule violation information by calling the "output-message" template.

XSLT
 1      <xsl:call-template name="output-message">
 2        <xsl:with-param name="line" select="$table-hint-target/@se:sline"/>
 3        <xsl:with-param name="column" select="$table-hint-target/@se:scol"/>
 4        <xsl:with-param name="msg" select="$v-rulename"/>
 5        <xsl:with-param name="desc" select="concat($v-rulename,' : The NOLOCK table hint is not set for table [', $table-identifier/@name,'].')"/>
 6        <xsl:with-param name="near" select="$table-identifier/@name"/>
 7        <xsl:with-param name="type" select="$v-ruleseverity"/>
 8      </xsl:call-template>
 9    </xsl:if>
10  </xsl:for-each>
11
12</xsl:for-each>

For examples of analysis rules, you can check the rule expressions of the existing rules.

See Also