locked
sql server 2017 very slow performance RRS feed

  • Question

  • Hi,

    I've moved to new server with Win 2019 and SQL 2017 STD on SSD disk from old server with Win 2008 and SQL 2008 STD and my performance is very very poor. I've a query that i run on both server and on SQL 2008 it runs 1sec but on SQL 2017 it runs 5sec. what i try - install latest CU21,change comp level to 110,120,130,140 change max dop and parallelism, update stats,rebuild indexs,clear cache,try all those trace flags OPTION (QUERYTRACEON 4199,QUERYTRACEON 4136,QUERYTRACEON 4137,QUERYTRACEON 9481),restart server and more.. nothing help.. the only thing that helps is if i add to the query the OPTION  (RECOMPILE) and change the comp level to 110 it runs 1sec.

    why is that? what i'm missing here?

    THX


    Avi.G

    Tuesday, August 4, 2020 8:46 PM

All replies

  • It's not unheard of that you see performance regressions when you upgrade to a newer version of SQL Server and also change to the most recent compatibility level. There are often changes in the optimizer, and while they statistically are to the better, there are always queries where the improvements backfire.

    What I have found is that in many cases, there is something "problematic" with the query, but with a bit luck it worked OK in the older version. The problem could be that indexing is not perfect or that the query is not written in the best way.

    Without seeing the query and query plans it is impossible to discuss but in a general manner like I've done above.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 4, 2020 9:28 PM
  • The new cardinality estimator is most likely the problem here - which is causing a different execution plan to be generated on the 2017 instance.

    You should compare the execution plans from each system to see the difference - and modify the query to work better in the new system.

    The other option is to enable query store on that database - set the database to the lower compatibility and capture data for some time.  How long you capture depends on how busy the system.  Once you have this data - switch compatibility level to the latest and let query store capture and identify queries that regressed.  You can then force the plan...

    But again - I would review the query and address it directly.  


    Jeff Williams

    Tuesday, August 4, 2020 9:56 PM
  • Always go to the execution plan first before tinkering with settings. Actual plan is preferable over estimated plan, but one of two is needed. second, identify indexes in which the lead key is not unique, these should have fullscan statistics 

    jchang

    Tuesday, August 4, 2020 10:16 PM
  • Hi Avi.G

    >why is that? what i'm missing here?

    Microsoft has an upgrade strategy for changing the compatibility mode on SQL Server 2017.

    In your case, you do not need to change the database compatibility level (keep it at prior level).

    And you should not miss the “Important” note:

    • If the compatibility level of a user database was 100 or higher before the upgrade, it remains the same after upgrade.
    • If the compatibility level of a user database was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2017 (14.x).
    • The compatibility levels of the tempdb, model, msdb and Resource databases are set to the current compatibility level after upgrade.
    • The master system database retains the compatibility level it had before upgrade.

    And this may be helpful: sql-server-upgrade-2008-2016-100-to-130-compatibility-slow-queries-new-ceavoid-sql-server-upgrade-performance-issues

    If the response helped, do "Accept Answer" and upvote it.

     

    BR,

    Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    • Edited by MIAOYUXI Wednesday, August 5, 2020 2:29 AM
    Wednesday, August 5, 2020 2:28 AM
  • Hi Avi.G,

    Is the reply helpful?

    If the response helped,  "Accept Answer" and upvote it will be benefit for others.

    BR,

    Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 1:25 AM
  • Hi Avi.G,

    Is the reply helpful?

    "Accept Answer" and upvote if the reply helped.

     

    BR,

     

    Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 7, 2020 1:24 AM
  • i think i know where is the problem but not how to fix it. i see in the actual exec plan that the query use DOP1 instead more. i setup the DOP on the server level to 6 but in the exec plan it show DOP1.

    why is that?

    * can i upload the exec plan?

    exec sp_executesql N'select top 1            (0.0 + ( round( ( (0.0 + ( sum( (0.0 + ( ( ( ( ( convert(decimal(20,4), ncenter.dbo.ORDERITEMS.PRICE) * convert(float, ncenter.dbo.ORDERITEMS.TQUANT ) / 1000.000000 ) * ( 1.000000000 - convert(decimal(11,2), ncenter.dbo.ORDERITEMS.T$PERCENT) / (case when ( 100.000000000 ) = 0 then 1 else ( 100.000000000 ) end) ) ) * ( 1.000000000 - convert(decimal(11,2), ncenter.dbo.ORDERS.T$PERCENT) / (case when ( 100.000000000 ) = 0 then 1 else ( 100.000000000 ) end) ) ) * ( 100.000000000 + case when ( ( ncenter.dbo.ORDERITEMS.VATFLAG =  ''Y'' ) ) then ( convert(decimal(10,3), ncenter.dbo.TAXPERCENT.TAXPERCENT1) ) else ( 0.000000000 ) end ) ) / (case when ( 100.000000000 ) = 0 then 1 else ( 100.000000000 ) end) )) ) ))  * 100.000000000 ) ,0) / (case when ( 100.000000000 ) = 0 then 1 else ( 100.000000000 ) end) )) 
    from ncenter.dbo.CSL_CUSTBTRANS  inner join ncenter.dbo.CSL_CUSTBTRANS a$1 on ( a$1.CUSTBTRANS = ncenter.dbo.CSL_CUSTBTRANS.CUSTBTRANS ) inner join ncenter.dbo.SIM_ORDERITEMS  on ( ncenter.dbo.SIM_ORDERITEMS.CSL_BTRANSACTION = ncenter.dbo.CSL_CUSTBTRANS.BTRANSACTION ) inner join ncenter.dbo.ORDERS  on ( ncenter.dbo.ORDERS.CUST = ncenter.dbo.CSL_CUSTBTRANS.CUST ) inner join ncenter.dbo.ORDERSA  on ( ncenter.dbo.ORDERSA.ORD = ncenter.dbo.ORDERS.ORD ) inner join ncenter.dbo.PART  on 1 = 1 inner join ncenter.dbo.PARTPARAM  on ( ncenter.dbo.PARTPARAM.PARTPARAM = ncenter.dbo.PART.PARTPARAM ) inner join ncenter.dbo.ORDERITEMS  on ( ncenter.dbo.ORDERITEMS.ORDI = ncenter.dbo.SIM_ORDERITEMS.ORDI ) inner join ncenter.dbo.TAXPERCENT  on ( ncenter.dbo.TAXPERCENT.TODATE >= ncenter.dbo.ORDERS.CURDATE ) and ( ncenter.dbo.TAXPERCENT.FROMDATE <= ncenter.dbo.ORDERS.CURDATE ) and ( ncenter.dbo.TAXPERCENT.TAXGROUP = case when ( ( ncenter.dbo.PARTPARAM.TAXGROUP = @P1 ) ) then ( - ( @P2 ) ) else ( ncenter.dbo.PARTPARAM.TAXGROUP ) end ) and ( ncenter.dbo.TAXPERCENT.TAX = ncenter.dbo.ORDERSA.TAX ) inner join ncenter.dbo.CUSTOMERS  on ( ncenter.dbo.CUSTOMERS.CUST = ncenter.dbo.ORDERS.CUST ) 
    where ( ( ( ncenter.dbo.CUSTOMERS.CUST = @P3 ) and ( ncenter.dbo.CSL_CUSTBTRANS.UNITED <>  ''Y'' ) ) or ( ( ncenter.dbo.CUSTOMERS.SIM_CLASS = ltrim(rtrim( @P4 )) ) and ( ncenter.dbo.CSL_CUSTBTRANS.UNITED =  ''Y'' ) ) ) and ( ncenter.dbo.ORDERITEMS.ORD = ncenter.dbo.ORDERS.ORD ) and ( ncenter.dbo.ORDERITEMS.PART = ncenter.dbo.PART.PART ) and ( ncenter.dbo.ORDERITEMS.ORDI <> @P5 ) and ( ( ncenter.dbo.ORDERITEMS.CLOSED =  '' '' ) or ( ncenter.dbo.ORDERITEMS.TBALANCE = @P6 ) ) and ( a$1.SIM_CLASS = ltrim(rtrim( @P7 )) ) and ( a$1.BTRANSACTION = @P8 ) and ( ncenter.dbo.CSL_CUSTBTRANS.BTRANSACTION = @P9 ) 
    having count(*) > 0
     ',N'@P1 bigint,@P2 bigint,@P3 bigint,@P4 varchar(5),@P5 bigint,@P6 bigint,@P7 varchar(5),@P8 bigint,@P9 bigint',0,2,76713,'60109',1685183,0,'60109',85,85


    Avi.G

    Friday, August 7, 2020 4:20 AM
  • Hi Avi.G,

    Could you please test with T-Sql?

    EXEC sp_configure 'show advanced options', 1;  
    GO  
    RECONFIGURE WITH OVERRIDE;  
    GO  
    EXEC sp_configure 'max degree of parallelism', 6;  
    GO  
    RECONFIGURE WITH OVERRIDE;  
    GO    

    More information: configure-the-max-degree-of-parallelism-server-configuration-optiondifferent-ways-set-max-degree-parallelism-sql-server

    BR,

    Mia

    If the response helped, do "Accept Answer" and upvote it.--Mia



    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 7, 2020 5:52 AM
  • Hi Avi.G,

    Could you please test with T-Sql?

    EXEC sp_configure 'show advanced options', 1;  
    GO  
    RECONFIGURE WITH OVERRIDE;  
    GO  
    EXEC sp_configure 'max degree of parallelism', 6;  
    GO  
    RECONFIGURE WITH OVERRIDE;  
    GO    

    More information: configure-the-max-degree-of-parallelism-server-configuration-optiondifferent-ways-set-max-degree-parallelism-sql-server

    BR,

    Mia

    If the response helped, do "Accept Answer" and upvote it.--Mia



    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    did try and get the same results only 1DOP was using on the SQL 2008 run the same query and it using 4DOP

    Avi.G

    Friday, August 7, 2020 7:07 AM
  • i think i know where is the problem but not how to fix it. i see in the actual exec plan that the query use DOP1 instead more. i setup the DOP on the server level to 6 but in the exec plan it show DOP1.

    why is that?

    * can i upload the exec plan?

    These forums do not support attachments other than images. But you can upload plans on http://www.brentozar.com/pastetheplan and post the link here.

    It would help to see both the slow plan with compat level 140 and the fast plan with compat level 110.

    The query you posted is very difficult to read and I would have to clean it up be able to get a grip of it. But this is not a hand-crafted query is it? How much generated is it? Is there an ORM producing this, or is it generated by code that is within you powers to change?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 7, 2020 8:06 AM