I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire execution plan here. I have made the effort to make the table and column names in the query and execution plan generic both for brevity and to protect my company's IP. The execution plan can be opened with SQL Sentry Plan Explorer.

I have done a fair amount of T-SQL, but using execution plans to optimise my query is a new area for me and I have really tried to understand how to do it. So, if anyone could help me with this and explain how this execution plan can be deciphered to find ways in the query to optimise it, I would be eternally grateful. I have many more queries to optimise - I just need a springboard to help me with this first one.

This is the query:

DECLARE @Param0 DATETIME     = '2013-07-29';
DECLARE @Param1 INT          = CONVERT(INT, CONVERT(VARCHAR, @Param0, 112))
DECLARE @Param2 VARCHAR(50)  = 'ABC';
DECLARE @Param3 VARCHAR(100) = 'DEF';
DECLARE @Param4 VARCHAR(50)  = 'XYZ';
DECLARE @Param6 VARCHAR(50)  = 'Text3';


    B_Var1_PK int,
    Job_Var1 varchar(512),
    Job_Var2 varchar(50)

INSERT INTO @MyTableVar (B_Var1_PK, Job_Var1, Job_Var2) 
SELECT B_Var1_PK, Job_Var1, Job_Var2 FROM [fn_GetJobs] (@Param1, @Param2, @Param3, @Param4, @Param6);

    TTVar2_LK VARCHAR(100),
    TTVar3_LK VARCHAR(50),
    TTVar4_LK INT,
    TTVar5 VARCHAR(20)

    MyTable1 T
    INNER JOIN feeds.MyTable2 A ON A.T2_Var1 = T.T1_Var4_LK
    INNER JOIN @MyTableVar B ON B.Job_Var2 = A.T2_Var2 AND B.Job_Var1 = A.T2_Var3
GROUP BY T.T1_PK, T.T1_Var1_LK, T.T1_Var2_LK, T.T1_Var4_LK

-- This is the slow statement...
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    SUM(CONVERT(DECIMAL(18,4), A.A_Var1) + CONVERT(DECIMAL(18,4), A.A_Var2))
FROM #TempTable T
    INNER JOIN @MyTableVar     B ON B.B_Var1_PK     = A.Job
    INNER JOIN TableC (NOLOCK) C ON C.C_Var2_PK     = A.A_Var5_FK_LK
    INNER JOIN TableD (NOLOCK) D ON D.D_Var1_PK     = A.A_Var6_FK_LK
    INNER JOIN TableE (NOLOCK) E ON E.E_Var1_PK     = A.A_Var7_FK_LK  
    LEFT OUTER JOIN feeds.TableF (NOLOCK) F ON F.F_Var1 = T.TTVar5
WHERE A.A_Var8_FK_LK = @Param1
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    CASE E.E_Var1_LK 
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5

IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable

What I have found is that the third statement (commented as being slow) is the part that is taking the most time. The two statements before return almost instantly.

The execution plan is available as XML at this link.

Better to right-click and save and then open in SQL Sentry Plan Explorer or some other viewing software rather than opening in your browser.

If you need any more information from me about the tables or data, please don't hesitate to ask.

  • 2
    Your statistics are way off. When is the last time you de-fragmented indexes or updated stats? Also, I would try to use a temp table, instead of the table variable, @MyTableVar ,as the optimizer really can't use stats on table variables. Commented Aug 2, 2013 at 20:58
  • Thanks for your reply Adam. Changing the @MyTableVar to a temp table doesn't have any effect, but it's only a small number of rows (which can be seen from the execution plan). What in the execution plan shows that my stats are way off? Does it indicate which indexes should be reorganised or rebuilt, and which table(s) should have stats updated?
    – Neo
    Commented Aug 2, 2013 at 21:12
  • 3
    That hash join at the bottom right has an estimated 24,000 rows in the build input but actual 3,285,620 so may well be spilling to tempdb. i.e. the estimates for the rows resulting from the join between TableA and @MyTableVar are way off. Also the number of rows going into the sorts are much greater than estimated so they could well be spilling as well. Commented Aug 2, 2013 at 21:44

2 Answers 2


Before getting to the main answer, there are two pieces of software you need to update.

Required Software Updates

The first is SQL Server. You are running SQL Server 2008 Service Pack 1 (build 2531). You ought to be patched up to at least the current Service Pack (SQL Server 2008 Service Pack 3 - build 5500). The most recent build of SQL Server 2008 at the time of writing is Service Pack 3, Cumulative Update 12 (build 5844).

The second piece of software is SQL Sentry Plan Explorer. The latest versions have significant new features and fixes, including the ability to directly upload a query plan for expert analysis (no need to paste XML anywhere!)

Query Plan Analysis

The cardinality estimate for the table variable is exactly right, thanks to a statement-level recompilation:

table variable estimate

Unfortunately, table variables do not maintain distribution statistics, so all the optimizer knows is that there are six rows; it knows nothing of the values that might be in those six rows. This information is crucial given that the next operation is a join to another table. The cardinality estimate from that join is based on a wild guess by the optimizer:

