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:

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.