Categories
General

Day 5–Trace Flag 1118–Allocate Uniform extent to tempdb objects

Have you ever faced allocation contention problems with tempdb database in SQL Server 2000? If yes then you might be ended up with the recommendation to enable trace flag 1118** as per http://support.microsoft.com/kb/328551 or http://support.microsoft.com/kb/936185. By default when you create a temporary object it will allocate a single page to that object searching SGAM however in a large environment if you create temp tables a lot then you might ending up with allocation contention. However if you enable trace flag 1118 then it will check GAM to allocate a uniform extent to the object so that there by reducing the look ups on SGAM page.