4

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?

Here is a minimal, complete, and verifiable example showing the issue:

CREATE OR ALTER PROCEDURE dbo.test_proc
AS
BEGIN
    SELECT 0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D;
END
GO
CREATE OR ALTER PROCEDURE dbo.spQueryPlanCache @textstring nvarchar(max)
WITH ENCRYPTION
AS
BEGIN
    SELECT deqp.query_plan
    FROM sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
    WHERE dest.text LIKE '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%'
        AND deqp.query_plan IS NOT NULL;
END
GO

--output from this will contain the query plan, with the StatementText column "encrypted".
EXEC dbo.spQueryPlanCache @textstring = '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%';

GO
CREATE OR ALTER PROCEDURE dbo.test_enc
(
    @xml nvarchar(max)
)
WITH ENCRYPTION
AS
BEGIN
    SELECT CONVERT(xml, @xml);
END
GO

DECLARE @val nvarchar(max);

SELECT TOP(1)
    @val = CONVERT(nvarchar(max), deqp.query_plan)
FROM sys.dm_exec_query_stats deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
    CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
WHERE dest.text LIKE '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%'
    AND deqp.query_plan IS NOT NULL;

--output here will show the same query plan, but the STatementText won't be encrypted.
EXEC dbo.test_enc @val;

The same behavior has been exhibited on 2014, 2016, and 2019.

0

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.