Query fills tempdb after upgrade from 2005 to 2012

Question

Query fills tempdb after upgrade from 2005 to 2012

jamie_collins

Ten Centuries

Points: 1192
More actions
February 7, 2017 at 7:57 pm

#319284

Just a quick post before I go to bed for the night...
We migrated one of our databases from 2005 to 2012 and since then a query that runs on 2005 with no issues fills up tempdb.
On 2005 uses about 6GB in temodb and on the new server fills up 400GB of tempdb in about 30 minutes.
I don't have time to post code tonight but thought I would see if anything that I may have missed between versions that might stand out for you guys?
TIA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) Upgrade all stats with full scan? I am pretty sure this is old information from prior upgrades, but still could be applicable for that level of jump.

2) What is the estimated query plan? How does that compare to the old server's plan?

3) Use sp_whoisactive to see what is going on in real time, including grabbing the query plan.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Definitely post the query. If you can post the actual execution plan as an SQLPlan attachment, that would help, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jamie_collins Ten Centuries Points: 1192 More actions · Answer 3

Sorry I didn't get a chance to update but I've managed to resolve the issue.
This was fixed by creating stats on the new database for every table and then updating them after.

exec sp_createstats
exec sp_updatestats

Not sure why but maybe there is something different about how the optimizer works between the 2 versions.

Thanks for the replies.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

jamie_collins - Tuesday, February 14, 2017 2:46 PM
Sorry I didn't get a chance to update but I've managed to resolve the issue.
This was fixed by creating stats on the new database for every table and then updating them after.

exec sp_createstats
exec sp_updatestats
Not sure why but maybe there is something different about how the optimizer works between the 2 versions.
Thanks for the replies.

WOW that second statement scares the BAJEEZUS out of me!! Can you please describe exactly what you did? And add in why it was necessary since you had upgraded an existing database that should have (by default anyway) already had all the statistics it needed.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

TheSQLGuru - Tuesday, February 14, 2017 5:16 PM
jamie_collins - Tuesday, February 14, 2017 2:46 PM
Sorry I didn't get a chance to update but I've managed to resolve the issue.
This was fixed by creating stats on the new database for every table and then updating them after.

exec sp_createstats
exec sp_updatestats
Not sure why but maybe there is something different about how the optimizer works between the 2 versions.
Thanks for the replies.
WOW that second statement scares the BAJEEZUS out of me!! Can you please describe exactly what you did? And add in why it was necessary since you had upgraded an existing database that should have (by default anyway) already had all the statistics it needed.

+1000 I'll second that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jamie_collins Ten Centuries Points: 1192 More actions · Answer 6

I agree.
Loaded the 2005 database into the 2012 instance.
Ran updatestats with full scan on 2012 instance.
tempdb filled up. (capped at 100GB)
For shi$s and giggles I set autogrow to max 500GB.
Ran it again and tempdb filled up.
I compared the showplans between the old and new and the new one was using different indexes on one of the pieces...
I updated stats on both databases with full scan.. same result. Worked on 2005 not on 2012.
I created stats on the 2012 instance and re-ran... same result. Worked on 2005 not on 2012.
I then ran the update stats on the 2012 instance and it worked.

As it sits now, I have no idea why it behaved this way.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

What stats did you create?

Why did you decide to create those particular ones?

Is auto-create statistics OFF on either or both servers?

More important than the indexes used for this conversation now is the differences between actual and estimated rows in the good and bad plans - and deciphering exactly why the (anticipated) huge differences occurred.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service