Statistics creation showing in query text with temp table inserts

sqldriver 2014-10-27 16:35:57

I only noticed this because the query almost bonked a production instance this weekend by filling up the tempdb drive.

Has anyone else ever seen statistics getting created as part of the query text, when inserting to temp tables? I've never seen it before and can't find another instance of it happening. There are no custom stats being created as part of the query.

Thanks

AaronBertrand 2014-10-27 17:53:50
These are auto-create or auto-update statistics – happening on the table you are selecting from, not the #temp table. You can see that by scrolling over, eventually you'll see that the stats are being created or updated against this permanent table, for example:

... FROM [dbo].[tbl_email_metadata] ...

Plan Explorer shows these operations because you triggered them (Management Studio will not, nor would Plan Explorer if you generated this plan in SSMS or pulled it from the plan cache).

sqldriver 2014-10-27 18:02:42
I'm confused; I pulled these out of plan cache via the Top SQL section of Performance Dashboard (unless I'm wrong about where Top SQL pulls from?). I incorrectly assumed that SQL was creating the statistics on the temp table from the root table, I guess.
AaronBertrand 2014-10-27 18:03:35
Yes, sorry, I thought you generated this from Plan Explorer, but yes Performance Advisor will capture these along with Top SQL, too.
sqldriver 2014-10-27 19:51:07
Cool, thank you.
nhmidi 2014-10-28 07:05:55
Although there is a parameter "optimize for ad hoc workloads". This parameter has the effect of creating a stub plane instead of the actual execution plan. It is only the second call to the same query that SQL Server will remove the stub from the cache and replace it with the real plan.
nhmidi 2014-10-28 07:09:13
Test this server parameter:

sp_configure 'optimize for ad hoc workloads', 1
go
reconfigure with override;
go