First, let me begin, we are avid users of all types of SQL Server encryption. We use Transparent Data Encryption (TDE), Cell Level Encryption and Always Encrypted. A few days ago, we started getting the wait, PREEMPTIVE_OS_CRYPTIMPORTKEY. This wait is described very well by SQL Skills team, https://www.sqlskills.com/help/waits/preemptive_os_cryptimportkey/. The wait started after we had patched our SQL Server. In addition, we changed our SQL Server to run with 24 cores (vCPUs, virtual machine).
Users started complaining the website of one product was unresponsive. The SQL Server is running multiple products. My go-to tool to start researching the SQL Server issues is Adam Machanic’s sp_whoisactive , http://whoisactive.com/. With sp_whoisactive, I was able to quickly find the culprit, PREEMPTIVE_OS_CRYPTIMPORTKEY. The wait was slowing down a stored procedure which is decrypting data with cell-level encryption. This slow down was also causing other processes to block behind the decryption process. Searching the web did not turn up a lot of information on this wait. We opened a help desk time with Microsoft to help resolve the performance issue.
Troubleshooting began, was the Anti-Virus causing the issue? No, Anti-Virus was not installed. Did we have an external key management system? No, we just used certificates created within SQL Server.
With the name of the wait, I knew SQL Server was waiting on the Windows Operating System. Even though the encryption certificate is stored in SQL Server, SQL Server still has to call out to the Windows Operating System to encrypt and decrypt the data which was also confirmed with Microsoft. Why was Windows Operating System taking so long to decrypt the data? Encryption is processed in the CPU.
We uninstalled the patches, but the performance issues were still happening. The only other thing which had changed was the addition of additional cores on the server. This was our issue but why we are running Enterprise edition of SQL Server. SQL Server Enterprise edition has unlimited cores, right? That is what I thought. Did you know there are two different Enterprise edition software you can install? The first Enterprise edition will limit your SQL Server to 20 cores. This is the Server + Cal license. The second is Core edition which is unlimited cores. We changed the server to only have 20 cores and the issue went away until we could upgrade our SQL Server to the Core Edition. You can upgrade the edition through the installation process. How do you know if you are limited to 20 cores? You can check this by executing the command:
if it returns
Enterprise Edition (64-bit)
you are limited to 20 cores because of CAL licensing.
If it returns
Enterprise Edition: Core-based licensing (64-bit).
If you are experiencing this wait in SQL Server, check your edition and the number of cores your SQL Server is running.