Select the product you need help with

You may notice a continuous growth in tempdb data file size with a corresponding increase in mixed_extent_page_count after upgrading SQL server

Article ID: 2000471 - View products that this article applies to.

Symptoms

Consider the following scenario:

You upgrade your SQL server 2005 installation to one of the following:

SQL Server 2008
SQL Server 2005 Service Pack 3 or higher.
SQL server 2005 Service Pack 2 build 09.00.3166.00 or higher.

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.

Back to the top | Give Feedback

Cause

The following SQL Server 2005 Service Pack 2 based hotfix introduced a change to the algorithm that is used when allocating new mixed extents to DML operations(INSERT, UPDATE and DELETE) in tempdb.

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.

Back to the top | Give Feedback

Resolution

If you are on SQL 2005 builds 09.00.3166.00 and later [including SQL Server 2005 SP3 and SQL Server 2008] and observe this excessive growth in tempdb data files, you can enable trace flag 1140 to revert to the old algorithm that uses aggressive search for existing mixed extents before allocating new mixed extents.

Back to the top | Give Feedback

More Information

SQL Server 2005 builds before 09.00.3166.00 uses the following algorithm to allocate mixed extents:

Choose a file from the list of data files based on proportional fill algorithm.
Check all SGAM pages of this file to locate existing mixed extents from where a page can be allocated.
If none exist in this file, check in other files of the database.
If no SGAM pages have room available for a new page, perform the same check on all GAM pages.
If still no extent can be located, grow the file(s) and allocate a new mixed extent.
Allocate a page from the new mixed extent.

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:

Choose a file from the list of data files based on proportional fill algorithm.
Check all SGAM pages of this file to locate existing mixed extents from where a page can be allocated.
If none exist in this file, allocate a new mixed extent and allocate a page from this extent.
If a new extent cannot be allocated in the current file due to lack of available space, check in other files of the database.
If still no extent can be located, grow the file(s) and allocate a new mixed extent.
Allocate a page from the new mixed extent

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.

Back to the top | Give Feedback

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use

(http://go.microsoft.com/fwlink/?LinkId=151500)

for other considerations.

Back to the top | Give Feedback