What's the surprise?

This is repost of a blog I did somewhere else. Originally posted 19/7/2011. Still trying to remember / work out what the surprise is. Think, I was unimpressed by the recommended index in the query plan. Could be a little clearer...

Executuion plans and a surprise

It's a while since my last blog but here goes...

You use a query’s execution plan to work out how SQL is running is returning the results of your query to you. In this post I am going to:

·         Show how to get estimated and actual query plans.

·         Show how to save them to allow other people to look at that.

·         Show a few things around using them

There are two version of execution plan, estimated and actual.

·         Estimated can be generated without running a query. It is the plan that SQL will try to use when it runs the query.

·         The actual plan is, surprisingly, the actual plan recorded when the query is actually run.

The actual plan has some details not in the estimated plan and there are some conditions when the actual plan will be different to the generated estimated plan. Using the actual plan allows you to see the results of your query and get an idea of how long it will take to run. If you’re looking at performance, also run without generating the actual plan as it adds a lot of overhead and does not reflect the query’s actual run time. Clear out the caches as well to give a clean basis of comparison.

You can get the estimated plan in SSMS using the Query \  Display Estimated Execution Plan menu option (or CTRL+L for anybody who just can’t wait). It will take a few seconds to return the plan. This is not a reflection of how long SQL takes to generate the plan when running.

The actual plan is generated when a query is run and will be returned if you set the Query \ Include Actual Execution Plan option before running the query.

This will return the actual details record when the query was run. A good thing to look out for is differences between the actual and estimated row counts. This indicates that SQL could not determine how many rows it would need to process and there is a good chance a better execution plan was possible if more accurate row counts were available. This can be caused by a lack of statistics, out of date statistics or using table variables with large row sets. For us, table variables are more likely to be an issue. I’ve seen cases where changing from table variables to temporary tables has changed query time form around 20 minutes to around 10 seconds. I’ve also seen cases where going from around 1 second to around 1/100 of a second. But, that’s not what I want to write about today.

Once you have the plan, you can start to use it to improve your query performance.

The easiest thing you can do is write two versions of your query and compare the relative cost. A lot of the time recoding the query can make a difference in performance and readability.

In SSMS, hovering over the query icons will show a tool tip. Details change with the task type. You can get more details by selecting the task and displaying its properties.

The size of the arrows joining tasks tells you how many rows the task is returning. The tool tip will tell you the number and size of estimated and actual rows. Differences in row counts indicate a statistics problem. The size of the rows multiplied by the number of rows gives the total data returned by the task. Therefore, the size of rows can be more significant than simply the number of rows returned.

In 2008, the plan can return a list of missing indexes and tell you how much adding them will improve your queries. It comes up in green. Right clicking it and selecting Missing Index Details… opens the index’s creation code in a new query window. In 2005, and in 2008, the supporting details are stored in DMVs and can be retrieved using queries. Be careful with these hints. They can recommend a number of slightly different but similar indexes when one covering index could improve all queries with less overhead. The index definition could be bad too. Look at this query and its recommended index.

Query

SELECT       * FROM       FactSales WHERE       ProductKey = 12345

Index

/* Missing Index Details from SQLQuery3.sql - (local)\dwh.DWH_TEST_08062011 (ERGO\imeade (57)) The Query Processor estimates that implementing the following index could improve the query cost by 99.962%. */ /* USE [DWH_TEST_08062011] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[FactSales] ([ProductKey]) INCLUDE ([everyb field in the table] ) GO */

I’ve marked the expected improvement and the include clause in bold and underline. The improvement look great, but you do not want this in this index. It’ll copy the entire table without adding much value. This is a table of about 35G in a 40G database. This index will give you a 75G database that has a few point queries with excellent performance. Adding the index without the include  will take about 1M and still give excellent gains for the test query. By the way, I didn’t know this and am quite shocked to discover this.

Back to the plan details, you can get even more details by right clicking on the plan and selecting Show Execution Plan XML… This isn’t easy to read but does have an important benefit. You can save the xml and send and open it as a graphic execution plan. In 2008, you can right click on the plan and selection Save Execution Plan As… This lets you save the xml as a file with the sqlplan extension. SQL recognises this extension and allow you to open the plan is SSMS. You can then save a plan to allow before and after comparison if you do major changes or want to monitor a query that after a few weeks of production data has hit a database. You could also email the plan to somebody else to look at. This is much more useful than a screen shot of an isolated task from an execution plan.

You can do the same in 2005, bit more difficult but can be done. Haven’t got a copy handy to check the steps but I think you get the XML plan and then copy and paste it into a file with the sqlplan extension.