I want to describe some symptoms that SQL Server may display when its Windows Registry is non-responsive or slow. From the symptoms, it’s hard to know that it’s a slow registry and so if a web search brought you here, hopefully this helps.
How does SQL Server use the Windows registry?
First, it’s useful to know a bit about how SQL Server uses the registry. We can watch registry activity using Process Monitor (procmon). On a fairly quiet local machine, I see these SQL Server processes “querying” registry keys:
- There is some background process reading Query Store settings (every minute).
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer\QueryStoreSettings
- There is also some background process writing uptime info (every minute).
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer\uptime_pid
- When a login is requested from a new connection, SQL Server will check to see if R Services are installed (aka Advanced Analytics).
SQL Server will checkSERVERPROPERTY('IsAdvancedAnalyticsInstalled')
every time to see if it has to care about logins associated with something called implied authentication. This happens on every login which will be important later.
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Setup\AdvancedAnalytics
- If I use a function like HASHBYTES, SQL Server looks up some cryptography settings. These settings get queried only on the first call to HASHBYTES in each session.
e.g.HKLM\SOFTWARE\Microsoft\Cryptography\Defaults\Provider\Microsoft Enhanced RSA and AES Cryptographic Provider
That’s not an exhaustive list, there are many other ways SQL Server uses the Windows Registry. For example:
- Many SQL Agent settings are stored there and are read regularly
xp_regread
coming from using wizards in SQL Server Management Studio.SERVERPROPERTY(N'MachineName')
gets its info fromHKLM\System\CurrentControlSet\Services\Tcpip\Parameters\Hostname
- And many others.
What happens when the Windows registry is slow?
SQL Server’s use of the registry can be fairly quiet – even on a busy server – so you may not see any symptoms at all. But if the calls to the registry are slow in responding, here is what you might see:
- New logins will ask whether Advanced Analytics Extensions is installed. Leading to a non-yielding scheduler and a memory dump. With some effort, you might find a stack trace like the one in the appendix below.
- Any other kind of memory dump caused by non-yielding schedulers in which the saved stack trace ends with
ntdll!NtOpenKeyEx
. The AdvancedAnalytics is just one example but it’s the most common because it’s executed first on each login. - Queries calling HASHBYTES (or other cryptography functions) will be suspended and wait with
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT
. I mostly see this when the login checks are skipped i.e. when an open connection from a connection pool is used. - Another symptom is Availability Group failovers (allegedly). It’s harder (for me) to do AG failover post mortems and tie them definitively to slow Windows registries
Why might the registry be slow?
I’m not sure. Perhaps it’s associated with some registry cleanup process. It may have something to do with an IO spike on the C: drive.
We rebuilt a virtual machine image from scratch which seems to avoid the problem. I’m keeping my fingers crossed.
I’d love to hear if you’ve come across anything similar.
Appendix: Sample call stack for non-yielding scheduler
00 ntdll!NtOpenKeyEx 01 KERNELBASE!LocalBaseRegOpenKey 02 KERNELBASE!RegOpenKeyExInternalW 03 KERNELBASE!RegOpenKeyExW 04 sqlmin!IniRegOpenKeyExW 05 sqlmin!GetServerProperty 06 sqlmin!IsAdvancedAnalyticsInstalled 07 sqllang!IsExtensibilityFeatureEnabled 08 sqllang!ImpliedAuthenticationManager::IsImpliedAuthenticationEnabled 09 sqllang!FindLogin 0a sqllang!login 0b sqllang!process_login_finish 0c sqllang!process_login 0d sqllang!process_commands_internal 0e sqllang!process_messages 0f sqldk!SOS_Task::Param::Execute 10 sqldk!SOS_Scheduler::RunTask 11 sqldk!SOS_Scheduler::ProcessTasks 12 sqldk!SchedulerManager::WorkerEntryPoint 13 sqldk!SystemThreadDispatcher::ProcessWorker 14 sqldk!SchedulerManager::ThreadEntryPoint 15 kernel32!BaseThreadInitThunk 16 ntdll!RtlUserThreadStart |
[…] Michael J. Swart diagnoses issues when the Windows registry slows down operations: […]
Pingback by SQL Server and the Slow Registry – Curated SQL — September 15, 2022 @ 8:10 am
Some other fun related wait types:

PREEMPTIVE_OS_GETPROCADDRESS?
Absent seems to be PREEMPTIVE_OS_QUERY_REGISTRY <shrug>
Comment by Michael J. Swart — September 22, 2022 @ 11:17 am