The definitive work on this by Paul White explains in tremendous detail some surprising facts about the caching of temporary tables and their statistics.
I’m going to summarise some of these points and outline some general recommendations I like my team to follow. This goes a little beyond Paul’s advice, possibly because he thought this corollary too obvious to mention, or because he wanted to retain applicability to OLTP workloads with a high volume of concurrent transactions.
I recently consulted on the migration of some large tables in SQL Server 2008R2. “Statistically significant” errors were made, and hurriedly corrected. Here are some of the lessons learnt.
Yeah, that’s pretty annoying! You’re in a rush to troubleshoot the performance of a stored proc that’s gone rogue, and the tool you are using (something like the SQL statement pictured above) is crashing with this XML error with sys.dm_exec_text_query_plan. You don’t have time to fix the tool properly, so you hack it apart and finally get the query plan text displayed graphically, address the issue and hastily move on to the next burning issue.
I really should get more practice writing blog posts to give me a chance of remembering the torturous process of inserting quite nicely formatted SQL code. I say “quite nicely” because I’ve seen it done better elsewhere, but then again I’ve also seem much worse.
There’s an SSAS Dimension ProcessAdd tuning tip that no one seems to consider, as evidenced in several blog posts and workplaces. I’m not sure if that’s correlation or causation but the line of conversation I’ve witnessed goes like this:
Simon Birch on SQL Server 