SQL Server 2016 (130) compatibility level

David Frederick

The Tessitura SQL Server 2016 set up by the Network for LSC on RAMP is compatibility level 130.  I'm using Tessitura v15.0.4.

Based on.

USE impresario;

GO

SELECT compatibility_level 

FROM sys.databases WHERE name = 'impresario';

GO

Hope that helps.

Yes, thanks Tom! That is helpful.

We are stuck at 2012 levels. The new Cardinality Estimator (CE) does not work well with some of our older procedures/functions. Since we are self hosted, this project will be long. Everything custom will need to be tested with queryhints or DBCC hints. 

Hi Troy - Microsoft added a database option called Legacy Cardinality Estimation in 2016. If you set that to ON, SQL Server uses the old cardinality estimator even if you are at 120 or 130 compatibility levels. We are using 2014 (120) compatibility level with this turned on, and it resolved the issues we had with the new cardinality estimator. I'm hoping to be able to change that at some point down-the-road. 

Good to know. We haven't upgraded to 2016 yet. Thanks.

We are locally hosted, and running Tess v15.0.5 on SQL Server v16 with no issues. According to support, for v15 "SQL Server 2012, 2014, or 2016 Standard or Enterprise, with current Service Pack will work".

HI Chris - the question wasn't about SQL Server version, but the database compatibility level. For example, you can be running SQL Server 2016, but have a database set to SQL Server 2014 (120) compatibility level (which is what we are currently using). You can find this value in database properties in the Options section labeled, "Compatibility level". You can also find it via this script:

SELECT compatibility_level
FROM sys.databases WHERE name = 'impresario';

Thanks,
David

David Frederick said:

the question wasn't about SQL Server version, but the database compatibility level.

I understand; we're running SS 2016 compatibility level, a.k.a. 130, as well.

Thanks, Chris. That's good to know. We'll probably shift our test environments to 130 just to confirm nothing weird happens and then shift live over.

Hi All,

Just wanted to say that in Development we have seen cases in standard code where the new cardinality estimator causes a noticeable performance hit.  In looking at this we find that if we change the code then we often cause the same issue in the older CE.  Since we have multiple SQL versions running in the field this causes an obvious problem (lots of similar comments show up online).  If you find that you are impacted by increasing the compatibility level, our recommendation for now is to set the new Legacy Cardinality Estimation option that David mentioned and then you should be ok to raise the compatibility level.  A little farther down the road when we make SQL 2016 the minimum supported version we will make the needed code changes so new CE can be used.

Ron