Prod vs Dev execution mystery

Question

Post reply

Prod vs Dev execution mystery

Yuri55

SSCrazy Eights

Points: 8449
More actions
October 11, 2016 at 5:22 pm

#322096

Have a really strange case when same UPDATE statement (part of SP) runs on Prod sql server for few hours while on Dev one- under minute. Prod sql server has more CPU, memory, cannot find any resource contention, no extra locks on table that supposed to be updated. Seems UPDATE is progressing (SELECT with NoLock shows some move) but extremely slow. At the same time on Dev sql server (with DB copied from Prod, i.e. same size, indexes, no triggers) UPDATE runs as it should be- under 1 min. Really appreciate any tips or suggestion what I should double check. Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

On Prod there must be some concurrent activity which is surely absent in DEV.

Did you check for blocking?

_____________
Code for TallyGenerator

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 2

No blocking- checked. For some reason this process (as per sys.sysprocesses) has SOS_SCHEDULER_YEILD wait, i.e. allows other processes to use scheduler thread (but waiting itself). May be this wait is result, not-reason for long execution. Not sure. Thanks

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

Yuri55 (10/11/2016)
Have a really strange case when same UPDATE statement (part of SP) runs on Prod sql server for few hours while on Dev one- under minute. Prod sql server has more CPU, memory, cannot find any resource contention, no extra locks on table that supposed to be updated. Seems UPDATE is progressing (SELECT with NoLock shows some move) but extremely slow. At the same time on Dev sql server (with DB copied from Prod, i.e. same size, indexes, no triggers) UPDATE runs as it should be- under 1 min. Really appreciate any tips or suggestion what I should double check. Thanks

Post the update statement. I've seen this problem several times before and it has usually been to an illegal form of the UPDATE statement that usually works but flakes out when certain stars align for parallelism and certain indexes.

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

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Yuri55 (10/11/2016)
No blocking- checked. For some reason this process (as per sys.sysprocesses) has SOS_SCHEDULER_YEILD wait, i.e. allows other processes to use scheduler thread (but waiting itself). May be this wait is result, not-reason for long execution. Not sure. Thanks

SOS_SCHEDULER_YEILD usually appears when there are not enough resources to fulfil all queries.

There might be no direct blocking in your case, but other activities on the server take all it resources, and you UPDATE had to wait till the server has some air to breathe.

_____________
Code for TallyGenerator

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Can you post the actual execution plan of the update run in dev as a .sqlplan attachment please? As Jeff has pointed out, there may be tuning opportunities.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 6

You mean it can be (let say, potentially) bad query itself? I know it's not the best and we have some ideas how to improve it but even did not start tuning cause completely confused by fast execution in Dev env. Anyhow, thanks for all suggestions- I'll try to tune

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 7

Accidentally pressed Post tab, sorry 🙂

Anyhow, thanks for all suggestions- I'll try to tune Update statement myself first.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Yuri55 (10/12/2016)
You mean it can be (let say, potentially) bad query itself? I know it's not the best and we have some ideas how to improve it but even did not start tuning cause completely confused by fast execution in Dev env. Anyhow, thanks for all suggestions- I'll try to tune

If the query is sufficiently complex, the optimiser could time out attempting to calculate a good enough plan. If this is the case then it's quite likely that you could get different plans in the two environments. You can find out by right-clicking on the result operator icon (in this case, the UPDATE at the far left of the plan) and examining the property sheet.

You could post the statement here for suggestions...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eric M Russell SSC Guru Points: 125562 More actions · Answer 9

Compare the execution plan between the two servers for an identical query using same parameters.

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

Yuri55 (10/12/2016)
You mean it can be (let say, potentially) bad query itself? I know it's not the best and we have some ideas how to improve it but even did not start tuning cause completely confused by fast execution in Dev env. Anyhow, thanks for all suggestions- I'll try to tune

You need to go back and read my previous post. Seriously. This may not have a thing to do with tuning. It may have everything to do with improper usage of the UPDATE statement. Strongly recommend you post the update statement.

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

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 11

Thanks Jeff,

Will do shortly- have Prod issue right now 🙁