August 30, 2015 at 12:22 am
An Stored proc which was running in SQL 2008 for 6hrs, after upgrade to 2012 takes 12 to 13hrs to complete.
Inside the proc, an cursor is created and 300K records are passed into it. Then, each record is updated.
Even if cursors/query are not ideally written, it should not impact perforamnce after upgrade. I thought the first execution after upgrade would take time due to first time execution pan creation. But its been third time, and now too it takes 13hrs.
So, recompile option may not work here. since its already running late and recompile gonna increase duration.
Any help?
August 30, 2015 at 12:41 am
balasach82 (8/30/2015)
An Stored proc which was running in SQL 2008 for 6hrs, after upgrade to 2012 takes 12 to 13hrs to complete.Inside the proc, an cursor is created and 300K records are passed into it. Then, each record is updated.
Even if cursors/query are not ideally written, it should not impact perforamnce after upgrade. I thought the first execution after upgrade would take time due to first time execution pan creation. But its been third time, and now too it takes 13hrs.
So, recompile option may not work here. since its already running late and recompile gonna increase duration.
Any help?
Quick questions, as we need more information in order to answer this, are the statistics up to date? Are indices regularly maintained? Was the upgrade a straight upgrade on the same hardware? Are the server configurations the same as before the upgrade?
😎
Recompile is not going to hurt for anything that is running in minutes let alone hours, adding a fraction of a second cannot be considered to be an increase in duration in this case.
Looks to me that the problem is more the procedure itself, 6 hours for 300000 rows is a very very long time indeed, roughly 75ms p.row.
August 30, 2015 at 1:09 am
Its a new Win 2008 R2 box with 128 GB ram. SQL is allocated about 4 GB (min) max (61 GB). Thsi configuratin is much higher than what we had in SQL 2008 (win 2003).
Yes, stats are uptodate. There are 1 or 2 indexes only which doesnt have much frag.
August 30, 2015 at 2:52 am
balasach82 (8/30/2015)
Its a new Win 2008 R2 box with 128 GB ram. SQL is allocated about 4 GB (min) max (61 GB). Thsi configuratin is much higher than what we had in SQL 2008 (win 2003).Yes, stats are uptodate. There are 1 or 2 indexes only which doesnt have much frag.
Canyou post the actual execution plan?
😎
August 30, 2015 at 5:09 am
Eirikur Eiriksson (8/30/2015)
balasach82 (8/30/2015)
Its a new Win 2008 R2 box with 128 GB ram. SQL is allocated about 4 GB (min) max (61 GB). Thsi configuratin is much higher than what we had in SQL 2008 (win 2003).Yes, stats are uptodate. There are 1 or 2 indexes only which doesnt have much frag.
Canyou post the actual execution plan?
😎
One from the old instance and one from the new would be useful.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 31, 2015 at 6:46 am
I have seen this occur. It's usually in queries that were problematic, but barely adequate, in the older version of SQL Server. The newer optimizer in 2012 just isn't always very forgiving of poor code choices. Many of the suggestions around statistics and indexes are good, but it's probably going to require fundamental query tuning and a rewrite to address whatever in the code is causing issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply