SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Huge Latency in TempDB


Huge Latency in TempDB

Author
Message
Narine M
Narine M
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2268 Visits: 200
Hi All,

We are facing a huge latency in tempdb, what will be the reason???

Latency
1174
1174
1166
1173
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)

Group: General Forum Members
Points: 847407 Visits: 46675
Narine M - Tuesday, January 30, 2018 3:34 AM
Hi All,

We are facing a huge latency in tempdb, what will be the reason???

Latency
1174
1174
1166
1173

With only that small amount of information, it could be anything including but not limited to serious garbage code (the most frequent reason, IMHO and personal observation), a physically challenged disk, a bad network card, poor cabling, wrong settings just about anywhere in the chain between the server and the disk, some bad memory (especially in any of the caches), some server settings, poor growth settings that create way too many VLFs in that same log file, etc, etc.

On the subject of garbage code, remember that poor disk latency is symptom, not a cause. If you have garbage code that beats the hell out of TempDB, it can pin disk latency to the ceiling.


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (584K reputation)SSC Guru (584K reputation)SSC Guru (584K reputation)SSC Guru (584K reputation)SSC Guru (584K reputation)SSC Guru (584K reputation)SSC Guru (584K reputation)SSC Guru (584K reputation)

Group: Administrators
Points: 584753 Visits: 20895
As Jeff noted, it could be because of hardware or poor code. If you benchmark the disk, is it slower than others?

Are there certain days/times/functions that cause you to notice this? Likely it's bad code that either spills to tempdb because of too many reads or someone really loves temp tables and is overusing them.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119795 Visits: 8980
My experience (~25 years as a SQL Server only consultant) shows that suboptimal IO is the cause of this. You can also add in suboptimal virtual machine configuration these days too.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
kevaburg
kevaburg
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15830 Visits: 1283
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)

Group: General Forum Members
Points: 847407 Visits: 46675
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.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RandomStream
RandomStream
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2849 Visits: 648
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.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)

Group: General Forum Members
Points: 847407 Visits: 46675
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RandomStream
RandomStream
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2849 Visits: 648
Jeff Moden - Sunday, February 4, 2018 9:53 AM
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.

Thank, Jeff. You do have a valid point. In this particular DW database, no one is allowed to create tables. So all SP's will need to use temp tables to do data crunching. On top of that, we have to rely on vendor's supplied SP's to meet business needs. There is very little room for in-house dev team to improve the processes. I will certain keep your advice in mind.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)

Group: General Forum Members
Points: 847407 Visits: 46675
RandomStream - Sunday, February 4, 2018 11:36 AM
Jeff Moden - Sunday, February 4, 2018 9:53 AM
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.

Thank, Jeff. You do have a valid point. In this particular DW database, no one is allowed to create tables. So all SP's will need to use temp tables to do data crunching. On top of that, we have to rely on vendor's supplied SP's to meet business needs. There is very little room for in-house dev team to improve the processes. I will certain keep your advice in mind.


Thanks for the feedback. Yeah... I agree. 3rd party stuff can be a real pain. Going through that now. And, your patch for the problem their code was causing wasn't inappropriate at all... it got things working. It's still worth throwing a hammer at the vendor to get them to fix their junk. Either that or write new code to replace the vendor and their code if they refuse to help out.

And, yeah, I know I'm definitely preaching to the choir on both of those suggestions. I just had to say it out loud for the benefit of those that are stuck in similar situations.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search