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!

Plan Forcing in SQL Server

Last month I was in Portugal for their SQLSaturday event, and I spent a lot of time talking about Plan Forcing in SQL Server – both manual and automatic (via the Automatic Plan Correction feature). I had some really great questions from my pre-con and regular session and wanted to summarize a few thoughts on Plan Forcing functionality.

Forcing plans in SQL Server provides a very easy method for DBAs and developers to stabilize query performance. But plan forcing is not a permanent solution.  Consider the premise on which plan forcing relies: multiple plans exist for a query and one of them provides the most consistent performance.  If I have high variability in query performance, ideally, I want to address that in the code or through schema changes (e.g. indexing).  Forcing a plan for a query is a lot like creating a plan guide – they are similar but they are two separate features – in that it’s a temporary solution.  I also view adding OPTION (RECOMPILE) as a temporary solution. Some of you might be shocked at that, but when I see a RECOMPILE on a query, I immediately ask why it was added, when it was added, and I start looking at what can be done to remove it.

Knowing that this is how I view plan forcing, how do I decide when to force a plan?  When the query has variability in performance.

Consider Query A, which generates multiple plans, but they’re all about the same in terms of duration, I/O, and CPU.  The performance across the different plans is consistent.  I won’t force a plan for that query.

Query with multiple, consistent plans

Query with multiple, consistent plans

Next consider Query B, which also generates different plans, and some are stable but a couple are over the place in terms of duration, I/O, and CPU.  Maybe a couple plans provide good performance, but the rest are awful.  Would I force one of the “good plans”?  Probably – but I’d do some testing first.

Query with multiple plans that have variable performance

Query with multiple plans that have variable performance

Understand that if I force a plan for a query, that’s the plan that’s going to get used unless forcing fails for some reason (e.g. the index no longer exists).  But does that plan work for all variations of the query?  Does that plan provide consistent performance for all the different input parameters that can be used for that query?  This requires testing…and oh by the way, concurrent with any testing/decision to force a plan I’m talking to the developers about ways to address this long-term.

Now, out of my entire workload, if I have many queries that have multiple plans, where do I start?  With the worst offenders.  If I’m resource-bound in some way (e.g. CPU or I/O), then I would look at queries with the highest resource use and start working through those.  But I also look for the “death by a thousand cuts” scenario – the queries which execute hundreds or thousands of times a minute. As an aside, during the pre-con in Portugal one of the attendees had me look at a query in Query Store in the production environment.  There was concern because the query had multiple plans.  I pointed out that the query had executed 71,000 times in an hour…which is almost 20 times a second.  While I want to investigate multiple plans, I also want to know why a query executes so often.

Thus far, I’ve talked about a workload…one workload.  What about the environment where you support hundreds of SQL Server instances?  You can obviously take the approach I’ve detailed above, which requires a review of poor-performing queries with multiple plans and deciding which plan (if any) to force until development addresses the issue.  Or, if you’re running SQL Server 2017 Enterprise Edition, you could look at Automatic Plan Correction, which will force a plan for a query (without human intervention) if there’s a regression.  I wrote a post (Automatic Plan Correction in SQL Server) on SQLPerformance.com about this feature, so I’m not going to re-hash the details here.

Whether you force plans manually, or let SQL Server force them with the Automatic Plan Correction feature, I still view plan forcing as a temporary solution.  I don’t expect you to have plans forced for years, let alone months.  The life of a forced plan will, of course, depend on how quickly code and schema changes are ported to production.  If you go the “set it and forget it route”, theoretically a manually forced plan could get used for a very long time.  In that scenario, it’s your responsibility to periodically check to ensure that plan is still the “best” one for the query.  I would be checking every couple weeks; once a month at most.  Whether or not the plan remains optimal depends on the tables involved in the query, the data in the tables, how that data changes (if it changes), other schema changes that may be introduced, and more.

Further, you don’t want to ignore forced plans because there are cases where a forced plan won’t be used (you can use Extended Events to monitor this).  When you force a plan manually, forcing can still fail.  For example, if the forced plan uses an index and the index is dropped, or its definition is changed to the point where it cannot be used in plan in the same manner, then forcing will fail. Important note: if forcing fails, the query will go through normal optimization and compilation and it will execute; SQL Server does not want your query to fail!  If you’re forcing plans and not familiar with the reasons that it can fail, note the last_force_failure_reason values listed for sys.query_store_plan.  If you have manually forced a plan for a query, and the force plan fails, it remains forced.  You have to manually un-force it to stop SQL Server from trying to use that plan.  As you can see, there are multiple factors related to plan forcing, which is why you don’t just force a plan and forget it.

This behavior is different if you’re using Automatic Plan Correction (APC).  As mentioned in the Automatic tuning documentation, if a plan is automatically forced, it will be automatically un-forced if:

  • forcing fails for any reason
  • if there is a performance regression using the forced plan
  • if there is a recompile due to a schema change or an update to statistics.

With APC, there is still work to be done – here you want to use Extended Events or sys.dm_db_tuning_recommendations to see what plans are getting forced, and then decide if you want to force them manually. If you force a plan manually it will never be automatically un-forced.

There are a lot of considerations when you embrace plan forcing – I think it’s an excellent alternative to plan guides (much easier to use, not schema bound) and I think it’s absolutely worth a DBA or developer’s time to investigate what plan to force, and then use that as a temporary solution until a long-term fix can be put in place.  I hope this helps those of you that have been wary to give it a try!