July 26, 2016 at 7:37 am
Not too long ago we migrated some 2008 databases to 2014, but the compatibility level remained set at 2008 (100). I recently changed those to 2014 (120) hoping to take advantage of the improved query optimizer and since we were no longer going to use any deprecated features. After the change there have been some processes that randomly take much longer than they used to prior changing the compatibility level. It's not always the same process and a very large majority of the processes seem to not be impacted. I should also point at that the runtimes of the processes were very consistent prior to the change. I wouldn't think anything of it if the first run after the change took longer, but some ran normal for multiple days, take a long time to run one day, and then returned to normal the next. My question is were statistics wiped out upon changing the compatibility level? What else could the change of compatibility level have done that I'm not thinking of? We currently check for index fragmentation every night and reorganize/rebuild accordingly but do not do anything with recompiling statistics (this might lead to another discussion) other than setting them to update automatically.
July 26, 2016 at 7:54 am
RonMexico (7/26/2016)
Not too long ago we migrated some 2008 databases to 2014, but the compatibility level remained set at 2008 (100). I recently changed those to 2014 (120) hoping to take advantage of the improved query optimizer and since we were no longer going to use any deprecated features. After the change there have been some processes that randomly take much longer than they used to prior changing the compatibility level. It's not always the same process and a very large majority of the processes seem to not be impacted. I should also point at that the runtimes of the processes were very consistent prior to the change. I wouldn't think anything of it if the first run after the change took longer, but some ran normal for multiple days, take a long time to run one day, and then returned to normal the next. My question is were statistics wiped out upon changing the compatibility level? What else could the change of compatibility level have done that I'm not thinking of? We currently check for index fragmentation every night and reorganize/rebuild accordingly but do not do anything with recompiling statistics (this might lead to another discussion) other than setting them to update automatically.
In 2014, a new Cardinality Estimator[/url] was introduced. By altering a database's compatibility level, you are (cough) taking advantage of it.
As you've found, it's not always an improvement on what was there before, but at least now you know what you need to research.
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 26, 2016 at 8:07 am
It'll be the new cardinality estimator. Most queries improve in performance or stay the same, a small number typically experience performance regressions.
I've seen 3 clients upgrade to 2014. One did performance testing upfront and the upgrade went according to plan and they got roughly a 35% improvement in throughput afterwards. The other two didn't test and ended up fighting with performance problems afterwards. I've just finished doing the 'pre-upgrade' performance tests for one of them.
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
July 26, 2016 at 8:42 am
Thanks! That seems like exactly what I have going on. I'll play around with a few things and probably take a look at the recompile query option since one particular process that took long today uses variables in a where clause to define a date range.
July 26, 2016 at 8:57 am
Best practice is to set the database in a single user mode when changing the compatibility level to prevent cross level compilation and incorrect / bad plans being cached. Changing the compatibility level causes all procedures to be automatically recompiled.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply