This article has been guest blogged by Iliyan Stoyanov of Ubitsoft
As databases get bigger, the number of database objects increase and the amount of business logic implemented using T-SQL code becomes quite substantial, often even more critical than the application code; the automation of the process of ensuring code quality is becoming more and more important.
Static code analysis is a popular method for code verification and defect detection in the .NET, C++ and Java development worlds, but very often left behind and neglected by the SQL Server development community.
SQL Enlight is a tool for SQL Server that can automate and facilitate the T-SQL code and database schema analysis. The tool works in a way similar to the .NET focused tools like FxCop and StyleCop. It provides design-time code, database schema and query plan analysis. The tool comes with about 70 out of the box analysis rules and also supports custom analysis rules.
Code Analysis
One of the supported types of analysis by SQL Enlight is the T-SQL script analysis. It can be used validating the code for syntax errors as well as for conforming to best practices or your own standards and requirements.
Some of the T-SQL code aspects that can be checked are:
- Deprecated syntax (old outer joins, IMAGE and TEXT data types, deprecated stored procedures usage and etc.)
- Naming conventions ( ‘sp_’ prefix, special characters or reserved keywords in object names)
- Not recommended statements, expressions and operators that can impact maintenance, produce unwanted results or have a good chance to be written by mistake.
- Statements, expressions and operators that may have impact on the query performance
Database Analysis
Besides the T-SQL code analysis, SQL Enlight supports database schema analysis and can combine and use both forms of analysis.
The database schema analysis can identify several issues that can have negative impact on the performance:
- Query performance issue because not optimal index usage
- Index fragmentation issues
- Missing or not used indexes
- Missing primary keys and clustered indexes
- Missing column and index statistics
- Missing indexes on foreign key columns
- Inefficient indexes which may be dropped
- Existence of better candidate for clustered index than the current table index
- Overlapping or duplicate indexes
- Outdated statistics
Database analysis can be applied at once on single or multiple databases as well as on a SQL Server instance.
Query Execution Plan Analysis
Viewing the graphical execution plan in SQL Server Management Studio provides a great way to visually read through the operations of the statements of the execution plan and identify possible issues which may affect your query performance. This is a fairly useful feature, but it has some limitations:
- Large plans can be hard and time consuming to read.
- The execution plan for procedures, views and functions cannot be viewed directly.
- Encrypted procedures, views and functions are not supported.
Next to these, reading and analyzing through all your database objects and scripts can take quite some time when you have several dozen or more of T-SQL database objects, especially when there are more than one developer responsible for modifying and maintaining the specific database. Adding or dropping indexes, creating or updating statistics, inserting, deleting or updating large amount can negatively affect the performance of existing stored procedures, views and functions. The impact can be often hard to as it may require the affected T-SQL objects and their execution plans to be revised for issues.
SQL Enlight provides a feature which can very well automate and provide solution for the above problems:
- Automate execution plan analysis, identify and notify for: missing indexes that have high impact on the analyzed query; Bookmark Lookups(Key Lookup, RID Lookup), Table Scan, Index Scan, Sort and Hash Match operations that have significant estimated operator cost.
- Support for direct execution plan analysis of stored procedure, views and functions.
- At once execution plan analysis of all T-SQL objects in one or all databases of a single SQL Server instance.
Additional Features
SQL Enlight is provided as an add-on to Microsoft SQL Server Management Studio and Microsoft Visual Studio. The tool has a command line version and includes MSBuild tasks which can be used for scheduling analysis or integrating with automated builds. Along with the static analysis, SQL Enlight offers a set of T-SQL code refactoring features: code layout and formatting, encapsulate script, script summary and executed statements history.
The best way to find out how SQL Enlight will work for you is to download and try it out during the 14-day free trial.
About The Author
2 comments:
What is the difference between SQL Enlight when compared to Policy Based Management?
How is SQL Enlight compared to Policy Based Management?
Post a Comment