{"id":4165,"date":"2013-06-27T08:00:53","date_gmt":"2013-06-27T15:00:53","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4165"},"modified":"2020-05-03T17:06:06","modified_gmt":"2020-05-04T00:06:06","slug":"the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention\/","title":{"rendered":"The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention"},"content":{"rendered":"

(New\u00a0for 2020: we\u2019ve published a range of SQL Server interview candidate screening assessments with our partner Kandio, so you can avoid hiring an \u2018expert\u2019 who ends up causing problems. Check them out\u00a0here<\/a>.)<\/p>\n

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA<\/a>, which we present\u00a0several times each year<\/a>. You can find all the other posts in this series at\u00a0http:\/\/www.SQLskills.com\/help\/AccidentalDBA<\/a>. Enjoy!<\/em><\/p>\n

One of the most common performance problems that exists in SQL Server instances across the world is known as tempdb contention. What does that mean?<\/p>\n

Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I\/O.<\/p>\n

Consider the scenario of hundreds of concurrent queries that all create, use, and then drop small temporary tables (that by their very nature are always stored in tempdb). Each time a temp table is created, a data page must be allocated, plus an allocation metadata page to keep track of the data pages allocated to the table. This requires making a note in an allocation page (called a PFS page – see here<\/a> for in-depth info) that those two pages have been allocated in the database. When the temp table is dropped, those pages are deallocated, and they must be marked as such in that PFS page again. Only one thread at a time can be changing the allocation page, making it a hotspot and slowing down the overall workload.<\/p>\n

Back in SQL Server 2005, my dev team at Microsoft implemented a small cache of temp tables, to try to reduce this contention point, but it’s only a small cache, so it’s very common for this contention to be an issue, even today.<\/p>\n

What’s really interesting though, is that many people don’t realize they have this problem – even seasoned DBAs. It’s really easy to figure out whether you have this kind of problem using the\u00a0sys.dm_os_waiting_tasks<\/em> DMV. If you run the query I have below, you’ll get an idea of where the various threads on your server are waiting, as Erin discussed earlier this month<\/a>.<\/p>\n

\r\nSELECT\r\n    [owt].[session_id],\r\n    [owt].[exec_context_id],\r\n    [owt].[wait_duration_ms],\r\n    [owt].[wait_type],\r\n    [owt].[blocking_session_id],\r\n    [owt].[resource_description],\r\n    CASE [owt].[wait_type]\r\n        WHEN N'CXPACKET' THEN\r\n            RIGHT ([owt].[resource_description],\r\n            CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)\r\n        ELSE NULL\r\n    END AS [Node ID],\r\n    [es].[program_name],\r\n    [est].text,\r\n    [er].[database_id],\r\n    [eqp].[query_plan],\r\n    [er].[cpu_time]\r\nFROM sys.dm_os_waiting_tasks [owt]\r\nINNER JOIN sys.dm_exec_sessions [es] ON\r\n    [owt].[session_id] = [es].[session_id]\r\nINNER JOIN sys.dm_exec_requests [er] ON\r\n    [es].[session_id] = [er].[session_id]\r\nOUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]\r\nOUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]\r\nWHERE\r\n    [es].[is_user_process] = 1\r\nORDER BY\r\n    [owt].[session_id],\r\n    [owt].[exec_context_id];\r\nGO\r\n<\/pre>\n
Note that the [est].text<\/em> line does not have text<\/em> delimited – it throws off the plugin.<\/p>\n
If you see a lot of lines of output where the wait_type<\/em> is PAGELATCH_UP<\/em> or PAGELATCH_EX<\/em>, and the resource_description<\/em> is 2:1:1<\/em> then that’s the PFS page (database ID 2 – tempdb, file ID 1, page ID 1), and if you see\u00a02:1:3<\/em> then that’s another allocation page called an SGAM (more info here<\/a>).<\/p>\n
There are three things you can do to alleviate this kind of contention and increase the throughput of the overall workload:<\/p>\n
    \n
  1. Stop using temp tables<\/span><\/li>\n
  2. Enable trace flag 1118 as a start-up trace flag<\/li>\n
  3. Create multiple tempdb data files<\/li>\n<\/ol>\n
    Ok – so #1 is much easier said than done, but it does solve that problem :-) Seriously though, you might find that temp tables are a design pattern in your environment because they made a query go faster once and then everyone started using them, whether they’re *really* needed or not for enhancing performance and throughput. That’s a whole other topic though and outside the scope of this post.<\/p>\n
    #2 prevents contention on the SGAM pages by slightly changing the allocation algorithm used. There is no downside from having this enabled, and I even say that all SQL Server instances across the world should have this trace flag enabled by default (and I said the same thing when I ran the dev team that owned the allocation code in the SQL Server Storage Engine).<\/p>\n
    #3 will help to remove the PFS page contention, by spreading the allocation workload over multiple files, thus reducing contention on the individual, per-file PFS pages. But how many data files should you create?<\/p>\n
    The best guidance I’ve seen is from a great friend of mine, Bob Ward, who’s the top Escalation Engineer in Microsoft SQL Product Support. Figure out the number of logical processor cores you have (e.g. two CPUS, with 4 physical cores each, plus hyperthreading enabled = 2 (cpus) x 4 (cores) x 2 (hyperthreading) = 16 logical cores. Then if you have less than 8 logical cores, create the same number of data files as logical cores. If you have more than 8 logical cores, create 8 data files and then add more in chunks of 4 if you still see PFS contention. Make sure all the tempdb data files are the same size too. (This advice is now official Microsoft guidance in\u00a0KB article 2154845<\/a>.)<\/p>\n
    Here’s an example screenshot of a 100-connection workload on tempdb.<\/p>\n
    \"tempfiles\"<\/a><\/p>\n
    On the left was when I had a single tempdb data file and I was seeing nearly all the connections waiting for\u00a0PAGELATCH_UP<\/em>\u00a0on\u00a02:1:1<\/em>. After adding three more tempdb data files and stabilizing, the throughput jumped, as you can clearly see.<\/p>\n
    This has just been an overview of this problem, but as I said, it’s very common. You can get more information from the links below:<\/p>\n