Forcing Uniform Extent Allocations
Posted by decipherinfosys on January 30, 2009
We had discussed uniform and mixed extents in one our posts before. A colleague recently pointed out that there is a trace flag ( -T1118 ) which can be used to force uniform extent allocations instead of mixed page allocations. This was the first time that we used it at a client site when we were facing TempDB issues and upon doing more research, it is actually covered in a MSFT whitepaper:
“Use TF-1118. Under this trace flag SQL Server allocates full extents to each tempdb object, thereby eliminating the contention on SGAM page. This is done at the expense of some waste of disk space in tempdb. This trace flag has been available since SQL Server 2000. With improvements in tempdb object caching in SQL Server 2005, there should be significantly less contention in allocation structures. If you see contention in SGAM pages, you may want to use this trace flag. Cached tempdb objects may not always be available. For example, cached tempdb objects are destroyed when the query plan with which they are associated is recompiled or removed from the procedure cache.”
The reason we had to use it was because the customer still had a lot of legacy code and was using select … into #temp_1 where 1=0 in order to create the temp definitions. Since the code is not moving any data into those tables and is only creating those temp objects, it was placing pressure on the allocation and using this flag made it use only uniform extent allocations. There are some very good pointers in that whitepaper from MSFT so when you get time, do read it thoroughly.