June 5, 2019 at 7:35 pm
We did a side-by-side install of SQL Server 2016 from SQL Server 2008r. Now we are having some execution timeouts on our backend processes(not sql server agent). I've already check the statistics and they look fine. Any info would be greatly appreciated.
June 5, 2019 at 8:00 pm
Did you do your research into the new cardinality estimator? See here, for example.
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
June 5, 2019 at 8:47 pm
There is also a traceflag/command for using the old carnality estimator
TraceFlag 9481
Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 (11.x) and earlier versions, irrespective of the compatibility level of the database. For more information, see Microsoft Support article.
Starting with SQL Server 2016 (13.x), to accomplish this at the database level, see the LEGACY_CARDINALITY_ESTIMATION option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
Starting with SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'FORCE_LEGACY_CARDINALITY_ESTIMATION' query hint instead of using this trace flag.
June 5, 2019 at 9:07 pm
There is also a traceflag/command for using the old carnality estimator .
Don't remember reading about that in BOL!
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply