Advanced tool for optimizing T-SQL? [closed]

问题:

Is there a tool which can look at your T-SQL and suggest hints for better performance? For example it finds usage of SARGs and converts a query into one without use of a sarg. Or pinpointing inefficient use of joins.

I am looking for tools beyond the Database Engine Tuning Advisor & use of the Profiler. For example I am currently checking out Toad for SQL Server which has some optimization features.

Addition:

Toad has an optimizer where it creates different queries or scenarios for my query and evaluates the cost of each query but its suggestions are primitive & simple. It's in the right direction but hopefully the optimizer gets a lot more smarter over time and be more useful.

回答1:

While it won't cook breakfast nor repair your automobile, the LessThanDot SqlCop can highlight some wayward database code and objects which have an effect on performance. It is a best practices analyzer that is more application-oriented than the Microsoft Best Practices Analyzer (which is more DBA-oriented). The price is nice for both of them (free).

Check them out for yourself...

• LessThanDot SqlCop

• MS Best Practices Analyzer

回答2:

There are a couple of static analysis tools for T-SQL which can identify the types of problems you're interested in detecting, although they will not suggest detailed fixes.

SQL Enlight offers 80+ pre-defined static analysis rules plus the ability to define your own - it will run as an SSMS plugin and a command-line tool. There's also a limited version on the website.

The Visual Studio editions which include the database tools also include fewer (20 or so) static analysis rules for T-SQL. Using it requires importing your code into a VS database project.