Is Your Tempdb Stressed Out?

Most SQL Server customers use tempdb a lot. But you might not realize that heavy use of tempdb can cause resource-allocation contention and result in potentially serious performance problems. I recently ran across a Microsoft Knowledge Base article that describes potential problems with tempdb that I hadn't been unaware of. Coincidentally, this information has helped some of my clients in the past several weeks, and it might be relevant to your environment.

I recently investigated a performance problem that my client and I suspected was related to creating a large number of objects in tempdb. When the client's site was busy, it created tens of thousands of tables in tempdb in a short amount of time. There's nothing inherently wrong with an architecture that relies heavily on the creation of tables in tempdb, but the site showed an increasing number of locks and latches while response time and throughput began to drop. I won't bore you with all the troubleshooting we did, but we eventually stumbled across a Microsoft article that proved to be surprisingly helpful, "FIX: Concurrency Enhancements for the Tempdb Database" ( http://support.microsoft.com/default.aspx?scid=kb;en-us;328551 ).

This article describes how the page-free space, Secondary Global Allocation Map (SGAM), and Index Allocation Map (IAM) pages can become tempdb hotspots when you quickly create many objects in tempdb or delete them from tempdb. Potential problem operations include tempdb activity associated with the following:

Repeated creation and dropping of temporary tables (local or global)

Using table variables that use tempdb for storage

Using work tables associated with cursors

Using work tables associated with an ORDER BY clause

Using work tables associated with a GROUP BY clause

Using work files associated with hash plans

The article offers three solutions for avoiding this potential tempdb bottleneck: a hotfix, a trace flag that reduces mixed-extent allocation for small objects, and a recommendation to increase the number of files in tempdb. Increasing the number of files in tempdb, even if they're all on the same disk, helps minimize contention on the SGAM because each tempdb file has its own SGAM. Microsoft expects to include the hotfix in SQL Server 2000 Service Pack 4 (SP4), but I don't recommend applying it without experimenting with the trace flag and tempdb file-management changes, which are less intrusive. (Covering SGAMs, mixed extents, and other issues is beyond the scope of this commentary. But Kalen Delaney's "Inside SQL Server" column in SQL Server Magazine is an excellent source for this kind of SQL Server internals information.)

I suspect that this tempdb bottleneck is more common than Microsoft realizes. Although I discovered the Microsoft article about the tempdb problems just recently, I've seen this type of problem affect several customers and read multiple newsgroup postings that describe similar symptoms associated with heavy use of tempdb. Without the information in this Knowledge Base article, many customers might have chalked up most of their tuning problems to "ghosts in the machine" and would have had a difficult time troubleshooting—if they could discover the problem at all. If you think tempdb is slowing down your system, check out this article.

Discuss this Article 2

L Smith (not verified)

on Feb 9, 2004

Very interesting article
Oracle has similar problems, one vendor Texas Memory Systems suggests using solid-state disks.
http://www.superssd.com/oracleperformance.htm
Who knows it might apply for SQL Server as well?
Lowell

Thanks for one sort of help in increasing the performance of database. Article seems to be very useful but at the same time expecting the use of trace flags , its behaviour and most important trace flags. I heard that we should not use trace flags untill really required it change the behaviour. I not sure about these issue. Help in this regards will be highly appreciated. Thanks in advance for great help and exploring such a nice information. Keep it up !! Cheers !

From the Blogs

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....More