What are SQL Execution Plans and how can they help me?

Question

I've been hearing a lot lately that I ought to take a look at the execution plan of my SQL to make a judgment on how well it will perform. However, I'm not really sure where to begin with this feature or what exactly it means.

I'm looking for either a good explanation of what the execution plan does, what its limitations are, and how I can utilize it or direction to a resource that does.

Answer 1

It describes actual algorithms which the server uses to retrieve your data.

An SQL query like this:

SELECT  *
FROM    mytable1
JOIN    mytable2
ON      …
GROUP BY
        …
ORDER BY
        …

, describes what should be done but not how it should be done.

The execution plan shows how: which indexes are used, which join methods are chosen (nested loops or hash join or merge join), how the results are grouped (using sorting or hashing), how they are ordered etc.

Unfortunately, even modern SQL engines cannot automatically find the optimal plans for more or less complex queries, it still takes an SQL developer to reformulate the queries so that they are performant (even they do what the original query does).

A classical example would be these too queries:

SELECT  (
        SELECT  COUNT(*)
        FROM    mytable mi
        WHERE   mi.id <= mo.id
        )
FROM    mytable mo
ORDER BY 
        id

and

SELECT  RANK() OVER (ORDER BY id)
FROM    mytable

, which do the same and in theory should be executed using the same algorithms.

However, no actual engine will optimize the former query to implement the same algorithms, i. e. store a counter in a variable and increment it.

It will do what it's told to do: count the rows over and over and over again.

To optimize the queries you need to actually see what's happening behind the scenes, and that's what the execution plans show you.

You may want to read this article in my blog:

• Double-thinking in SQL

Answer 2

Here and Here are some article check it out. Execution plans lets you identify the area which is time consuming and therefore allows you to improve your query.

Answer 3

1. An execution plan shows exactly how SQL Server processes a query
2. it is produced as part of the query optimisation process that SQL Server does. It is not something that you directly create.
3. it will show what indexes it has decided are best to be used, and basically is a plan for how SQL server processes a query
4. the query optimiser will take a query, analyse it and potentially come up with a number of different execution plans. It's a cost-based optimisation process, and it will choose the one that it feels is the best.
5. once an execution plan has been generated, it will go into the plan cache so that subsequent calls for that same query can reuse the same plan again to save having to redo the work to come up with a plan.
6. execution plans automatically get dropped from the cache, depending on their value (low value plans get removed before high value plans do in order to provide maximum performance gain)
7. execution plans help you spot performance issues such as where indexes are missing

Answer 4

A way to ease into this, is simply by using "Ctrl L" (Query | Display Estimated Execution Plan) for some of your queries, in SQL Management Studio.

This will result in showing a graphic view of Execution Plan, which, at first are easier to "decode" than the text version thereof.

Query plans in a tiny nutshell:
Essentially the query plan show the way SQL Server intends to use in resolving a query.
There are indeed many options, even with simple queries.
For example when dealing with a JOIN, one needs to decide whether to loop through the [filtered] rows of "table A" and to lookup the rows of "table B", or to loop through "table B" first instead (this is a simplified example, as there are many other tricks which can be used in dealing with JOINs). Typically, SQL will estimate the number of [filtered] rows which will be produced by either table and pick the one which the smallest count for the outer loop (as this will reduce the number of lookups in the other table)
Another example, is to decide which indexes to use (or not to use).

There are many online resources as well as books which describe the query plans in more detail, the difficulty is that SQL performance optimization is a very broad and complex problem, and many such resources tend to go into too much detail for the novice; One first needs to understand the fundamental principles and structures which underlie SQL Server (the way indexes work, the way the data is stored, the difference between clustered indexes and heaps...) before diving into many of the [important] details of query optimization. It is a bit like baseball: first you need to know the rules before understanding all the subtle [and important] concepts related to the game strategy.

See this related SO Question for additional pointers.