Long running Stored Procedure

Question

Long running Stored Procedure

Harold Buckner

SSCarpal Tunnel

Points: 4173
More actions
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.
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.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

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

Harold Buckner SSCarpal Tunnel Points: 4173 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246693 More actions · Answer 3

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

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

Harold Buckner SSCarpal Tunnel Points: 4173 More actions · Answer 5

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.

Harold Buckner SSCarpal Tunnel Points: 4173 More actions · Answer 6

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.

Harold Buckner SSCarpal Tunnel Points: 4173 More actions · Answer 7

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?

Harold Buckner SSCarpal Tunnel Points: 4173 More actions · Answer 8

Harold Buckner

SSCarpal Tunnel

Points: 4173

September 15, 2018 at 9:26 am

#2005868

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 9

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)

Harold Buckner SSCarpal Tunnel Points: 4173 More actions · Answer 10

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