A friend of mine from Tampa, Florida wrote me the other day with an interesting observation:
"I have a small finding and don't know where to publish it. Maybe you can help get the word out via your blog?
OPEN SYMMETRIC KEY is somewhat costly to execute. Since keys will remain open unless explicitly closed, we wrote a little stored proc to only open the key if it's not already open:
if not exists(select 1 from sys.openkeys where...)
open symmetric key...
Seemed to work ok. I'm sure this is not news to you, just laying the groundwork here.
Later on during performance testing, comparing encryption vs non-encryption approach we noticed pretty big impact, traced it to our little "open key" stored proc. It wasn't "recompiling" but was "compiling" every time it was called. We could see this in profiler by monitoring sp:cachehit and sp:cachemiss...this proc was always a miss.
Only way we were able to "fix" this was to put the OPEN SYMMETRIC KEY stmt in its own 1-line stored proc. We modified our original proc to call this new helper proc whenever it needed to open the key. This yielded significantly better performance, and all we see now are sp:cachehit events in profiler.
This may or may not be a flaw in the way things work on the SQL server, but it's certainly easy enough to work around once you know it's a problem. I haven't seen any articles addressing it, maybe you can help get this out?"
Actually, I do know why this happens. To prevent a brute-force attack where someone sniffs packets or reads memory, SQL Server doesn't cache any statement where a password or encrypted content is stored. It seems more important to protect the data than to speed it up, but of course that's always a tradeoff. What my friend is doing with his code is "priming the pump" with the key open function, which speeds things up nicely.