Consider the following scenario:
You upgrade your SQL server 2005 installation to one of the following:
In this scenario, you may notice that the data files of tempdb database grow continuously and use up all the available disk space. Additionally you may receive the following error message:
Error 1101: Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup ' FilegroupName'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
If you query the value of mixed_extent_page_count column in sys.dm_db_file_space_usage dynamic management view (DMV), it shows a continuous growth over a period of time.
936185 FIX: Blocking and performance problems may occur when you enable trace flag 1118 in SQL Server 2005 if the temporary table creation workload is high
This change is also included in all SQL Sever builds higher than 9.00.3166 including SQL Server 2005 Service Pack 3 and SQL Server 2008.
When tempdb database is heavily used, this search process can introduce contention on these SGAM pages. You will notice this issue even on SQL Server installations where you have configured one tempdb data file for each available CPU. When this contention occurs, you will notice that the DMV’s like sys.dm_exec_requests and sys.dm_os_waiting_tasks show the wait_resource column as 2:1:3 or 2:n:3 [where n is the file id of any tempdb data file].
SQL Server 2005 builds 09.00.3166.00 and later [including SQL Server 2005 SP3 and SQL Server 2008] uses the following algorithm to allocate mixed extents in tempdb database:
As you can see from step 3, this new algorithm can speed up the allocation of mixed extents within the same file. Consequently it reduces the contention on SGAM pages to a great extent. One of the side effects of not searching aggressively in other files for available mixed extents is that the algorithm now needs to allocate more more mixed extents. When tempdb is set to autogrow on large disk volumes in an environment where it is heavily used, the new algorithm will mostly be using step 3 to get mixed extents, thus resulting in a continuous growth in mixed_extent_page_count value of one or more tempdb data files.