Compat Level change and Impact on Query Performance

Question

Compat Level change and Impact on Query Performance

sqlguy80

SSCrazy

Points: 2765
More actions
March 6, 2020 at 6:45 pm

#3731994

Hello DBA Gurus,
We have a prod SQL Server Instance with a critical Database on SQL Server 2014, but Compat Level on SQL Server 2012 ( Compat Level 110) . It was probably set in that level, way back by someone so the workloads, queries etc can be tested.One morning there were performance issues on the App view etc was loading slow, and when I looked at it, I saw compat level 110 but testing and val boxes were all at Compat Level 120( SQL Server 2014) . I went ahead and updated the Compat Level to SQL Server2014 ( Compat Level 120) and things loaded really faster since then. I googled and didn't find any detailed explanation how and why that change can improve query performance. Any insights / explanation please?

Thanks in Advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Microsoft introduced the new Cardinality Estimator in the SQL Server engine in 2014. If the database was set to an older compatibility mode, it was still using the old cardinality rules. Considering your testing boxes were at the newer compatibility level, it sounds like someone wanted to do thorough testing before switching the production environment to the new cardinality rules. While many queries benefited from the new rules, some performed worse, so testing was important.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver15

https://www.brentozar.com/archive/2014/04/sql-2014-cardinality-estimator-eats-bad-tsql-breakfast/

sqlguy80 SSCrazy Points: 2765 More actions · Answer 2

Thank you for the response, but need explanation for this problem / solution.

CPU looked ok on the VM ( ~ 30% ) . but view was opening ok till Friday, On Monday afternoon I was informed the view is really slow and is not populating ..Compat Level was at 110, Once I changed it to 120 it worked fast..

How can it suddenly start working after changing to 120 when it was working ok with 110 till friday, that's what the business needs to know, Found no detailed explanation online.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

sqlguy80 wrote:

Thank you for the response, but need explanation for this problem / solution.
CPU looked ok on the VM ( ~ 30% ) . but view was opening ok till Friday, On Monday afternoon I was informed the view is really slow and is not populating ..Compat Level was at 110, Once I changed it to 120 it worked fast..
How can it suddenly start working after changing to 120 when it was working ok with 110 till friday, that's what the business needs to know, Found no detailed explanation online.

I'm not sure but I believe that changing the compatibility level causes everything to recompile and you may have needed to simply do a recompile because of the bad form of parameter sniffing.

As a bit of a sidebar, rebuilding stats a lot more than you might think you need to can actually help quite a bit.

--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)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Take a look at the execution plans when it's compiling in each compatibility mode to understand how the behavior is changing.

"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