Sql-server – SP with Encryption Read/Writes Stats

Don't blindly rely on the sys.dm_db_index_usage_stats as it does not tell you the entire story.

Refer to Joe Sack's

• Exceptions – what sys.dm_db_index_usage_stats doesn’t tell you
• Exceptions–what sys.dm_db_index_usage_stats doesn’t tell you (Part II)

Why would this infrequently used index be taking up so much space in the buffer pool?

In simple terms, SQL Server tends to rely on Buffer Pool to do its normal operations to avoid expensive disk I/O's. If you have enough RAM and have set up MAX Memory appropriately, then all the queries that are ran across your database will CACHE the objects that are required by your queries.

Doesn't the high number of user_updates compared to the low number of scans and seeks make this a candidate for removal due to maintenance overhead?

It can be the case where the index from your perspective is not useful, but there might be queries that require that index or part of that Index.

I agree that caching such a huge index and if it is really not useful - is a waste of Buffer Pool and you might run into performance issues like more I/O's or excessive plan recompilations if the plan cache is too constrained.

Should a field with such low cardinality be indexed in the first place?

Not really as maintaining an index - it costs you for modifications like insert, update and deletes as well as disk space. Also, low cardinality indexes might be ignored by the Query Optimizer as it wont help generate an efficient query plan - its not just selective enough.Try to look into Filtered indexes.

Is the only way to clear this from the buffer pool to drop and recreate? Clearing the entire cache or buffer pool is obviously not possible on a production box.

Unfortunately, DBCC DROPCLEANBUFFERS does not have any parameters for specific database or objects.

Best is to rebuild the index.

OK, In case anyone wants to know, I got to the bottom of this.

The chain is pretty easy to work out:

Create Service Master Key (automatically done upon install)

Create Database Master Key (user creates their own key)

SymetricEncrypt(Service Key,Database Master Key) -> Store Encrypted Master Key into Master DB

Create Certificate

SymetricEncrypt(Database Master Key, Certificate Private Key) -> Store Encrypted private key in Master DB

Create Database Encryption Key (system creates a random key, but must be manually added to database)

ASymetricEncrypt(Certificate, Database Encryption Key) -> Store Encrypted DEK into database.

So, to decrypt the database data, you'll need to:

Use the SMK to decrypt the DMK

Use the DMK to decrypt the cert private key

Use the certificate to decrypt the DEK

Use the DEK to decrypt the database

The starting point is the Service Master Key... how is this stored (if you encrypt it, you can't retrieve data, and if left in plaintext your system is exposed).

The answer is, the SMK is encrypted with Data Protection API, which is a service built into windows.

Basically, data is encrypted with "secrets" from your user profile. So the same user profile must be used to decrypt the SMK.

Now that I know this, I can use DPAPI in my applications to encrypt my encryption keys :)