11

Are there any tools that do Query Optimizations for SQL Server 2005 and above?

I've searched & didn't find anything.

What do you recommend?

If this Question was repeated before you can close it but I didn't see anything similar

Imran Sh
  • 1,623
  • 4
  • 27
  • 50
Saif al Harthi
  • 2,948
  • 1
  • 21
  • 26
  • 2
    You may not have seen the duplicate because there are no such tools - other than the human mind, perhaps aided by the SQL Profiler, Index Tuning Wizard, etc. – John Saunders Dec 14 '10 at 20:52
  • i dont write SQL too much , but i get this Question alot , i'm sorry – Saif al Harthi Dec 14 '10 at 20:53
  • What about extending the question to books covering the topic. I played a bit with dta (database tuning assistant) and had most of the time the feeling what now. – bernd_k Dec 14 '10 at 20:55
  • SQL Profiler is a workload recording tool to feed into the Database Engine Tuning Advisor – JeffO Dec 14 '10 at 20:56

6 Answers6

32

The best tool I've ever used for optimizing queries in MS SQL Server, by far, is the "Include Actual Execution Plan" option in Microsoft SQL Server Management Studio. It displays detailed information about the execution path taken by the server when running your query. (Note that this works best when there's data in the tables. Of course, without a good bit of test data, any optimization is purely theoretical anyway.)

It basically gives you three very important things:

  1. It tells you which steps take the most processing time and what they're doing at that step.
  2. It tells you which steps carry the most data to the next step, including how many records, which helps identify places where you can be more specific about the data you want and exclude unnecessary records.
  3. It gives you a ton of insight into the inner workings of SQL Server and what it does with your queries. This knowledge will help you optimize things a lot over time.
David
  • 208,112
  • 36
  • 198
  • 279
6

In SSMS - Tools | Database Engine Tuning Advisor - Does not work on Express versions.

JeffO
  • 7,957
  • 3
  • 44
  • 53
5

One very good tool and now free for use is Plan Explorer from SentryOne: https://sentryone.com/plan-explorer

(they also have many other optimization software, like Azure specific software etc.)

seekingtheoptimal
  • 642
  • 7
  • 15
2

One of the best query optimizers is just running the query in SQL Management Studio, and then inspecting the query plan. This will give you clues as to what indexes it is (or is not) using, and how you can change the query to take advantage of those.

Joel Martinez
  • 46,929
  • 26
  • 130
  • 185
0

Also this is a good tool for monitor and optimize queries:

Sql Monitor by Red Gate

Imran Sh
  • 1,623
  • 4
  • 27
  • 50
0

As John Saunders commented, the best tool at your disposal is your own mind. Following bernd_k's comment, here are a few suggestions for sharpening that tool.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Not trying to dismiss your advice, but it seems a bit bland to advice for manual tuning when specifically asking for an automatic tuning tool. It's like asking for a laser printer and get adviced on improving your calligraphy. I get it from a personal point of achievement, but from a work perspective, you're expected to deliver results fast. – alejandrob May 20 '15 at 14:05
  • Perhaps his point is that you can't just get fast results. The same way that you can't get a tool to write your application for you. At some point you have to spend time to learn and understand the reason a query is slow. Many performance problems require rewriting the query or stored procedure. No tool will do that for you. – NickG Aug 26 '16 at 14:01