I'm having a really bizarre issue when querying the plan cache that relates to stored procedure encryption. However, it's the stored procedure that is querying the plan cache that is encrypted, not the queries in the plan cache themselves. Let me explain...
The procedure below simply searches the plan cache for a matching string and outputs the query plan (the last column).
ALTER PROCEDURE spQueryPlanCache @textstring nvarchar(max) AS BEGIN SELECT databases.name, dm_exec_sql_text.text AS TSQL_Text, dm_exec_query_stats.creation_time, dm_exec_query_stats.execution_count, dm_exec_query_stats.total_worker_time AS total_cpu_time, dm_exec_query_stats.total_elapsed_time, dm_exec_query_stats.total_logical_reads, dm_exec_query_stats.total_physical_reads, dm_exec_query_plan.query_plan FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle) CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle) INNER JOIN sys.databases ON dm_exec_sql_text.dbid = databases.database_id WHERE dm_exec_sql_text.text LIKE @textstring END
This is a sample of the plan XML that is outputted in the last column:
<StmtSimple StatementText="CREATE PROCEDURE [dbo].[spMySproc];1 (@...
Everything looks normal - my plan cache sproc has found a procedure called spMySproc in the plan cache and I can see the statement text. Great!
However.....if I add WITH ENCRYPTION to the definition of my plan cache query procedure (spQueryPlanCache) and re-run it, the query_plan column from the plan cache that is OUTPUTTED by my encrypted stored procedure has itself been encrypted!!!
<StmtSimple StatementText="** Encrypted Text **"
This makes no sense to me. The only circumstance where I would expect this to happen is if spMySproc was also encrypted, which it isn't! If I query the plan cache again, I can see that spMySproc is not encrypted.
Is this a known bug, or can someone explain why this makes sense to them?