first join estimate

From that point on, the plan chosen by the optimizer is based on incorrect information, so it's no wonder really that performance is so poor. In particular, the memory set aside for sorts and hash tables for hash joins will be far too small. At execution time, the overflowing sorts and hashing operations will be spilled out to physical tempdb disk.

SQL Server 2008 does not highlight this in execution plans; you can monitor the spills using Extended Events or Profiler Sort Warnings and Hash Warnings. Memory is reserved for sorts and hashes based on cardinality estimates before execution starts, and cannot be increased during execution regardless of how much spare memory your SQL Server may have. Accurate row count estimates are therefore crucial for any execution plan that involves workspace memory consuming operations.

Your query is also parameterized. You should consider adding OPTION (RECOMPILE) to the query if different parameter values affect the query plan. You should probably consider using it anyway, so the optimizer can see the value of @Param1 at compilation time. If nothing else, this may help the optimizer produce a more reasonable estimate for the index seek shown above, given that the table is very large, and partitioned. It may also enable static partition elimination.

Try the query again with a temporary table instead of the table variable and OPTION (RECOMPILE). You should also try materializing the result of the first join into another temporary table, and run the rest of the query against that. The number of rows is not all that large (3,285,620) so this should be reasonably quick. The optimizer will then have an exact cardinality estimate and distribution statistics for the result of the join. With luck, the rest of the plan will fall into place nicely.

Working from the properties shown in the plan, the materializing query would be:

INTO #AnotherTempTable
FROM @MyTableVar AS B
    ON A.Job = B.B_Var1_PK
    A_Var8_FK_LK = @Param1;

You could also INSERT into a predefined temporary table (the correct data types are not shown in the plan, so I cannot do that part). The new temporary table may or may not benefit from clustered and nonclustered indexes.

  • Thanks so much for this in-depth answer. Sorry it's taken a week to reply - I've been working on this every day interspersed with other work. I've implemented your suggestions materialising the join to TableA into #AnotherTempTable. This seemed to have the best impact - the other suggestions (using a temp table instead of a table variable for @MyTableVar, and using OPTION (RECOMPILE) didn't have much effect or any at all. The 'Anonymize' and 'Post to SQLPerformance.com' options in SQL Sentry Plan Explorer are great - I have just used them: answers.sqlperformance.com/questions/1087
    – Neo
    Commented Aug 9, 2013 at 10:35

I notice there should be a PK on @MyTableVar and agree that #MyTableVar is often better performing (esp. with larger number of rows).

The condition within the where clause

   WHERE A.A_Var8_FK_LK = @Param1

should be moved to inner join A AND'ed. The optimizer is not clever enough in my experience to do this (sorry didn't look at plan) and it can make a huge difference.

If those changes do not show improvement I would next create another temp table of A and all the things it joins to constrained (nicely?) by A.A_Var8_FK_LK = @Param1 if that grouping makes logical sense to you.

Then create a clustered index on that temp table (either before or after creation) for the next join condition.

Then join that result to the few tables (F and T) that remain.

Bam, who needs a stinking query plan when the row estimates are off and are sometimes not easily improvable anyway). I'm assuming you have proper indices which is what I would first check within the plan though.

A trace can show the tempdb spills which may or may not have a drastic impact.

Another alternative approach--that is faster to try out at least--is to order the tables from lowest number of rows (A) to highest and then begin adding the merge, hash, and loop to the joins. When hints are present the join order is fixed as specified. Other users wisely avoid this approach because it can hurt in the long run if the relative row counts change dramatically. A minimum number of hints is desirable.

If you're doing many of these, perhaps an commercial optimizer is worth a try (or trial) and is still a good learning experience.

  • Yes it is. It ensures the rows returned by A are limited by the constraint. Otherwise the optimizer may join first and apply the constraint later. I deal with this one daily.
    – crokusek
    Commented Aug 3, 2013 at 8:37
  • 5
    @crokusek You are just wrong. SQL-Server's optimizer is pretty good at knowing the queries are equivalent (whether a condition is at WHERE or the ON clause) when it's an INNER join. Commented Aug 3, 2013 at 9:05
  • 7
    You might find Paul White's series on the Query Optimiser useful. Commented Aug 3, 2013 at 9:06
  • Its a terrible habit. Maybe it will for this particular case (where there is one constraint) but I'm coming from the land of multiple developers piling on AND conditions at the where clause. SQL Server does not consistently "move" them back to the join for you.
    – crokusek
    Commented Aug 3, 2013 at 9:23
  • Agree incorrect for outer (and right joins). But when there are only AND'd expressions within a where clause and each term corresponds solely to an specific inner join, that term may safely and confidently be moved to the "on" location as a optimization and best practice (imo). Whether it is a "true" joining condition or just a fixed constraint is secondary to a large performance gain. That link is for a trivial case. Real life has multiple where conditions with convert()'s and math and such making them better candidates from which to derive best practices.
    – crokusek
    Commented Aug 3, 2013 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.