June 25, 2018 at 6:33 am
Hi,
We changed the compatibility mode from 100 (2008) to 110 (2012) on a 2012 Enterprise SQL server last week.
Since then we have found that the performance on a stored procedure has nose dived from 18 minutes to 48 hours (before we killed it).
I changed the compatibility mode back to 100 and the sproc runs in 18 mins again. Comparing plans from before and after, the 110 version has parallelism at every step of the way, the 100 version has none whatsoever. Other stored procedures (about 50) have been running at the normal speed.
This is totally new ground for me, why would changing the compatibility mode from 100 to 110 cause a huge increase in parallelism in just one stored procedure?
Cheers,
Lins
July 10, 2018 at 10:30 am
lindsayscott23 - Monday, June 25, 2018 6:33 AMHi,
We changed the compatibility mode from 100 (2008) to 110 (2012) on a 2012 Enterprise SQL server last week.
Since then we have found that the performance on a stored procedure has nose dived from 18 minutes to 48 hours (before we killed it).
I changed the compatibility mode back to 100 and the sproc runs in 18 mins again. Comparing plans from before and after, the 110 version has parallelism at every step of the way, the 100 version has none whatsoever. Other stored procedures (about 50) have been running at the normal speed.
This is totally new ground for me, why would changing the compatibility mode from 100 to 110 cause a huge increase in parallelism in just one stored procedure?
Cheers,
Lins
Could be quite a few different things. The optimizer may be better at making use of parallelism, but for this query, that's a problem. You can add OPTION (MAXDOP 1) to stop that from happening as a specific fix for that query. However, you should probably consider updating the statistics to see what effect that has, but only after having a backup taken and ready to restore on a moment's notice.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2018 at 10:48 am
In addition to updating the stats, I would check the cost threshold for parallelism. If it's at the default, it's almost guaranteed to be incorrect.
Sue
July 11, 2018 at 6:33 am
Sue_H - Tuesday, July 10, 2018 10:48 AMIn addition to updating the stats, I would check the cost threshold for parallelism. If it's at the default, it's almost guaranteed to be incorrect.
Sue
Yep, and I also wonder if the default value for it changed between the 2008 and 2012 versions of SQL Server...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2018 at 7:14 am
sgmunson - Wednesday, July 11, 2018 6:33 AMSue_H - Tuesday, July 10, 2018 10:48 AMIn addition to updating the stats, I would check the cost threshold for parallelism. If it's at the default, it's almost guaranteed to be incorrect.
Sue
Yep, and I also wonder if the default value for it changed between the 2008 and 2012 versions of SQL Server...
It's been stuck with a default at 5 since the 90's. As if nothing has changed with systems since then. No idea why they never changed it.
Sue
July 11, 2018 at 11:33 am
Sue_H - Wednesday, July 11, 2018 7:14 AMsgmunson - Wednesday, July 11, 2018 6:33 AMSue_H - Tuesday, July 10, 2018 10:48 AMIn addition to updating the stats, I would check the cost threshold for parallelism. If it's at the default, it's almost guaranteed to be incorrect.
Sue
Yep, and I also wonder if the default value for it changed between the 2008 and 2012 versions of SQL Server...
It's been stuck with a default at 5 since the 90's. As if nothing has changed with systems since then. No idea why they never changed it.
Sue
Not actually surprised... good ole Microsoft... turning bugs into features since 1981...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply