If your SQL Server suddenly becomes slow during peak business hours, reports take longer to complete, and users experience intermittent delays\u2014even though CPU, memory, and disk utilization appear normal\u2014the problem may not be your application or hardware.<\/p>\n\n\n\n
One of the most common yet overlooked SQL Server performance bottlenecks is TempDB allocation contention<\/strong>.<\/p>\n\n\n\n TempDB is one of the busiest databases in SQL Server. It is used by almost every workload, including sorting, hashing, temporary tables, table variables, row versioning, index creation, and internal query processing. When multiple sessions attempt to allocate pages simultaneously, SQL Server can experience severe allocation bottlenecks.<\/p>\n\n\n\n The most recognizable symptoms are excessive PAGELATCH_UP<\/strong> and PAGELATCH_EX<\/strong> waits, which indicate contention on TempDB allocation pages such as PFS (Page Free Space)<\/strong>, GAM (Global Allocation Map)<\/strong>, and SGAM (Shared Global Allocation Map)<\/strong>.<\/p>\n\n\n\n These waits can significantly reduce throughput, increase query response times, and impact overall application performance.<\/p>\n\n\n\n In this guide, you’ll learn how TempDB allocation contention occurs, how to diagnose it using Dynamic Management Views (DMVs), and the best practices to eliminate contention by configuring TempDB correctly.<\/p>\n\n\n\n TempDB is a system database recreated every time SQL Server starts.<\/p>\n\n\n\n Unlike user databases, TempDB stores temporary objects and internal SQL Server operations.<\/p>\n\n\n\n It is heavily used for:<\/p>\n\n\n\n Because every database on the instance shares TempDB, it often becomes a performance bottleneck on busy servers.<\/p>\n\n\n\n Whenever SQL Server creates temporary objects, it must allocate new pages inside TempDB.<\/p>\n\n\n\n These allocations are managed through special system pages:<\/p>\n\n\n\n When hundreds or thousands of concurrent sessions attempt to allocate pages simultaneously, they compete for access to these allocation pages.<\/p>\n\n\n\n This competition is called TempDB allocation contention<\/strong>.<\/p>\n\n\n\n Instead of processing queries, sessions wait for access to allocation pages, causing performance degradation.<\/p>\n\n\n\n You may be experiencing TempDB contention if you notice:<\/p>\n\n\n\n Unlike PAGEIOLATCH waits, which indicate disk I\/O delays, PAGELATCH waits occur entirely in memory.<\/p>\n\n\n\n This means SQL Server is waiting for access to internal memory structures rather than physical storage.<\/p>\n\n\n\n Common latch waits include:<\/p>\n\n\n\n Occurs when multiple sessions request update access to allocation pages.<\/p>\n\n\n\n Occurs when exclusive access is required for page allocation.<\/p>\n\n\n\n Frequent waits on these types usually indicate TempDB allocation contention.<\/p>\n\n\n\n Use the following DMV query to identify the most significant latch waits.<\/p>\n\n\n\n If The following query displays sessions waiting on TempDB pages.<\/p>\n\n\n\n If the resource description references database ID 2<\/strong>, the contention is occurring in TempDB.<\/p>\n\n\n\n One of the first things every DBA should verify is the number and size of TempDB data files.<\/p>\n\n\n\n Review:<\/p>\n\n\n\n Uneven file sizes can create allocation hotspots.<\/p>\n\n\n\n Imagine a server with:<\/p>\n\n\n\n If TempDB has only one data file, every allocation request targets the same allocation pages.<\/p>\n\n\n\n This creates a bottleneck.<\/p>\n\n\n\n Multiple TempDB files distribute allocations across several allocation maps, reducing contention.<\/p>\n\n\n\n Microsoft recommends using multiple TempDB data files of equal size<\/strong>.<\/p>\n\n\n\n General guidance:<\/p>\n\n\n\n Important:<\/strong> More files are not always better. Start with up to 8 equally sized data files<\/strong>, monitor performance, and increase only if contention continues.<\/p>\n<\/blockquote>\n\n\n\n Example:<\/p>\n\n\n\n Repeat until the desired number of data files is configured.<\/p>\n\n\n\n Ensure all data files have:<\/p>\n\n\n\n Avoid percentage-based auto-growth.<\/p>\n\n\n\n Instead, use fixed increments.<\/p>\n\n\n\n Good example:<\/p>\n\n\n\n Avoid:<\/p>\n\n\n\n Percentage growth can create uneven file sizes over time.<\/p>\n\n\n\n Poor application design can overload TempDB.<\/p>\n\n\n\n Examples include:<\/p>\n\n\n\n Review application code to minimize unnecessary TempDB usage.<\/p>\n\n\n\n Snapshot isolation and Read Committed Snapshot Isolation (RCSI) use TempDB extensively.<\/p>\n\n\n\n Monitor version store space.<\/p>\n\n\n\n Unexpected growth may indicate long-running transactions.<\/p>\n\n\n\n Historically, SQL Server administrators enabled trace flags such as:<\/p>\n\n\n\n These trace flags reduced allocation contention by changing extent allocation behavior.<\/p>\n\n\n\n SQL Server 2016 and later<\/strong> include these improvements by default, so manually enabling these trace flags is generally not required<\/strong>.<\/p>\n\n\n\n For older SQL Server versions, consult Microsoft’s guidance before enabling trace flags in production.<\/p>\n\n\n\n Use this query to check current TempDB allocation.<\/p>\n\n\n\n This helps identify excessive TempDB consumption.<\/p>\n\n\n\n TempDB allocation contention often develops gradually and is difficult to detect before users experience slow performance. Waiting until applications begin timing out can lead to extended troubleshooting and unnecessary downtime.<\/p>\n\n\n\n DBPulse<\/strong> continuously monitors SQL Server performance and provides real-time visibility into TempDB health, helping database administrators detect allocation bottlenecks before they become critical.<\/p>\n\n\n\n With DBPulse, you can:<\/p>\n\n\n\n Instead of manually checking DMVs during a performance incident, DBPulse provides continuous monitoring and actionable insights, enabling faster diagnosis and long-term optimization of TempDB performance.<\/p>\n\n\n\n It occurs when many concurrent sessions compete for TempDB allocation pages such as PFS, GAM, and SGAM while creating temporary objects or performing internal SQL Server operations.<\/p>\n\n\n\n A common starting point is up to 8 equally sized data files<\/strong>, depending on CPU count and workload. Add more only if monitoring confirms ongoing contention.<\/p>\n\n\n\n For SQL Server 2016 and later, their behavior is largely built into the product. They are primarily relevant to older SQL Server versions.<\/p>\n\n\n\n Look for high TempDB allocation contention is one of the most common causes of performance degradation in high-concurrency SQL Server environments. Excessive By configuring multiple equally sized TempDB data files, monitoring latch waits, optimizing application design, and following Microsoft’s recommended TempDB best practices, organizations can significantly improve throughput and reduce query latency.<\/p>\n\n\n\n For proactive database administration, continuous monitoring is essential. DBPulse<\/strong> helps database teams monitor TempDB usage, detect allocation contention, analyze wait statistics, and receive real-time alerts\u2014making it easier to resolve performance issues before they impact business-critical applications.<\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" SQL Server TempDB Allocation Contention: Complete Guide to Diagnose and Fix PAGELATCH Waits If your SQL Server suddenly becomes slow during peak business hours, reports take longer to complete, and users experience intermittent delays\u2014even though CPU, memory, and disk utilization appear normal\u2014the problem may not be your application or hardware. One of the most common<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-164","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=\/wp\/v2\/posts\/164","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=164"}],"version-history":[{"count":1,"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=\/wp\/v2\/posts\/164\/revisions"}],"predecessor-version":[{"id":165,"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=\/wp\/v2\/posts\/164\/revisions\/165"}],"wp:attachment":[{"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.vigplanet.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\n\n\nWhat Is TempDB?<\/h1>\n\n\n\n
\n
#TempTables<\/code>)<\/li>\n\n\n\n
\n\n\n\nWhat Is TempDB Allocation Contention?<\/h1>\n\n\n\n
\n
\n\n\n\nCommon Symptoms of TempDB Contention<\/h1>\n\n\n\n
\n
PAGELATCH_UP<\/code> waits<\/li>\n\n\n\nPAGELATCH_EX<\/code> waits<\/li>\n\n\n\n
\n\n\n\nUnderstanding PAGELATCH Wait Types<\/h1>\n\n\n\n
PAGELATCH_UP<\/h3>\n\n\n\n
PAGELATCH_EX<\/h3>\n\n\n\n
\n\n\n\nIdentifying TempDB Allocation Waits<\/h1>\n\n\n\n
SELECT\n wait_type,\n waiting_tasks_count,\n wait_time_ms,\n signal_wait_time_ms\nFROM sys.dm_os_wait_stats\nWHERE wait_type LIKE 'PAGELATCH%'\nORDER BY wait_time_ms DESC;\n<\/code><\/pre>\n\n\n\nPAGELATCH_UP<\/code> or PAGELATCH_EX<\/code> appear at the top of the results, investigate TempDB configuration.<\/p>\n\n\n\n
\n\n\n\nVerify That Waits Are Occurring in TempDB<\/h1>\n\n\n\n
SELECT\n session_id,\n wait_type,\n wait_duration_ms,\n resource_description\nFROM sys.dm_os_waiting_tasks\nWHERE wait_type LIKE 'PAGELATCH%';\n<\/code><\/pre>\n\n\n\n
\n\n\n\nCheck TempDB File Configuration<\/h1>\n\n\n\n
SELECT\n name,\n physical_name,\n size * 8 \/ 1024 AS SizeMB\nFROM tempdb.sys.database_files;\n<\/code><\/pre>\n\n\n\n\n
\n\n\n\nWhy a Single TempDB File Causes Problems<\/h1>\n\n\n\n
\n
\n\n\n\nBest Practice: Configure Multiple TempDB Data Files<\/h1>\n\n\n\n
Logical CPU Cores<\/th> Recommended TempDB Data Files<\/th><\/tr><\/thead> 1\u20134<\/td> 1\u20134<\/td><\/tr> 8<\/td> 8<\/td><\/tr> 16<\/td> 8<\/td><\/tr> 32<\/td> 8 (increase only if contention persists)<\/td><\/tr> 64+<\/td> 8\u201316 (monitor before adding more)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n \n
\n\n\n\nAdd Additional TempDB Files<\/h1>\n\n\n\n
ALTER DATABASE tempdb\nADD FILE\n(\nNAME = tempdev2,\nFILENAME = 'D:\\SQLData\\tempdb2.ndf',\nSIZE = 1024MB,\nFILEGROWTH = 256MB\n);\n<\/code><\/pre>\n\n\n\n\n
\n\n\n\nConfigure Appropriate File Growth<\/h1>\n\n\n\n
Initial Size: 4 GB\nGrowth: 512 MB\n<\/code><\/pre>\n\n\n\nGrowth: 10%\n<\/code><\/pre>\n\n\n\n
\n\n\n\nOptimize Temporary Object Usage<\/h1>\n\n\n\n
\n
\n\n\n\nMonitor Version Store Usage<\/h1>\n\n\n\n
SELECT\n SUM(version_store_reserved_page_count) * 8 AS VersionStoreKB\nFROM sys.dm_db_file_space_usage;\n<\/code><\/pre>\n\n\n\n
\n\n\n\nTrace Flags and Modern SQL Server Versions<\/h1>\n\n\n\n
\n
\n\n\n\nMonitor TempDB Space Usage<\/h1>\n\n\n\n
SELECT\n SUM(user_object_reserved_page_count) * 8 AS UserObjectsKB,\n SUM(internal_object_reserved_page_count) * 8 AS InternalObjectsKB,\n SUM(version_store_reserved_page_count) * 8 AS VersionStoreKB,\n SUM(unallocated_extent_page_count) * 8 AS FreeSpaceKB\nFROM sys.dm_db_file_space_usage;\n<\/code><\/pre>\n\n\n\n
\n\n\n\nBest Practices for Preventing TempDB Contention<\/h1>\n\n\n\n
\n
\n\n\n\nMonitoring TempDB Health with DBPulse<\/h1>\n\n\n\n
\n
PAGELATCH_UP<\/code> and PAGELATCH_EX<\/code> waits<\/li>\n\n\n\n
\n\n\n\nFrequently Asked Questions<\/h1>\n\n\n\n
What causes TempDB allocation contention?<\/h2>\n\n\n\n
\n\n\n\nWhat is the difference between PAGELATCH and PAGEIOLATCH?<\/h2>\n\n\n\n
PAGELATCH<\/code> waits occur in memory<\/strong> and indicate contention for internal pages. PAGEIOLATCH<\/code> waits indicate delays while reading data from disk.<\/p>\n\n\n\n
\n\n\n\nHow many TempDB files should I create?<\/h2>\n\n\n\n
\n\n\n\nAre trace flags 1117 and 1118 still required?<\/h2>\n\n\n\n
\n\n\n\nHow can I tell if TempDB contention is affecting my server?<\/h2>\n\n\n\n
PAGELATCH_UP<\/code> or PAGELATCH_EX<\/code> waits on database ID 2<\/strong>, along with slow query performance and heavy TempDB usage.<\/p>\n\n\n\n
\n\n\n\nConclusion<\/h1>\n\n\n\n
PAGELATCH_UP<\/code> and PAGELATCH_EX<\/code> waits indicate contention for allocation pages, preventing SQL Server from processing workloads efficiently.<\/p>\n\n\n\n