July 4, 2017 at 7:00 am
HI All,
I am currently in a situation whereby i have migrated 2 databases from SQL Server 2008 to SQL Server 2016 on an AZURE VM.
Originally i had upgraded the db's to be compatible with 2016 but performance of a single job regressed so much that i restored the databases again to the original compatibility mode 100.
When the job was on the old server it rab in less than 5 minutes. when i moved the databases to 2016 comp mode the job took over 1 hour.
after i restored the database on the 2016 server to comp mode 100 the job takes 30 mins.
i put the hour long duration down to new features in 2016 such as cardinality enumerator however i cannot figure out why the job is now taking 6 times longer even when in its original comp mode.
has anyone encountered this issue before and what have they done to resolve? any information would be greatly appreciated.
Regards,
Niall
July 4, 2017 at 7:48 am
niall5098 - Tuesday, July 4, 2017 6:59 AMHI All,I am currently in a situation whereby i have migrated 2 databases from SQL Server 2008 to SQL Server 2016 on an AZURE VM.
Originally i had upgraded the db's to be compatible with 2016 but performance of a single job regressed so much that i restored the databases again to the original compatibility mode 100.
When the job was on the old server it rab in less than 5 minutes. when i moved the databases to 2016 comp mode the job took over 1 hour.after i restored the database on the 2016 server to comp mode 100 the job takes 30 mins.
i put the hour long duration down to new features in 2016 such as cardinality enumerator however i cannot figure out why the job is now taking 6 times longer even when in its original comp mode.
has anyone encountered this issue before and what have they done to resolve? any information would be greatly appreciated.Regards,
Niall
Are all of the other migrated jobs running faster, slower or roughly at the same speed as before?
What does this particular job do?
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
July 4, 2017 at 8:11 am
Thanks for responding.
all other jobs perform the same if not slightly better.
this particular calls a stored procedure that basically does a massive select into from a view.
I've had a look at the SPROC syntax and can't see any reference to deprecated datatypes etc
July 4, 2017 at 8:21 am
niall5098 - Tuesday, July 4, 2017 8:11 AMThanks for responding.all other jobs perform the same if not slightly better.
this particular calls a stored procedure that basically does a massive select into from a view.
I've had a look at the SPROC syntax and can't see any reference to deprecated datatypes etc
Have you had a go at getting an actual execution plan for the view & checking for optimisations?
Have you rebuilt stats?
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
July 4, 2017 at 8:24 am
Phil Parkin - Tuesday, July 4, 2017 8:21 AMniall5098 - Tuesday, July 4, 2017 8:11 AMThanks for responding.all other jobs perform the same if not slightly better.
this particular calls a stored procedure that basically does a massive select into from a view.
I've had a look at the SPROC syntax and can't see any reference to deprecated datatypes etc
Have you had a go at getting an actual execution plan for the view & checking for optimisations?
Have you rebuilt stats?
HI,
I did rebuild indexes and updated stats.
now, i don't have access to the previous environment to view the previous execution plan.
i have looked at the query store and forced recommended plans but to no avail.
July 4, 2017 at 8:33 am
If you are happy to post the plan here as a .sqlplan attachment, someone here may be able to help you (but bear in mind the date ... not many US nationals online today).
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
July 4, 2017 at 9:11 am
Even without all the optimizer changes and effects from the new CE in play (once you're on compatibility level 100), there are a lot of ways a particular query might execute differently on a new instance on a new machine.
What were the specs (CPU, storage, RAM, etc.) of the old server, and what Azure VM size are you using for the new one, with what kind of storage?
Without being able to compare anything at the SQL Server layer (wait stats, execution plans, etc.) to the old environment this will basically just be a case of tuning the existing code from scratch.
As Phil said, if you could post the execution plan (preferably the actual execution plan, if you're willing to wait the 30 minutes; it's a lot more helpful than the estimated plan), the code the job calls, along with the definition of the objects referenced (especially the view), then we might be able to give some pointers.
Cheers!
July 10, 2017 at 2:55 pm
Can you try to tweak the database level setting "Use legacy cardinality estimator" and then check the results ?
In our environment it has helped at couple of places where the execution improved after changing this setting
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply