Showplan Query Execution Plans

SQL Server execution plans are the key to optimising queries in an empirical, scientific way. The wrong way to go about optimising SQL is to trial and error until the query appears to run more quickly. The problem with that technique is that your query might not scale efficiently.

Confronting an execution plan for the first time, graphical or otherwise, can be quite daunting. There are a lot of different operations going on and whilst some of them are fairly self evident, others are quite obscure and only relevant to the internal query engine and have no corresponding objects or T-SQL that they can be easily related to.

There is a very good reference in Books online (BOL) about this which shows all the icons for the query plan components and what they mean.

Looking at estimated or actual query plans whilst referencing that BOL information is a good place to start to become familiar with what all the options do.

Grant Fritchey, the scary DBA, has an excellent book on this topic that is available for free e-book download from simple-talk for registered members. If you work with SQL Server and are not a member of simple-talk or for that matter SQL Server Central then you are seriously missing out on a very friendly and expert community of DBAs and other Data professionals.

I’ve never actually seen such a friendly forum in tech. Oracle community take note! Elitism is eschewed and self improvement is de rigueur in the SQL Server community, but I digress.

Query plans whilst initially confusing can often show in a glarish fashion exactly where the inefficiencies are and which part of the query or DB objects need tweaking. For example index seeks and index scans areobviously related to indexes on tables. Pinal Dave (as usual – who honestly has not read his sharp and useful posts) has a good post about the difference between an  index seek and index scan. Whilst you might think the index seek is the ideal it really depends on how selective the query is, how many records are in the table, the fill factor and other factors.

Generally when looking at execution plans you are interesting in how long the whole query takes, the percentage taken up by each step in the query plan and whether things like table scans would be better served by index scans/seeks.

A table with four rows in it will not be better served by an index.

Monitoring changes to the execution plan whilst optimising a query is many times more advanced and effective than just using trial and error.

 

I highly recommend Grant’s book! Buy it!

 

 

One thought on “Showplan Query Execution Plans

Leave a Reply

Your email address will not be published. Required fields are marked *