We are happy to announce the today’s we release of SQL Enlight 1.9 Beta 2.

Here are the main release notes:

• Fixed the 80004003 error which appeared when starting Viusal Studio or SSMS.
• Several documentation improvements including new tutorials for creating analysis rules.
  • SQL Enlight Online Documentation
  • How To: Extend Analysis Template with Visual Studio
  • How To: Create Analysis Rule with Visual Studio
  • How To: Create Analysis Rule with Rule Designer
• A new SDK component, which is mainly meant to provide API for creating and testing custom analysis rules. See the SQL Enlight SDK Reference for more information.

The new version can be downloaded here.

After almost a year, we are very happy to announce the release of SQL Enlight 1.9.0.587 Beta.
Here follows a list of the major new features and improvements that are included in it.

New Features

1. Static Code Analysis TFS Check-in Policy. Both TFS 2008 and TFS 2010 are supported.
2. Two new refectories:
   • Expand Wildcards
   • Add Statement Terminators
3. Layout:
   • Option to ignore layout of selected statements
   • New comment layout options
4. 25 new analysis rules. See here for complete list of the new rules.

Improvements

• Command line tool, NAnt and MSBuild tasks improvements now support recursive processing of folders and multiple target files.
• Analysis rules – several extensions made available including.
• Layout command – syntax errors will not stop the layout, but will just skip the source batch.
• Automatic updates checking.
• Memory usage optimizations.

Free features

The following features are now free to use and are not trial limited:

• Validate SQL Code
• Statements History
• Summarize Script
• Outline Statements

The version is 1.9.0.587 and is available for download here.

We are happy to announce the release of the latest SQL Enlight version – 1.8.1.541.

Here is what’s new in this release:

• Encapsulate as View
• Encapsulate as sp_executesql call
• Extract to separate script
• Extract object’s body to separate script
• Expand sp_executesql call
• Qualify object names refactoring
• Encrypted database objects support
• NAnt Tasks

In addition to the above features, the new version includes updated analysis rules and a few other improvements and small fixes.

SQL Enlight 1.8.1.541 is available for download here.

Version 1.7.3.515 of SQL Enlight is already released and available for download.

Here is what’s new in this release:

• Extended query execution plan analysis support
• A new analysis rule for query execution plan analysis – EX0018
• A new external template parameter support in the command line tool and MSBuild tasks
• Small improvements and fixes

The new parameter is added to provide a way to set external analysis template which to be used during analysis instead of the default one. Check the SQL Enlight documentation for the command line tool and MSBuild tasks parameters.

As the Execution Plan Analysis is the most important feature update in this release, here we have prepared a separate post for it.

We will be happy to help you if you have any questions or comments.

Thank you!

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