Post reply

Long running Stored Procedure

  • September 14, 2018 at 11:31 am

    #415643

    We have a vendor application that has a few long running stored procedures that build reports using Tempdb. The stored procedures has many, many left outer joins. The Virtual Machine has 8 CPU processors and plenty of memory running SQL 2014. When the longest stored procedure runs, it uses all 8 processors and all 8 are at 50% for 1 hour and 6 min while the stored procedure is running. I've tried tuning the Cost Threshold for Parallelism with no luck. 

    I can run this same stored procedure in SQL 2016 on a Virtual Machine on the same host as the 2014 Virtual Machine. It has 2 cpu's and less memory and it completes in 10 seconds. 

    Can SQL 2016 be that much faster? Is there anything I can do to 2014 to make it faster? Both machine Disk and File layouts are the same. My only recommendation now is to move to 2016. We do not have the permissions to make changes to the code so trying to find something related to the server I can do.

  • September 14, 2018 at 11:39 am

    #2005818

    Have you tried updating statistics and recompiling the SP on the slower system?

  • September 14, 2018 at 11:43 am

    #2005819

    ZZartin - Friday, September 14, 2018 11:39 AM

    Have you tried updating statistics and recompiling the SP on the slower system?

    Yes that has been done along with rebuilding indexes and FreeProcCache.

  • September 14, 2018 at 11:58 am

    #2005820

    Harold Buckner - Friday, September 14, 2018 11:31 AM

    We have a vendor application that has a few long running stored procedures that build reports using Tempdb. The stored procedures has many, many left outer joins. The Virtual Machine has 8 CPU processors and plenty of memory running SQL 2014. When the longest stored procedure runs, it uses all 8 processors and all 8 are at 50% for 1 hour and 6 min while the stored procedure is running. I've tried tuning the Cost Threshold for Parallelism with no luck. 

    I can run this same stored procedure in SQL 2016 on a Virtual Machine on the same host as the 2014 Virtual Machine. It has 2 cpu's and less memory and it completes in 10 seconds. 

    Can SQL 2016 be that much faster? Is there anything I can do to 2014 to make it faster? Both machine Disk and File layouts are the same. My only recommendation now is to move to 2016. We do not have the permissions to make changes to the code so trying to find something related to the server I can do.

    Have you compared Actual Execution Plans between the two instances? That should give you a clue.

    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

  • September 14, 2018 at 12:16 pm

    #2005824

    In addition to looking at the execution plans, use extended events to capture the wait statistics for each query. Understand what is making one slower than the other. It's likely that just looking at the exec plan will tell you all you need, but I'd check that. I'd also look to see if the compatibility level is set to in 2014. How about the query optimizer hot fixes? They're enabled by default in 2016. However, most of these should show up in the execution plan.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 14, 2018 at 12:27 pm

    #2005830

    Grant Fritchey - Friday, September 14, 2018 12:16 PM

    In addition to looking at the execution plans, use extended events to capture the wait statistics for each query. Understand what is making one slower than the other. It's likely that just looking at the exec plan will tell you all you need, but I'd check that. I'd also look to see if the compatibility level is set to in 2014. How about the query optimizer hot fixes? They're enabled by default in 2016. However, most of these should show up in the execution plan.

    In 2014 the wait that is effecting it is the CXPACKET wait. It is 99% of the wait time. 

    I am going to compare the execution plans next. I will look into the query optimizer hot fix too.

  • September 14, 2018 at 1:33 pm

    #2005846

    Phil Parkin - Friday, September 14, 2018 11:58 AM

    Harold Buckner - Friday, September 14, 2018 11:31 AM

    We have a vendor application that has a few long running stored procedures that build reports using Tempdb. The stored procedures has many, many left outer joins. The Virtual Machine has 8 CPU processors and plenty of memory running SQL 2014. When the longest stored procedure runs, it uses all 8 processors and all 8 are at 50% for 1 hour and 6 min while the stored procedure is running. I've tried tuning the Cost Threshold for Parallelism with no luck. 

    I can run this same stored procedure in SQL 2016 on a Virtual Machine on the same host as the 2014 Virtual Machine. It has 2 cpu's and less memory and it completes in 10 seconds. 

    Can SQL 2016 be that much faster? Is there anything I can do to 2014 to make it faster? Both machine Disk and File layouts are the same. My only recommendation now is to move to 2016. We do not have the permissions to make changes to the code so trying to find something related to the server I can do.

    Have you compared Actual Execution Plans between the two instances? That should give you a clue.

    The sqlvmtst08_Plan is SQL server 2014 and the SQLVMtst25_Plan is SQL 2016. If anyone mind looking and offering feedback. The database has some temporal tables included in the joins and has millions of rows. At one point the report was running fine until we added 2019 data and did some mass calculations then the report SP slowed to a snails pace. Runs like a champ on SQL 2016. I was shocked at the difference.

  • September 15, 2018 at 6:44 am

    #2005866

    Another interesting find. On my SQL 2014 instance were the SP takes 66 min to run, It I switch the database to Compatibility level SQL Server  2012, it runs in 2 secs. 

    I tried several different Trace Flags and did not find anything to help. Currently I have no Trace Flags, with the database in 2012 compatibility mode, it runs in 2 seconds. I switch it to SQL 2014 compatibility mode and the SP runs in 66 minutes.

    Any clues?

  • September 15, 2018 at 9:26 am

    #2005868

  • Here is the answer to the problem
    The problem ends up being with the SQL Server 2014 Cardinality Estimator  Which was the first major redesign of the SQL Server Query Optimizer cardinality estimation process- If I use Trace Flag 9481 it forces the Cardinality Estimator to use the old 7.0 version. Apparently the 2014 estimator does not work very well in the first design. I would think that it should have been fixed in a SP or CU update but most not be.  However It is fixed in 2016. I timed the query in SQL Server 2014 compatibility mode and 2016 compatibility mode on the SQL server 2016 instance and both compatibility modes tested fine. https://msdn.microsoft.com/en-us/library/dn673537.aspx
    So you can put the database in 2012 compatibility mode or run trace flag -T9481 and both of these will take care of the issue we are having.

  • September 15, 2018 at 10:06 am

    #2005870

    Harold Buckner - Saturday, September 15, 2018 6:44 AM

    Another interesting find. On my SQL 2014 instance were the SP takes 66 min to run, It I switch the database to Compatibility level SQL Server  2012, it runs in 2 secs. 

    I tried several different Trace Flags and did not find anything to help. Currently I have no Trace Flags, with the database in 2012 compatibility mode, it runs in 2 seconds. I switch it to SQL 2014 compatibility mode and the SP runs in 66 minutes.

    Any clues?

    I know it's going to sound strange but compare the "Cost Threshold for Parallelism" in the server settings on all three.  If someone tried to get a little too clever with that setting, it can cause this type of problem.

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

    Change is inevitable... Change for the better is not.

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

  • September 15, 2018 at 10:17 am

    #2005871

    They are at the default settings. I tried tweaking it on the 2014 instance but nothing helped.

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

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