RE: Huge Latency in TempDB – SQLServerCentral

SSC Guru

Points: 1003857

February 4, 2018 at 9:53 am

#1978463

RandomStream - Friday, February 2, 2018 5:37 PM
Jeff Moden - Friday, February 2, 2018 6:36 AM
kevaburg - Friday, February 2, 2018 5:06 AM
Have you looked at your wait stats? Although you haven't provided any detail I can guess that such high latency could be caused by too much IO read: not enough spindles and/or datafiles in the database for the amount of requests that need ot be serviced.
How is your TempDB set up?
Heh... or some seriously performance challenged code with non-SARGable predicates and insufficient criteria that cause accidental many-to-many joins.
We had some weekly issue with tempdb bottleneck using an SSD drive with 4 files and a log file. We split the SSD mirror into two and place two tempdb files on each drive, and moved tempdb log file to a 3rd drive. Problem no more.

IMHO, you didn't actually fix the problem. You just split the problem into two. Stop and think about it... your TempDB is on SSDs and you still had contention? While your patch on the problem certainly got you out of the woods in the short term, it doesn't solve future problems that will occur due to the eventual large increases in scale nor does solve the eventual problem of what you'll need to do when similar contentious code makes it's appearance. That's some nasty technical debt that has been built and remporarily solved with hardware and someone someday is going to need to bite the bullet and fix the code.

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