Description

Based on my post at bit.ly/TempTables:
When a temporary table in a stored procedure is cached, any statistics created by AutoStats are also cached. This leads to odd effects when a query in the procedure recompiles with statistics cached from a previous execution. In general, the contents of a temporary table change dramatically between executions, so this behaviour is unexpected.
A workaround is to add an explicit UPDATE STATISTICS command to the stored procedure to rebuild the cached statistics. This action successfully replaces the cached statistics, but cached plans within the procedure that reference the temporary table, and found the cached statistics 'interesting', do not recompile as is normally the case.
The demo below shows the two behaviours. With UPDATE STATISTICS commented out, the SELECT plan for the first execution (E) is reused by the second execution (T) resulting in poor performance (and statistics are retained from the E run).
With UPDATE STATISTICS, the statistics are updated to show only (T) values for the second run, but the cached plan (optimized for 'E') does not recompile (reproduces on SQL Server 2005-2012 inclusive):
USE AdventureWorks2012;
GO
CREATE PROCEDURE dbo.Demo
@StartsWith nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Temp
(
ProductID integer NOT NULL,
Name nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL
);
INSERT INTO #Temp
(ProductID, Name)
SELECT
p.ProductID,
p.Name
FROM Production.Product AS p
WHERE
p.Name LIKE @StartsWith + N'%';
--UPDATE STATISTICS #Temp;
SELECT
t.Name,
OrderCount = COUNT_BIG(DISTINCT th.ReferenceOrderID)
FROM #Temp AS t
JOIN Production.TransactionHistory AS th ON
th.ProductID = t.ProductID
GROUP BY
t.Name;
DBCC SHOW_STATISTICS (N'tempdb..#Temp', Name) WITH STAT_HEADER, HISTOGRAM;
DROP TABLE #Temp;
END;
GO
DBCC FREEPROCCACHE;
GO
EXECUTE dbo.Demo @StartsWith = N'E';
GO
EXECUTE dbo.Demo @StartsWith = N'T';
==========
A workaround is to use UPDATE STATISTICS *and* OPTION (RECOMPILE) on the SELECT statement, but this should not be necessary. There does not seem to be much value in caching statistics on the temporary table between executions at all. In addition, new statistics ought to cause the SELECT to recompile without the query hint.

Assign To

Sometimes, I really wish MS stops rolling out so-called "new" versions every year (sql2012, sql2014, sql2016, sql2017 and next sql2018?) with little or very fragile new features, like columnstore index in sql 2012 and even in sql 2014. How about fixing all these "wont fix yet worth fixing" connection items? and just invest your resources on improving the existing features, making them from quality level of 7 to quality level of 9, and prove MS has the gut to fix all the oversight and win the reputation of quality. (Just as early days of Apple products when Steve Jobs was still CEO of Apple, when we think of Apple products, we think of quality and elegance)

Posted by SAinCA on 5/13/2014 at 3:15 PM

Condolences, Paul. After weeks of effort, detailed explanations and on-point suggestions, you get a slammed door (and a lame, "If you have more supporting information and details..." that is a bit beyond belief had the writer consumed your 45 pages before including this pro-forma response) from MS and some less than charitable comments from others on this thread.

I, for one, am VERY grateful for your industrious pursuits and willingness to share knowledge at your expense. OPTION (RECOMPILE) has become my automatic DML inclusion whenever dealing with temp tables and TVs, with, from your work, very good reason - the differences in performance are at times stunning...

Cheers, mate!

Posted by Microsoft on 4/26/2013 at 11:08 AM

Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenarios reported in the bug are not common enough and so unfortunately it does not meet the bar for the current release, also viable workaround is available and sent.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.Gus Apostol, SQL Server Program Manager

Posted by Elatesummer on 9/17/2012 at 5:47 AM

Paul - really nice post and a very intriguing question on why it's currently necessary to use the workaround you found. I look forward to hearing Microsoft's response!

Posted by Paul White NZ on 8/30/2012 at 10:06 AM

Peter,

The complete and best workaround I have today is in my free blog post (the one that took weeks of my own time to research and write).

If you are referring to the end-of-blog invitation to come and hear me speak at the PASS Summit 2012 in Seattle, then you might like to know that it costs me $4000 to attend and I don't get paid a dime. Yes speakers get free entry to the conference but (a) I could get that anyway as a current MVP; and (b) that applies whether anyone turns up to my sessions.

I don't offer any paid training by the way.

Paul

Posted by PeterCape on 8/29/2012 at 9:11 AM

Microsoft, in the above link Paul states 'It is possible that one or more of these behaviors is unintentional, but they have been this way for a very long time, and so are very unlikely to be changed.'Does this mean Microsoft will not fix such bugs?

Paul, do you expect customers to attend your trainings to learn how to avoid such issues?Are you planning more trainings that include such unreported bugs?This looks like a sales pitch to me :(

Posted by Paul White NZ on 8/28/2012 at 4:20 AM

See you there BillHam! And you're welcome :)

Posted by Microsoft on 8/22/2012 at 2:53 PM

Thanks for the feedback Paul. We will get back to you. ThanksVishal

Posted by BillHam on 8/22/2012 at 12:54 PM

Thanks Paul for the 30 page blog exploring the bug & 2nd 15 page blog with further explanation; with many RTs you got my attention.Reading these comments in the blog made me smile: 'I hope you enjoyed this post' Enjoyed the bug? 'If you happen to be attending the PASS Summit 2012, I will be speaking more about these sorts of things in my half day and regular sessions'. Count me in!

Posted by Paul White NZ on 8/21/2012 at 3:07 PM

Hi Paul,

As I said on my blog, it was an email (not a forum thread) from Dave Ballantyne that *eventually* led me to do the research on this. At the time, I explained the behaviour Dave was seeing in another way.

Paul

Posted by PaulJohnson on 8/21/2012 at 11:31 AM

To me, this looks like a trivial case where SQL Server missed performing UPDATE STATISTICS on temp tables and RECOMPILATION.Paul, you mentioned in the link that you came to know about this bug on a forum in 2010. Given that it is easily reproducible <create temptable in a procedure and it did not recompile>, why did it take so long for you to report it in Connect? For important bugs, reporting quickly will improve turnaround time.