Advanced tool for optimizing T-SQL? [closed]

As it 开发者_运维百科currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 9 years ago.

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.

---

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

---

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.