sql-servert-sqlsql-execution-plan

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?

Solution

  • 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: