Encrypted Stored Procedures and Query Store

Very often I get interesting questions from the community, and most recently someone asked about encrypted stored procedures and Query Store.  Here’s the question (paraphrased):

I have some encrypted stored procedures in one of my vendor databases.  I have enabled Query Store, and I can see query_id values and runtime statistics, and when a query has multiple plans, I can see some of the execution plans, but not others.  If I force a plan for a query that’s part of an encrypted stored procedure, will it work?

There are two issues here:

  • Why are only some plans showing up in Query Store for a query that’s part of an encrypted stored procedure?
  • Can I force a plan for a query that’s part of an encrypted stored procedure?

When in doubt, test.

Setup

For testing I will use a copy of WideWorldImporters that’s been enlarged, so that there’s some variability in the data.  After it’s restored we will enable Query Store and clear out any old data.

USE [master];
GO
RESTORE DATABASE [WideWorldImporters]
     FROM  DISK = N'C:\Backups\WideWorldImporters_Bits.bak'
     WITH  FILE = 1,
     STATS = 5;
GO

/*
     Enable Query Store with settings we want
     (just for testing, not for production)
*/
USE [master];
GO
ALTER DATABASE [WideWorldImporters]
     SET QUERY_STORE = ON;
GO

ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (
     OPERATION_MODE = READ_WRITE,
     CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
     DATA_FLUSH_INTERVAL_SECONDS = 60,
     INTERVAL_LENGTH_MINUTES = 30,
     MAX_STORAGE_SIZE_MB = 100,
     QUERY_CAPTURE_MODE = ALL,
     SIZE_BASED_CLEANUP_MODE = AUTO,
     MAX_PLANS_PER_QUERY = 200)
GO

/*
     Clear out any old data, just in case
     (not for production either!)
*/
ALTER DATABASE [WideWorldImporters]
     SET QUERY_STORE CLEAR;
GO

With the database restored, we will create our encrypted stored procedure:

USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Sales].[usp_CustomerTransactionInfo];
GO

CREATE PROCEDURE [Sales].[usp_CustomerTransactionInfo]
     @CustomerID INT
WITH ENCRYPTION
AS
     SELECT [CustomerID], SUM([AmountExcludingTax])
     FROM [Sales].[CustomerTransactions]
     WHERE [CustomerID] = @CustomerID
     GROUP BY [CustomerID];
GO

Testing

To create two different plans, we will execute the procedure twice, once with a unique value (1092), and once with a non-unique value (401), with a sp_recompile in between:

EXEC [Sales].[usp_CustomerTransactionInfo] 1092;
GO

sp_recompile 'Sales.usp_CustomerTransactionInfo';
GO

EXEC [Sales].[usp_CustomerTransactionInfo] 401;
GO

Note that if you enable the actual query plan option in Management Studio, no plan will appear.  You won’t find a plan in the plan cache (sys.dm_exec_query_plan), nor will you find one if you run Profiler or Extended Events.  But when you use encrypted stored procedures and Query Store, you can get the plan:

SELECT
     [qsq].[query_id],
     [qsp].[plan_id],
     [qsp].[is_forced_plan],
     [qsq].[object_id],
     [rs].[count_executions],
     DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())),
     [qsp].[last_execution_time]) AS [LocalLastExecutionTime],
     [qst].[query_sql_text],
     ConvertedPlan = TRY_CONVERT(XML, [qsp].[query_plan])
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
     ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp]
     ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
     ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [qsq].[object_id] = OBJECT_ID(N'Sales.usp_CustomerTransactionInfo')
ORDER BY [qsq].[query_id], [qsp].[plan_id];
GO
Query and Plan Info in Query Store
Query and Plan Info in Query Store

 

Both plans can be viewed, but the query text is not part of the plan, and it is not in Query Store.

Plan for query_id 1
Plan for query_id 1

 

Plan for query_id 2
Plan for query_id 2

We can still force a plan, though:

EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;
GO

How will we know if a forced plan is used when we run the query?

EXEC [Sales].[usp_CustomerTransactionInfo] 1050;
GO 5

EXEC [Sales].[usp_CustomerTransactionInfo] 401;
GO 5

Typically, I would run the query in SSMS and get the actual execution plan, and check the UsePlan parameter to see if it’s true.  But the plan won’t show up in SSMS, so we have to look in Query Store.  If we re-run the query above again, we see that the execution count has increased, and the last_execution_time has also changed.

Query Store Information After Plan is Forced
Query Store Information After Plan is Forced

Conclusion

Based on the data in Query Store, when you force a plan for a query that’s part of an encrypted stored procedure, it is used.  You will have to verify its use in Query Store, as other troubleshooting tools (plans, DMVs, XE) do not provide data to confirm.

As for why the execution plan for a query does or does not show up in Query Store, I’m not sure.  I couldn’t recreate the behavior where the plan did not appear in Query Store, but I’m hoping the person who sent in the question can help me figure out why.  Stay tuned!

One thought on “Encrypted Stored Procedures and Query Store

  1. Hi Erin,

    thank you for the info. When I found this post I was hoping that Query Store could help us to get Query Plans as we also have our DB encrypted but I can confirm a strange behavior of plan collection in encrypted DB. It really collects some query plans for some procedures although most of them is without a plan. I haven’t found a pattern yet of why some procs are without a plan. We have procs, triggers, functions encrypted and procs go up to 30 nesting levels. I tested on SQL2017 Dev edition, compat mode 140. XML format of a missing plant looks like this (hope it will not be filtered out by html)

    Erik

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.