The extended Execution Plan Analysis support is the most significant improvement in latest SQL Enlight release 1.7.3.515. It allows execution plans analysis to be automated and applied not only on separate statements or batches, but on all stored procedures and views in a given database.

The execution plan analysis is implemented in the new analysis rule ‘EX0018: Analyze execution plan and check for high cost operations’. The rule uses the XSLT extension method execute-query-plan which now extended with the support for generating XML execution plans for CREATE PROCEDURE and CREATE VIEW statements for which SQL Server otherwise does not produce execution plans.

The XML Execution Plan is generated using the execute-show-plan XSLT extension method which generates the execution plan XML by executing the provided T-SQL script with SHOWPLAN_XML ON option set. Internally, if necessary the method does some additional work in order to rewrite the CREATE PROCEDURE and CREATE VIEW statements in a way that it to be possible for an execution plan XML to be generated. The rewriting basically extracts the statement body (e.g. the SELECT statement from CREATE VIEW or the stored procedure body from CREATE PROCEDURE) and uses it to get the XML plan.

For example, the procedure statement on the left cannot generate execution plan XML, but after being rewritten by SQL Enlight, the resulting an expanded script (the one on the right) will be able to have an execution plan generated.

After having execution plan XML, the analysis rule EX0018 checks for several operations that could have in some cases a negative impact over query performance.

The rule checks the XML plan and returns information for missing indexes that have high impact on the analyzed query, and also checks several operations for having higher than allowed(set in the rule parameters) estimated operator cost. The currently supported operators are: Bookmark Lookups(Key Lookup, RID Lookup), Table Scan,Index Scan, Sort and Hash Match.

The analysis results are output in the SQL Enlight Error List allow navigation to the statement that produces the given rule violation.

See Also

• Showplan Schema – http://schemas.microsoft.com/sqlserver/2004/07/showplan/
• Logical and Physical Operators Reference – http://msdn.microsoft.com/en-us/library/ms191158.aspx
• Transact-SQL Statements That Produce Showplans – http://msdn.microsoft.com/en-us/library/ms187886.aspx

Last week we released a new version of SQL Enlight – 1.7.2.513.
It contains just small fixes and 2 new features:

• A new layout rule ‘Miscellaneous->Keywords padding’.

-- Keyword Padding option set to 10:
SELECT     ProductID ,
           SpecialOfferID ,
           AVG( UnitPrice ) AS 'Average Price' ,
           SUM( LineTotal ) AS SubTotal
FROM       Sales.SalesOrderDetail
WHERE      SpecialOfferID IN(  1 ,
                               2 ,
                               6 ,
                               9 )
GROUP BY   ProductID ,
           SpecialOfferID
ORDER BY   ProductID ,
           SpecialOfferID DESC

-- Keyword Padding option set to 0:

SELECT ProductID ,
       SpecialOfferID ,
       AVG( UnitPrice ) AS 'Average Price' ,
       SUM( LineTotal ) AS SubTotal
FROM Sales.SalesOrderDetail
WHERE  SpecialOfferID IN(  1 ,
                           2 ,
                           6 ,
                           9 )
GROUP BY ProductID ,
         SpecialOfferID
ORDER BY ProductID ,
         SpecialOfferID DESC

• A new XSLT extension method ‘execute-query-plan’ which to enable support for query plan analysis.

We will describe in more detail the usage of this method in the next post.

The new version is already available for download here.

One of the most exciting features in SQL Enlight is the ability to create your own static analysis rules. This is very powerful feature which can be of great use and unleash almost unlimited number of possibilities for the ones who consider trying it.

The current post is the first of a series of blog posts which to guide you through the creation of custom static code analysis rules with SQL Enlight. Each of the subsequent articles will cover the implementation of several interesting analysis rule and will point some unfortunately not so obvious features and abilities of SQL Enlight static code analysis engine.

(more…)

The SQL Enlight analysis templates are XML files that contain analysis rules and analysis groups definitions. The templates can be imported in SQL Enlight and this way can update or add new rules to the currently supported set of rules. (more…)

We are very excited to announce that the latest and greatest version of SQL Enlight, version 1.7.2.509, is now available for download.
The new release of SQL Enlight includes: several usability improvements, 13 absolutely new analysis rules, and at last but definitely not at least is the support for directly executing database queries from within the analysis rules.

This new direct queries support, even not being exceptional by its means is quite useful and important and enables new opportunities for not only implementing static code analysis rules, but also for dynamic database analysis with SQL Enlight. (more…)