Michael J. Swart

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 check SERVERPROPERTY('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 from HKLM\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