I recently had an interesting production SQL Server issue that turned out to be very easy to fix. The fix doesn’t fit every workload or server, but for the limited use cases described below, it’s 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.
This covers a very specific scenario and workload. You won’t use this on a typical server, which is why it isn’t 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’t use this as a default setting for your servers.
Symptoms
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.
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.
If you’ve 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’t 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:
- Performance is impacted and standard troubleshooting is performed
- Large number of page latch waits are found
- The page latch waits are in tempdb
- Other page latch wait types are ruled out – leaving only metadata latches
- GAM
- SGAM
- PFS
- Enable server setting ‘tempdb metadata memory-optimized’ if appropriate and confirm improvement
- If you know this is a high transaction system that extensively uses temp tables, it’s an even stronger indication to make the change
Performance
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’t see an obvious cause, examine current queries and wait types on the server.
Wait types for current queries
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’s an indication to continue investigating possible latch contention. Other wait types don’t immediately rule out latch contention, but it’s a strong sign that it’s 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’t see it here.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT SP.spid ,SP.lastwaittype ,SP.status ,SP.cpu ,SP.physical_io ,SP.memusage FROM sys.sysprocesses SP WHERE SP.spid > 50 AND SP.status <> 'background' AND SP.open_tran > 0 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT wt.session_id ,wt.wait_type ,er.last_wait_type AS last_wait_type ,wt.wait_duration_ms ,wt.blocking_session_id ,wt.blocking_exec_context_id ,resource_description FROM sys.dm_os_waiting_tasks wt INNER JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id INNER JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id WHERE es.is_user_process = 1 AND wt.wait_type <> 'SLEEP_TASK' ORDER BY wt.wait_duration_ms desc |
Page latch waits in tempdb
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’t show up directly, instead the other latch types are eliminated.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT session_id ,wait_type ,wait_duration_ms ,blocking_session_id ,resource_description ,ResourceType = Case When TRY_CONVERT(int,Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3))) - 1 % 8088 = 0 Then 'Is PFS Page' When TRY_CONVERT(int,(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)))) - 2 % 511232 = 0 Then 'Is GAM Page' When TRY_CONVERT(int,(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)))) - 3 % 511232 = 0 Then 'Is SGAM Page' Else 'Is Not PFS, GAM, or SGAM page' End FROM sys.dm_os_waiting_tasks WT WHERE wait_type Like 'PAGE%LATCH_%' And resource_description Like '2:%' ORDER BY WT.session_id |
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.
Implementation
Since this impacts tempdb, the change is implemented at the server level using sp_configure. The setting is ‘tempdb metadata memory-optimized’. If you haven’t already enabled viewing advanced options, you’ll need to do it for this. If it returns a config value of 0, it needs to be changed to 1.
|
1 |
EXEC sp_configure 'show advanced options' |
If you see the following, change the value to 1. It doesn’t require a service restart and is safe to change even under load.
|
1 2 3 4 |
EXEC sp_configure 'show advanced options',1 GO RECONFIGURE GO |
Advanced options are now available, but this is just the preamble to making the actual change. First, confirm the current setting.
|
1 |
EXEC sp_configure 'tempdb metadata memory-optimized' |
If your server configuration is using the default, tempdb is not memory optimized, as shown here.
To enable tempdb to be metadata memory-optimized, change the value to 1, run the reconfigure command, and restart the service.
|
1 2 3 4 |
EXEC sp_configure 'tempdb metadata memory-optimized',1 GO RECONFIGURE GO |
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’t 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.
Reminder – This will disrupt your service and drop existing connections. If possible, do this during a maintenance window.
Once the service is restarted, confirm the setting is changed.
|
1 2 3 |
--Restart the SQL Service --Restarting the physical server isn't necessary - just the service EXEC sp_configure 'tempdb metadata memory-optimized' |
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’s a simple change that can have a big impact on systems experiencing this particular issue.
Reverting the Configuration
If server performance doesn’t 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’t expect that you would need to revert this change, but it’s always good to have a contingency plan.
|
1 2 3 4 5 6 |
--REVERT SETTING EXEC sp_configure 'tempdb metadata memory-optimized',0 GO RECONFIGURE GO --Remember to restart the SQL service for this to take effect |
Reproducing the use case
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’t run this in a production environment. I wouldn’t even run it in a shared environment – 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
--This would normally be a bad example - don't do this! --Used to simulate a high-volume / high concurrency --systems using many temp tables --Note that the tables are never used - this is a demonstration of metadata contention DECLARE @Counter int = 0 ,@SQL varchar(max) = '' WHILE @Counter < 100000 BEGIN SELECT @SQL = 'CREATE TABLE #' + CONVERT(varchar(10),@Counter) + '( TestTableId int NOT NULL PRIMARY KEY CLUSTERED identity ,Column1 varchar(100) DEFAULT (' + '''' + 'Test table column default' + '''' + ') ,Column2 int DEFAULT(' + CoNVERT(varchar(10),@Counter) + ') ) DROP TABLE #' + CONVERT(varchar(10),@Counter) + ' ' EXEC (@SQL) SELECT @Counter += 1 END |
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.
If you enable the setting ‘tempdb metadata memory-optimized’ on your test server and run the test scripts again, you will see improved performance and that the page latch contention is gone.
Summary
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.
Load comments