July 13, 2017 at 6:43 am
I've got a query that just selects from one database into another.
It goes nothing clever , so I'm struggling with why sp_whoisactive reports tempdb_allocations for it.
It literally is,
INSERT INTO TargetDB.schema.table (columnlist) SELECT columnlist FROM schema.table
Everything matches, they are tables not views, no ORDERing is going on. The source table does have 2 persisted computed columns however.
It could be fine, I just want to understand why tempdb is involved.
Thanks
R
July 13, 2017 at 7:05 am
Perhaps your statistics are out of date and so you're getting an inappropriate memory grant, causing spillage to tempdb?
John
July 13, 2017 at 7:33 am
John Mitchell-245523 - Thursday, July 13, 2017 7:05 AMPerhaps your statistics are out of date and so you're getting an inappropriate memory grant, causing spillage to tempdb?John
The query plan is the first place I'd look for confirmation. Look for a spill warning.
July 13, 2017 at 8:01 am
i've used sp_whoisactive to get the query plan.
it does show a table spool costed at 36% to support an Index Insert so that must be my culprit.
July 13, 2017 at 11:05 am
r5d4 - Thursday, July 13, 2017 8:01 AMit does show a table spool costed at 36% to support an Index Insert so that must be my culprit.
Yup. Table spool = temp work table and it's stored in tempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply