CODEWITHSUNDEEP
sql-servert-sqlsql-execution-plan
Daniel
Daniel

Reputation: 11054

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

I hear that I ought to look at the execution plan of my SQL to make a judgment on how well it will perform.

What does the execution plan do?

What are its limitations?

How I can utilize it?

Upvotes: 8

Views: 687

Answers (7)

mjv
mjv

Reputation: 75125

Ease into this by using Ctrl-L (Query | Display Estimated Execution Plan) for some of your queries in SQL Management Studio.

This will show graphic views of execution plans, which at first are easier to decode than the text versions.

The query plan shows the way SQL Server intends to use in resolving a query.

There are many options, even with simple queries.

For example when dealing with a JOIN, SQL needs to decide whether to loop through the (filtered) rows of the first table and to look up the rows of the second table, or to loop through the second 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 with 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.

Many online resources as well as books describe query plans in more detail. 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 needs to understand the fundamental principles and structures which underlie SQL Server before diving into many of the (important) details of query optimization. (The way indexes work, the way the data is stored, the difference between clustered indexes and heaps ....) You need to know the rules before understanding all the subtle (important) concepts related to the strategy.

See Sql Query Optimization.

Upvotes: 1

HLGEM
HLGEM

Reputation: 96572

Here's a great resource to help you understand them http://downloads.red-gate.com/ebooks/HighPerformanceSQL_ebook.zip

This is from red-gate which is a company that makes great SQL server tools, it's free and it's well worth the time to download and read.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425371

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:

Upvotes: 4

Fenton
Fenton

Reputation: 250922

The Execution Plan shows you how the database is fetching, sorting and filtering the data required for your query.

For example:

SELECT
    *
FROM
    TableA
INNER JOIN
    TableB
ON
    TableA.Id = TableB.TableAId
WHERE
    TableB.TypeId = 2
ORDER BY
    TableB.Date ASC

Would result in an execution plan showing the database getting records from TableA and TableB, matching them to satisfy the JOIN, filtering to satisfy the WHERE and sorting to satisfy the ORDER BY.

From this, you can work out what is slowing down the query, whether it would be beneficial to review your indexes or if you can speed things up in another way.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

  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

Upvotes: 2

Sergey Mirvoda
Sergey Mirvoda

Reputation: 3239

it is a very serious part of knowledge. And I highly to recommend special training courses about that. As for me after spent week on courses I boosted performance of queries about 1000 times (nostalgia)

Upvotes: 0

Vinay Pandey
Vinay Pandey

Reputation: 8913

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.

Upvotes: 1

Related Questions