By: Sankar Reddy | Last Updated: 2009-10-07 | Comments (2) | System Databases
We are using SQL Server 2008 and the server has a lot of performance issues that seem to be related to a saturated tempdb database. TempDB on this server is heavily used by creating and dropping many temp tables and work tables. How do I effectively find out if the Tempdb database is suffering from an allocation bottleneck? Should I create multiple TempDB files per core on this server to improve performance? How do I check this information programmatically?
TempDB is an important part of SQL Server when an application creates many work and temporary tables. As such, Tempdb should be monitored for performance issues. Tempdb can be a severe bottleneck on the server if not properly configured and optimized. One of the potential problems with Tempdb is that it can be allocation bottleneck. This allocation bottleneck happens when you have a workload that creates and drops many temp tables from concurrent connections, utilizes heavy workload of worktables that are created by using extensive use of group by, order by, distinct, logic with a large set of data.
What is allocation bottleneck?
In short, SQL Server uses PFS (Page Free Space), GAM (Global Allocation Map) and SGAM (Shared Global Allocation Map) pages for allocating and deallocating space by placing SH (shared) and UPDATE latches on these pages. When multiple CPU's with concurrent connections, create and drop temp tables, work tables, the allocation bottleneck comes into play when these connections need to take latches on PFS, GAM, SGAM pages in conflicting modes. This allocation algorithm is explained in detail by Sunil Agarwal in this post. TempDB is prone to this allocation bottleneck and when this happens it can affect the overall performance of the whole server since TempDB is responsible for temporary objects for the entire instance.
How is TempDB allocation tracked in SQL Server 2005?
SQL Server 2005 has exposed a lot of information in the catalog views. Specifically the sys.dm_os_waiting_tasks view is used to track the allocation bottlenecks. Here is the code snippet to review that data.
Note that the above catalog view returns information about the tasks that are currently waiting on some resources. In other words, the information returned by sys.dm_os_waiting_tasks is transient and has to read at the exact moment. So, the above query has to be constantly polled at regular intervals to get a glimpse of allocation bottleneck. Our chances of capturing an allocation bottleneck correctly depends on the polling the table and there are ample chances of not being able to capture completely.
Monitoring TempDB allocation using Extended Events in SQL Server 2008
SQL Server 2005 allows us to look at wait statistics at the server level. While this information is useful, it doesn't allow us to look at wait statistics at a more granular level like per database or per session level. That's why in SQL Server 2005, sys.dm_os_waiting_tasks has to be polled constantly to look at the wait statistics using the Resource_Description column filtering only tempdb.
Extended Events are a new mechanism introduced in SQL Server 2008 to capture SQL Server performance data with low overhead compared to SQL Server profiler, server side tracing, DBCC commands and other utilities. The scope of Extended Events is far beyond this tip and please refer to the articles below for better understanding of this feature. Extended Events allow more granular level tracking that was impossible in prior SQL Server versions. Extended Events, XE in short, allows capturing performance monitoring data at individual database level or per session level and for this tip we will track information at the database level for tempdb. The queries below allow us to find out what information is available to track using Extended events.
The code snippet below creates the session and uses asynchronous filetarget command, which should be used as a best practice to avoid Ringbuffer overhead. The Ringbuffer uses 4 MB of data and if additional data is captured, then ringbuffer data is overwritten.
Let's run a test script to apply load on Tempdb by creating table variables and temp tables with and without named constraints. This code takes advantage of a technique from the Executing a T-SQL batch multiple times using GO tip.
Once the script is completed, let's summarize the wait info statistics. I ran these queries on my laptop and the results are listed below. The results show significant IO issues with tempdb along with an allocation bottleneck.
How to remove allocation bottleneck?
If you can recognize the allocation bottleneck for the TempDB database, then you can try the following techniques to reduce the bottleneck:
- For the TempDB database, create multiple data files. The recommended number of files is usually 1/4 to 1/2 of the number of CPU cores.
- Make sure the TempDB data files are of the same size to effectively use the proportional fill algorithm.
- Modify queries to avoid creating and dropping of temp tables excessively.
- Check queries and add indexes to avoid generating excessive IO.
- Use trace flag 1118 to force using uniform extents instead of mixed extents.
- Learn about Extended Events:
- Take a look at TempDB Monitoring and Troubleshooting: Allocation Bottleneck by Sunil Agarwal.
- Check out the Working with tempdb in SQL Server 2005 whitepaper.
- Check out the Should you create multiple files for a user DB on a multi-core box? by Paul Randal.
- Check out these additional tips:
Last Updated: 2009-10-07
About the author
View all my tips