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.