3

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?

Thanks

  • Should have mentioned - I've confirmed this behaviour on both 2014 and 2016. – FrugalShaun Nov 5 '19 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.