Post reply

Upgrade from SQL Server 2008R2 to SQL Server 2016 sp 2 performance issues

  • June 10, 2019 at 2:02 pm

    #3651842

    We have just upgraded our production SQL Server from 2008R2 to 2016 sp 2 and are experiencing some performance issues on our background processes.  We did not change the compatibility levels on our databases so they are still 100. The Legacy Cardinality Estimation is off and so is the query optimizer fixes. We left the compatibility level 100 because we didn't want to incur any issues with the SS2016 compatibility level. If i'm understanding correctly we could change the compatibility level to 130 and turn on the legacy cardinality indicator to get the best bang for our buck. Just wondering if anyone else has experienced this issue.

  • June 10, 2019 at 2:37 pm

    #3651859

    Here is one possibility. Back when we were upgrading servers from 2008 to 2014/2016, we encountered the issue described in the blog post below. Confirm that a SKU licensing misstep during installation has not resulted in some of your CPU cores not being visible to SQL Server.

    https://www.itprotoday.com/sql-server/how-troubleshoot-sql-server-2016-migration

     

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • June 11, 2019 at 10:22 pm

    #3652519

    We were in exact same situation, i will be honest our migration was very painful. We were almost at a point where we wanted to rollback  to 2008R2 but after tweaking and monitoring each setting we were finally able to settle. Below are some key things we had to do to make it stable on 2016.

    Tempdb:

    1.   Changed compatibility mode to 2008, some of the symptoms were queries were just stalled when loading data into temp tables. We also noticed lot more activity on tempdb statistics with 2016 compatibility mode.
    2.  Noticed high pfs/sgam wait types, had to split tempdb into more number of files.
    3.   Disable indirect tempdb checkpoint https://blogs.msdn.microsoft.com/sql_server_team/indirect-checkpoint-and-tempdb-the-good-the-bad-and-the-non-yielding-scheduler/ . Noticed stack dumps in error logs folder, this fixed our issue.

    User Databases:

    1. Set compatibility to 2008r2, enabled legacy cardinality and enable query store with read only with cap of 4 GB.
    2. On 2016 algorithm for auto stat is different, sampling rate is much lower so for larger tables this may be an issue where stats are being updated more frequently. Enabled at db level, disabled at the big tables.

    I should also say that 2016 has had the most number of errors/bugs in there CU's than any other version in past.

Viewing 3 posts - 1 through 2 (of 2 total)

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