{"id":105047,"date":"2025-01-08T06:56:48","date_gmt":"2025-01-08T06:56:48","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=105047"},"modified":"2025-01-08T06:56:49","modified_gmt":"2025-01-08T06:56:49","slug":"a-use-case-for-memory-optimized-tempdb-metadata","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/a-use-case-for-memory-optimized-tempdb-metadata\/","title":{"rendered":"A Use Case for Memory-Optimized Tempdb Metadata"},"content":{"rendered":"

I recently had an interesting production SQL Server issue that turned out to be very easy to fix. The fix doesn\u2019t fit every workload or server, but for the limited use cases described below, it\u2019s a simple configuration change. The general best practice for server level configurations is to leave things at default, unless there is a specific reason for changing the settings. This will outline a use case for using memory-optimized tempdb metadata.<\/p>\n

This covers a very specific scenario and workload. You won\u2019t use this on a typical server, which is why it isn\u2019t a default setting. You will see this for very specific server workloads, those with many transactions and high temp table usage. Most systems can better use the memory for the regular workload instead of optimizing tempdb metadata, so don\u2019t use this as a default setting for your servers.<\/p>\n

Symptoms<\/h2>\n

Symptoms for the issue started with reports of a very poor performing system. Digging into the details showed 100% CPU utilization on the SQL Server and queries performing much slower than normal. After eliminating deadlocks and excessive blocking, current queries were examined and their wait types. Wait types are an important health indicator and will be one of the first things you want to check when troubleshooting generalized issues.<\/p>\n

Changing server configuration options presupposes that a lot of additional database and server tuning has already happened. You want to be sure the general database health is good, indexes are getting rebuilt on a regular basis, statistics are updated, recommended \/ necessary indexes are in place, and queries are optimized. You also need to check for deadlocks and blocking.<\/p>\n

If you\u2019ve done your standard troubleshooting and notice a high number of page latch waits, your server might be a candidate for memory-optimized tempdb metadata. Latches are light weight locks used internally by the system. You don\u2019t need to know all of the details to diagnose this issue, but they are described in the first link in the references. The confirmation steps to diagnose this issue will follow the general pattern:<\/p>\n

    \n
  1. Performance is impacted and standard troubleshooting is performed<\/li>\n
  2. Large number of page latch waits are found<\/li>\n
  3. The page latch waits are in tempdb<\/li>\n
  4. Other page latch wait types are ruled out \u2013 leaving only metadata latches\n
      \n
    1. GAM<\/li>\n
    2. SGAM<\/li>\n
    3. PFS<\/li>\n<\/ol>\n<\/li>\n
    4. Enable server setting ‘tempdb metadata memory-optimized’ if appropriate and confirm improvement\n
        \n
      1. If you know this is a high transaction system that extensively uses temp tables, it\u2019s an even stronger indication to make the change<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

        Performance<\/h3>\n

        You might be lucky enough to catch this before you start having performance issues, but you will likely notice this first due to poor performance. It may be high CPU usage, or just very poor SQL Server performance. Conduct your standard troubleshooting here, including server diagnostics. Once you have confirmed the server is performing poorly and you don\u2019t see an obvious cause, examine current queries and wait types on the server.<\/p>\n

        Wait types for current queries<\/h3>\n

        Part of performance checks include looking at current queries and their wait types. The following shows current processes and their associated wait types. If there are a significant number of page latch waits, it\u2019s an indication to continue investigating possible latch contention. Other wait types don\u2019t immediately rule out latch contention, but it\u2019s a strong sign that it\u2019s a different issue. Keep an eye on wait types as you continue troubleshooting and monitoring, but it may not be the cause of your performance problems if you don\u2019t see it here.<\/p>\n

        SELECT\n\tSP.spid\n\t,SP.lastwaittype\n\t,SP.status\n\t,SP.cpu\n\t,SP.physical_io\n\t,SP.memusage\nFROM sys.sysprocesses SP\nWHERE SP.spid > 50\n\tAND SP.status <> 'background'\n\tAND SP.open_tran > 0<\/pre>\n
        <\/p>\n
        If you see page latch wait types when looking at current processes, you need to get additional details. You can look at waiting tasks and the current sessions to get more information. The resource description in the query shows the details for the resource, including the database ID. The number in the resource_description column before the colon indicates the database ID. Tempdb is always 2, as shown in the example below. If you see latch waits concentrated in tempdb, continue investigating. If the page latches are not in tempdb, you can use the Microsoft troubleshooting guide listed in the references.<\/p>\n
        SELECT\n\twt.session_id\n\t,wt.wait_type\n\t,er.last_wait_type AS last_wait_type\n\t,wt.wait_duration_ms\n\t,wt.blocking_session_id\n\t,wt.blocking_exec_context_id\n\t,resource_description\nFROM sys.dm_os_waiting_tasks wt\n\tINNER JOIN sys.dm_exec_sessions es \n\t\tON wt.session_id = es.session_id\n\tINNER JOIN sys.dm_exec_requests er \n\t\tON wt.session_id = er.session_id\nWHERE es.is_user_process = 1\n\tAND wt.wait_type <> 'SLEEP_TASK'\nORDER BY wt.wait_duration_ms desc<\/pre>\n
        <\/p>\n
        Page latch waits in tempdb<\/h3>\n
        After you have confirmed you are experiencing latch contention in tempdb, you need to confirm that the latches are not GAM, SGAM or PFS latches. Metadata latches don\u2019t show up directly, instead the other latch types are eliminated.<\/p>\n
        SELECT \n\tsession_id\n\t,wait_type\n\t,wait_duration_ms\n\t,blocking_session_id\n\t,resource_description\n\t,ResourceType = Case\n\t\t\tWhen TRY_CONVERT(int,Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3))) - 1 % 8088 = 0 Then 'Is PFS Page'\n\t\t\tWhen TRY_CONVERT(int,(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)))) - 2 % 511232 = 0 Then 'Is GAM Page'\n\t\t\tWhen TRY_CONVERT(int,(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)))) - 3 % 511232 = 0 Then 'Is SGAM Page'\n\t\t\tElse 'Is Not PFS, GAM, or SGAM page'\n\t\tEnd\nFROM sys.dm_os_waiting_tasks WT\nWHERE wait_type Like 'PAGE%LATCH_%'\n\tAnd resource_description Like '2:%'\nORDER BY WT.session_id<\/pre>\n
        <\/p>\n
        After you have confirmed you have tempdb metadata latch contention, and the issue persists, consider enabling the memory optimized configuration for tempdb. If you want additional confirmation, support can be engaged at this time. Be sure to include your troubleshooting steps to expedite your ticket.<\/p>\n
        Implementation<\/h2>\n
        Since this impacts tempdb, the change is implemented at the server level using sp_configure. The setting is \u2018tempdb metadata memory-optimized\u2019. If you haven\u2019t already enabled viewing advanced options, you\u2019ll need to do it for this. If it returns a config value of 0, it needs to be changed to 1.<\/p>\n
        EXEC sp_configure 'show advanced options'<\/pre>\n
        If you see the following, change the value to 1. It doesn\u2019t require a service restart and is safe to change even under load.<\/p>\n
        <\/p>\n
        EXEC sp_configure 'show advanced options',1\nGO\nRECONFIGURE\nGO<\/pre>\n
        Advanced options are now available, but this is just the preamble to making the actual change. First, confirm the current setting.<\/p>\n
        EXEC sp_configure 'tempdb metadata memory-optimized'<\/pre>\n
        If your server configuration is using the default, tempdb is not memory optimized, as shown here.<\/p>\n
        <\/p>\n
        To enable tempdb to be metadata memory-optimized, change the value to 1, run the reconfigure command, and restart the service.<\/p>\n
        EXEC sp_configure 'tempdb metadata memory-optimized',1\nGO\nRECONFIGURE\nGO<\/pre>\n
        The SQL Server service now needs to be restarted. Ideally, changes like this happen during your regular maintenance window, but you may not realize you need this change until it is a production issue and the server is already struggling. You don\u2019t have to restart the server, but the SQL Server service does need to restart using your usual method. The restart will result in a disruption of service. The disruption will be limited if you have a high-availability setup, but it will impact current connections.<\/p>\n
        Reminder – This will disrupt your service and drop existing connections. If possible, do this during a maintenance window.<\/strong><\/p>\n
        <\/p>\n
        Once the service is restarted, confirm the setting is changed.<\/p>\n
        --Restart the SQL Service\n--Restarting the physical server isn't necessary - just the service\nEXEC sp_configure 'tempdb metadata memory-optimized'<\/pre>\n
        <\/p>\n
        You now need to check your server health. Look at your wait types to confirm that you no longer have latch contention in tempdb metadata using the same methods described above. You also need to confirm that query and server performance have improved. It\u2019s a simple change that can have a big impact on systems experiencing this particular issue.<\/p>\n
        Reverting the Configuration<\/h2>\n
        If server performance doesn\u2019t improve or you have additional issues with this setting change, the fix is to revert the setting back to 0 and restart the service. If you confirmed that the server is having metadata latch contention in tempdb (and you did the other troubleshooting steps), I wouldn\u2019t expect that you would need to revert this change, but it\u2019s always good to have a contingency plan.<\/p>\n
        --REVERT SETTING\nEXEC sp_configure 'tempdb metadata memory-optimized',0\nGO\nRECONFIGURE\nGO\n--Remember to restart the SQL service for this to take effect<\/pre>\n
        Reproducing the use case<\/h2>\n
        This use case can be demonstrated by creating as many temp tables as possible, as quickly as possible. If you run a script like the following, in multiple connections in SSMS simultaneously, you will be able to see the problem. I was able to produce the issue locally with 5 scripts running. As noted in the script, don\u2019t run this in a production environment. I wouldn\u2019t even run it in a shared environment \u2013 it will cause performance problems, including high CPU usage. The good news is, there is no cleanup needed when you are done testing. Performance will return to normal once the scripts are stopped or once they finish creating temp tables.<\/p>\n
        --This would normally be a bad example - don't do this!\n--Used to simulate a high-volume \/ high concurrency\n--systems using many temp tables\n--Note that the tables are never used - this is a demonstration of metadata contention\nDECLARE @Counter\tint = 0\n\t,@SQL\t\t\tvarchar(max) = ''\nWHILE @Counter < 100000\nBEGIN\n\tSELECT @SQL = 'CREATE TABLE #' + CONVERT(varchar(10),@Counter) + '(\n\tTestTableId\t\tint\t\tNOT NULL PRIMARY KEY CLUSTERED identity\n\t,Column1\t\tvarchar(100)\tDEFAULT (' + '''' + 'Test table column default' + '''' + ')\n\t,Column2\t\tint\t\t\t\tDEFAULT(' + CoNVERT(varchar(10),@Counter) + ')\n\t)\n\t\t\n\tDROP TABLE #' + CONVERT(varchar(10),@Counter) + '\n\t'\n\tEXEC (@SQL)\n\tSELECT @Counter += 1\nEND<\/pre>\n
        CPU spikes when the script is run in multiple connections. If you run the troubleshooting queries from the previous sections, you will see page latch contention and you will also see that the contention is in tempdb. This query is how I reproduced the issue for my examples.<\/p>\n
        <\/p>\n
        If you enable the setting \u2018tempdb metadata memory-optimized\u2019 on your test server and run the test scripts again, you will see improved performance and that the page latch contention is gone.<\/p>\n
        Summary<\/h2>\n
        This is one use case for enabling the memory optimized tempdb metadata server setting. If your system has a high number of concurrent transactions and uses temp tables extensively, it may be a good fit. It worked well in the scenario I have described above, but remember to always test changes to the system settings in SQL Server and confirm improvement after making changes. The general best practice is to leave things at the installation default unless you have a specific reason to make the change. Check your usage patterns, look at your wait types, and be ready to revert this setting if it degrades performance. Opening a support ticket is always an option if you need additional confirmation before making a server configuration change in a high value production environment.<\/p>\n
        References<\/h2>\n