Freigeben über

OPEN SYMMETRIC KEY command prevents query plan caching

  • Artikel

This was quite an interesting issue. We started troubleshooting a SQL Server blocking problem due to COMPILE lock blocking. The KB Article: Description of SQL Server blocking caused by compile locks explains why COMPILE lock blocking is encountered on a SQL Server instance. Now there must be a catch here J, otherwise why would I be penning a blog post to talk about some KB Article published eons ago. While troubleshooting the issue, it was identified that a particular Stored Procedure which is called heavily by the application is actually the problem. All possibilities were explored such as not using schema qualified name for the SP, query plan of the SP getting flushed out and procedure recompiles.

While analyzing the profiler trace I found that the every time the SP was executed, there was no plan that was being inserted into the Procedure Cache. Digging deeper, I found that there was no SP:Cache Insert event being fired for the SP in question. I was able to reproduce the issue by using the T-SQL script given below. When I captured a SQL Profiler trace along with the SP:Cache Insert, SP: Cache Miss, SP: Recompile and SP: Cache Miss events along with other RPC events. You will notice that there will is SP:CacheInsert event which signifies that the query plan was stored in the SQL Procedure Cache. And no matter how many times I executed the SP, I didn’t see a Cache Insert. You will only see a SP: CacheMiss which indicates that the query was not found in the procedure cache.

Before you start crying FOUL, this is a by-design issue for a security reason. As OPEN SYMMETRIC KEY contains sensitive information (the password), hence we mark the batch as not cacheable. If a query plan is getting cached, you should see a SP:CacheInsert event getting fired for the procedure when it is executed for the first time.

There are two ways to work around this:

1. Use EXECUTE or sp_executesql to execute the OPEN SYMMTERIC KEY command

2. Use DecryptByKeyAutoCert instead of OPEN SYMMETRIC KEY

Option 1

When you use this option, you will find that the EXECUTE or sp_executesql batch is always compiled and can cause some delay. The reason for this is again for security reasons but if you have a large SP which has an OPEN SYMMETRIC KEY command, then you can avoid significant compile time and reduce the compilation to just one statement.

If you look above that there will be a SP:CacheMiss for the OPEN SYMMETRIC KEY command but the SP plan is found in the cache.

The modified SP will look as follows:

CREATE PROC [dbo].[usp_fetchdata3]

@a int

AS

declare @sql varchar(8000)

set @sql = 'OPEN SYMMETRIC KEY TestSymKey DECRYPTION BY PASSWORD = ''pGFD4bb925DGvbd2439587y'''

exec (@sql)

select a,b,c,CONVERT(varchar(1000), DecryptByKey(c))

from tblEncrypt where a > @a

CLOSE SYMMETRIC KEY TestSymKey

Option 2

If you use the DecryptByKeyAutoCert method, the password doesn’t need to be explicitly used if the Symmetric Key used to encrypt the data is protected by a certificate. In such a case, you will not run into the COMPILE lock blocking issue due to non-cacheable nature of the query.

 

You will notice above that the entire SP plan is cached (SP:CacheInsert event fired) and the plan will be reused for the next execution of the SP. The modification for the SP will look at as follows:

CREATE CERTIFICATE cert01 ENCRYPTION BY PASSWORD = '(*&^%YFSFHGSDbe!' WITH SUBJECT = 'Test'

go

OPEN SYMMETRIC KEY TestSymKey

DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y';

go

ALTER SYMMETRIC KEY TestSymKey ADD ENCRYPTION BY CERTIFICATE cert01

go

CLOSE SYMMETRIC KEY TestSymKey

go

CREATE PROC [dbo].[usp_fetchdata2]

@a int

AS

select a,b,c,CONVERT(varchar(1000),

     -- No need to explicitly open the key for decryption.

     DecryptByKeyAutoCert (cert_ID('cert01'),N'(*&^%YFSFHGSDbe!', c))

from tblEncrypt

where a > @a

go

Script to reproduce the issue

CREATE SYMMETRIC KEY TestSymKey WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y';

   

OPEN SYMMETRIC KEY TestSymKey

DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y';

CREATE TABLE tblEncrypt (a int, b varchar(1000), c varbinary(128))

SET NOCOUNT ON

DECLARE @var int

SET @var = 0

WHILE @var < 1000

BEGIN

 INSERT INTO tblEncrypt (a,b) VALUES (@var, 'DATA' + CAST (@var as varchar(10)))

 SET @var = @var + 1

END

UPDATE tblEncrypt SET c = EncryptByKey(Key_GUID('TestSymKey'), b);

CREATE PROC [dbo].[usp_fetchdata]

@a int

AS

OPEN SYMMETRIC KEY TestSymKey

DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y';

select a,b,c,CONVERT(varchar(1000), DecryptByKey(c))

from tblEncrypt where a > @a

CLOSE SYMMETRIC KEY TestSymKey

 

Regards,
Amit Banerjee
SEE, Microsoft SQL support

Comments

  • Anonymous
    February 07, 2011
    Wow!  This was a wonderful post!  I always suspected the symmetric key was causing the slow downs on our system but couldn't figure out what to do about it.  You saved me a lot of headaches.  Thank you!

  • Anonymous
    October 03, 2014
    Thanks a lot for this wonderful article. We was worried a lot for last one month to find this problem . Now this article gave a idea to overcome our problem . thanks a ton.

  • Anonymous
    May 10, 2015
    Good one

  • Anonymous
    November 03, 2015
    it helped us a lot