SQL Server uplift from 2008R2 enterprise edition to SQL Server 2016 enterprise

  • 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.

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

  • oogibah wrote:

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin wrote:

    oogibah wrote:

    There is also a traceflag/command for using the old carnality estimator  .

    Don't remember reading about that in BOL!

     

    ah crap, they are gonna kick me out of shadow IT

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

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