SQL-2016 Query Timeouts/Long execution

  • GonnaCatchIT

    SSCrazy

    Points: 2774

    Dear All,

    I have just migrated from SQL-2008 to SQL-2016.

    I see some of our queries get timed out ,take too long for execution.

    The same queries are quick in SQL-2008.

    I have tried executing them several times on SQL-2016 and execution plan doesn't help much.I don't want directly go to re-writing the queries as it would be too bigger task.

    What aspects should I check on SQL-2016?

    1. SQL Instance memory is about 135 gb ( same as SQL-2008)

    2.  SQL-2016 is TDE Enabled & SSL configured.

    Sample query:

    exec sp_executesql N'WITH Query AS ( SELECT MS.*, ROW_NUMBER() OVER( ORDER BY D_DT DESC ) AS RowNumber

    FROM ( SELECT DISTINCT Col1,col2

    FROM ( SELECT * FROM View WHERE DID = LTRIM(RTRIM(@DID)) and Type <> ''Fruit''

    AND T_DT >= LTRIM(RTRIM(@FROM_DATE)) AND TRANSACTION_DT <= LTRIM(RTRIM(@TO_DATE))

    AND Types IN (SELECT types FROM fruitypes ) ) TPYT INNER JOIN Veggies NPC ON TPYT.typeid = NPC.typeid

    WHERE TPYT.T_DT >= LTRIM(RTRIM(@FROM_DATE)) AND TPYT.T_DT <= LTRIM(RTRIM(@TO_DATE)) AND TPYT.DID = LTRIM(RTRIM(@DID))

    AND ( FTYPES IN (''0100'',''0200'') OR VTYPES IN (''200'',''400''))

    AND TPYT.Org IN ('' Fresh '' , ''Veg '') ) MS ) SELECT *, (SELECT COUNT(1) FROM Query)AS TOTALCOUNT FROM Query

    WHERE ROWNUMBER >= 1 AND ROWNUMBER <= 10 ORDER BY ROWNUMBER ASC; ',N'

    @TO_DATE datetime,@FROM_DATE datetime,@DID nvarchar(5)',

    @TO_DATE='2019-06-12 23:59:59',

    @FROM_DATE='2019-06-11 00:00:00',@DID=N'0100'

     

    • This topic was modified 4 days, 22 hours ago by  GonnaCatchIT.
  • Jeff Moden

    SSC Guru

    Points: 994238

    Perhaps it's the following problem.  It was for us.

    https://www.google.com/search?q=trace+flag+cardinality+estimator

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jonathan AC Roberts

    SSCoach

    Points: 16734

    Have you tried setting the Compatability level of the database to "SQL Server 2008"?

  • Grant Fritchey

    SSC Guru

    Points: 395394

    I'm with everyone else, get back to the old cardinality estimation engine for a short time. Turn on Query Store on your database. Run your load for a bit to see how things are behaving. Then, switch back to the new cardinality estimation engine. Any queries that regress in their behavior, use plan forcing from Query Store to make them use the older plans.  Problem solved.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • GonnaCatchIT

    SSCrazy

    Points: 2774

    Hi Jonanthan,

    No we cannot set it to SQL-2008 as a company standard

  • Grant Fritchey

    SSC Guru

    Points: 395394

    Assuming you're on SP1, or greater, of SQL Server 2016, you can use DATABASE SCOPED CONFIGURATION to change the cardinality estimation engine. You should set LEGACY_CARDINALITY_ESTIMATION to ON.

    Again, I would only do this in combination with Query Store so that you can turn it back off and use plan forcing to deal with regressions.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 994238

    GonnaCatchIT wrote:

    Hi Jonanthan,

    No we cannot set it to SQL-2008 as a company standard

    You need to read some of the articles at the Google link I posted.  You don't need to set the compatibility back to 2008.  As Grant mentions, you don't even have to change the server wide setting if you're using 2016 or better.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • GonnaCatchIT

    SSCrazy

    Points: 2774

    Thanks Jeff for the article, I am going through it.

    FYI, there was below changes made so far - though still no improvements.

    Rebuild indexes and update statistics to reduce fragmentations

    Max degree of parallelism was set to 1. Changed it to 10 now.

    Remote Login timeout changed from 10 to 30 seconds

     

     

  • Grant Fritchey

    SSC Guru

    Points: 395394

    Check your cost threshold for parallelism. 5 is the default and it's too low.

    I'd still strongly recommend doing what I suggested with query store.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • GonnaCatchIT

    SSCrazy

    Points: 2774

    Dear All,

    Thanks for all the Inputs.  When I turned ON 'Legacy Cardinality Estimation', the same query got executed in 6-7 seconds.

    With ON, it takes over 2 minutes.

    So I will review & try to re-write the query to improve the response time.

Viewing 10 posts - 1 through 10 (of 10 total)

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