Waits on temp db

Question

Waits on temp db

astrid 69000

Hall of Fame

Points: 3208
More actions
September 12, 2022 at 6:59 pm

#4086787
Hello,
I would like some input and ideas on my issue.
I have two virtual servers. Prod and Test.
A couple of weeks ago, out of the blue without any deployment of code, my production server started to get long wait time while processing procedure on large tables, something that will take 20 minutes the previous day, not it will take 11 hours or more. And we see a very long latency on the tempbd on the prod server.
Test has the same code and process data from the same source. but we didnt see any issues until a week after.
I have rebuild the temp files, we have moved the temp disc from one drive to another.
Both sql servers have the same configuration, and the data disk does not get max out, nor in cpu nor in memory. I am not sure what else to try or what to check.
Thanks
Astrid
- This topic was modified 2 years, 2 months ago by astrid 69000.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

astrid 69000 wrote:

Hello,
I would like some input and ideas on my issue.
I have two virtual servers. Prod and Test.
A couple of weeks ago, out of the blue without any deployment of code, my production server started to get long wait time while processing procedure on large tables, something that will take 20 minutes the previous day, not it will take 11 hours or more. And we see a very long latency on the tempbd on the prod server.
Test has the same code and process data from the same source. but we didnt see any issues until a week after.
I have rebuild the temp files, we have moved the temp disc from one drive to another.
Both sql servers have the same configuration, and the data disk does not get max out, nor in cpu nor in memory. I am not sure what else to try or what to check.
Thanks
Astrid

I went through something similar just a couple of weeks ago. New "report code" was released. The first day, it took 3 hours to run. The second day, I stopped it after it was still running after 6 hours. It looked like it might be a TempDB issue.

Look at you now. It MUST be TempDB, right? Ummmm... have all your queries suddenly started to have a similar issue? If not, it's not TempDB.

For the problem I was presented, I know it was going to be one of two things or a combination. And 99.9% of the time, this applies to all such problems.

Poorly written code and/or a lack of indexes.

When I got done with the code (and I only needed to fix one part of the code using a little "Divide'n'Conquer" to stop the accidental many-to-many joins as well as fix some things like non-SARGable criteria an other poor practices, I check the new execution plan, added a small index, and the code was now running sub-second.

The other thing that it could be (which I didn't have to check for on my issue because I'm religious about stats maintenance) for your problem is that (maybe) statistics need a fresh. Have you measured the RowModCtr on the the indexes on the tables involved? It's been "the fix" more times than I care to mention.

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

One item that comes to mind is since these are different instances, the load on them can be different and there could be blocks/locks /activity on one that you wont see on the other.

As Jeff mentioned, the ability to optimise code so any locks on resources are of short duration should help your code and not have a cascading effect.

----------------------------------------------------

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Also, you sometimes see this kind of thing when thresholds are crossed such that the row counts change and different execution plans get generated. Over time, statistics and data distribution just changes. What was a relatively well performing query yesterday could be a nightmare today, with no other changes to anything but the data itself. Usually happens on queries and structures that were less than optimal anyway, so back to what Jeff & MMartin1 say, tuning fundamentals.

"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

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 4

Thanks to everyone but the input.

No new code, nor report was released. I also don't get any blocks.

We found yesterday that we had we had the tempdb and the data disks on the same SCSI controller, we have moved tempdb to its own and now we are not getting any waits, I am still testing.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

astrid 69000 wrote:

Thanks to everyone but the input.
No new code, nor report was released. I also don't get any blocks.
We found yesterday that we had we had the tempdb and the data disks on the same SCSI controller, we have moved tempdb to its own and now we are not getting any waits, I am still testing.

It doesn't seem that something like that would become an issue in a "A couple of weeks ago, out of the blue" fashion and it doesn't seem likely that you would see such an issue on one "instance" and not the other if they're going through the same controller.

Have you checked the latency of the old controller since you've made this change and compared it to the new controller?

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