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!

Query Store Options in SSMS 18.4

Last week the SQL Server Tools team released a new version of Management Studio, 18.4, and it dropped in the middle of my Query Store full day pre-con on Monday. Those two are related. There were some changes in SSMS 18.4 specific to Query Store, and for a few seconds over lunch I thought about downloading the new version and running it for my afternoon demos. But…I didn’t want to anger the demo Gods, so I stuck with 18.3.

What’s New?

The list of features and fixes for each release of SSMS can be found here, and for this post I’m interested in the features related to Query Store. Within Management Studio we’ll create a new database named QS_Settings and turn on Query Store with the default options:

/*
     Of note: I NEVER recommend creating a database
     with this syntax, I am being lazy and just accepting all
     defaults, JUST for demo purposes
*/
USE [master];
GO
CREATE DATABASE [QS_Settings];
GO
ALTER DATABASE [QS_Settings] SET QUERY_STORE = ON;
GO
ALTER DATABASE [QS_Settings] SET QUERY_STORE (
     OPERATION_MODE = READ_WRITE
     );
GO

If we look at the Query Store page on the Database Properties window, you’ll see some additional entries:

Query Store options in SSMS 18.4

Query Store options in SSMS 18.4

For comparison, here is a screen shot of the same window in SSMS 18.3:

Query Store options in SSMS 18.3 and below

Query Store options in SSMS 18.3 and below

There are two options that, previously, you could only modify using T-SQL:

  • MAX_PLANS_PER_QUERY
  • WAIT_STATS_CAPTURE_MODE

And there is an entirely new section specific to the CUSTOM value for QUERY_CAPTURE_MODE, which was added in SQL Server 2019. In addition, the default values for some settings changed in SQL Server 2019.

Setting MAX_PLANS_PER_QUERY and WAIT_STATS_CAPTURE_MODE

When you enable Query Store in any version of SQL Server, by default the value for MAX_PLANS_PER_QUERY is 200. As mentioned in my Query Store Settings post, I recommend leaving this setting at the default value. The only case where I would change it is if I find that I have queries with more than 200 plans. If you are running SSMS 18.3 or below, or Azure Data Studio (ADS), you can use:

USE [master];
GO
ALTER DATABASE [QS_Settings] SET QUERY_STORE (
     MAX_PLANS_PER_QUERY = 500
     );
GO

When you enable Query Store in SQL Server 2017 or 2019, WAIT_STATS_CAPTURE_MODE is enabled by default (remember that query-level wait statistics were not available in Query Store in SQL Server 2016). When you upgrade to SQL Server 2017 or 2019 from SQL Server 2016, and you have Query Store enabled,  WAIT_STATS_CAPTURE_MODE is automatically enabled. If you want to turn it off for some reason, and you’re using SSMS 18.3 or below, or ADS, you can run:

USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (
     WAIT_STATS_CAPTURE_MODE = OFF
     )
GO

With SSMS 18.4, you now have the ability to change both options via the UI. In general, I recommend using the T-SQL because then it can go into change control, and you don’t have to worry about altering another setting by mistake.

CUSTOM Options for QUERY_CAPTURE_MODE

In SQL Server 2019 we now have the ability to customize queries that are captured based on execution count, total compile CPU time (ms), OR total execution CPU time (ms), within a defined window of time. This provides much more control than the AUTO option, which excludes queries based on internally-determined thresholds set by Microsoft, which are not documented. The CUSTOM option is a game-changer, as it allows those with ad hoc workloads to use Query Store and minimize the performance overhead.

Changing to CUSTOM

When upgrading a database from SQL Server 2016 or 2017 to SQL Server 2019, with Query Store already enabled, the value for QUERY_CAPTURE_MODE will not change. To enable and customize the options if you are running SSMS 18.3 or lower, or ADS, you can use:

USE [master];
GO
ALTER DATABASE [QS_Settings] SET QUERY_STORE (
     QUERY_CAPTURE_MODE = CUSTOM,
     QUERY_CAPTURE_POLICY = (
          STALE_CAPTURE_POLICY_THRESHOLD = 1 HOURS, 
          EXECUTION_COUNT = 30,
          TOTAL_COMPILE_CPU_TIME_MS = 1000, 
          TOTAL_EXECUTION_CPU_TIME_MS = 100)
          )
GO

Within SSMS 18.4, when you change QUERY_CAPTURE_MODE to CUSTOM in the UI you will see:

Options for CUSTOM capture policy

Options for CUSTOM capture policy

At this time, I do not have recommendations, based on data, for these options. I have one customer running SQL Server 2019, and I haven’t heard from many people who are running SQL Server 2019 and have Query Store enabled.  Yet. Over time, I am hoping to get better information, but my initial suggestion is that you make a best estimate and then monitor. The default values are a good starting point. The minimum window of time (STALE_CAPTURE_POLICY_THRESHOLD) is one hour, which is also the default. Note that the drop-down for this option contains a list of values:

STALE_CAPTURE_POLICY_THRESHOLD drop-down options

STALE_CAPTURE_POLICY_THRESHOLD drop-down options

Unlike INTERVAL_LENGTH_MINUTES, these are not the only values that you can use. For example, you can set STALE_CAPTURE_POLICY_THRESHOLD to a value not in the list, such as two hours, using T-SQL:

USE [master];
GO
ALTER DATABASE [QS_Settings] SET QUERY_STORE (
     QUERY_CAPTURE_MODE = CUSTOM, 
     QUERY_CAPTURE_POLICY = (
          STALE_CAPTURE_POLICY_THRESHOLD = 2 HOURS)
     );
GO

The value of 2 HOURS will then appear in the drop-down list going forward, which I find interesting, but that’s another rabbit hole to explore. Acceptable values for this option range from 1 hour to 7 days. While you can be clever and choose something like 42 hours, think about what window of time is appropriate for a business day, whether it’s an 8-hour day or a 24-hour day because you’re 24×7.

Thresholds

For EXECUTION_COUNT, the default value is 30, which is appropriate for an ad hoc workload as most of the time as queries only execute one time. Determining values for TOTAL_COMPILE_CPU_TIME_MS and TOTAL_EXECUTION_CPU_TIME_MS is a bit trickier. The defaults are 1000ms and 100ms, respectively. The settings are milliseconds, but the data in the Query Store table is stored in microseconds; remember this when you’re comparing your settings to what is in Query Store.

Think about the execution time first. Do you have a threshold where if a query requires more than X amount of CPU time, you’re concerned? Maybe 100ms is too low, and 1000ms (1 second) is more appropriate for your environment.

Compilation time is the hardest. I would not recommend setting it below 1000ms until you can look at captured data in Query Store. Understand that both compilation and CPU time are cumulative for these thresholds. If a query executions 5 times (below your EXECUTION_COUNT threshold, but takes 25ms of TOTAL_EXECUTION_CPU_TIME_MS each time, then on that fifth execution it will exceed 100ms and subsequent executions will be captured.

Summary

For anyone using SQL Server 2019, I highly recommend using SSMS 18.4, particularly if you use Query Store, or plan on using it. If you’re running SQL Server 2016 or 2017, it’s nice to see those additional settings in the UI, and in general I recommend staying current with releases for all the tools, assuming you don’t have business rules that limit or control what you can install.

Query Store Questions: 24HOP follow up

 

A couple weeks ago I presented for the 24HOP Summit Preview and I had a lot of great general questions about how Query Store works.  My session title was “Why You Need Query Store” (you can watch it here) and I only had about 45 minutes.  As you can probably guess – since I have a full day pre-con on the topic – I can talk about Query Store for a loooong time 🙂  The 24HOP session was really focused on getting folks to understand THE most important things that Query Store provides to show why it’s needed.  I left out a lot of details about HOW Query Store works because talking through it is the fun stuff that I’ll dive into during the pre-con.  I did have a good number of questions from attendees related to specific functionality, and I promised to write a post answering them.  Questions* and answers are below…if you need clarification on anything, please leave a comment and I’ll follow up!

*Questions copied exactly as they were shared with me, I did not try to re-word or make any inferences about what was being asked.

Questions

1. Can I get Query Store data for a production database deployed on a client site, that I don’t actually have access to myself? Can the DBA send me something I can use in my own development environment?

A: You provide instructions to the client that explain how to enable Query Store, either through the UI or with T-SQL.  If you want to view that Query Store data, the client can either send you a backup of the database or create a clone with DBCC CLONEDATABASE and share that.

 

2. If a user executes a stored procedure from ‘master’ it is not captured?

A: If you have Query Store enabled for a user database, and you execute a query against that user database from the context of the master database, it is not supposed to be captured in Query Store for that user database.  But in my testing, it is.  But it is not supposed to be, so there is no guarantee it will always work that way.

 

3. If your database is part of an AG the data you look at can be different based on the server it is running on at that time, correct?

A: I’m not quite clear what’s being asked, but I wrote a post about Query Store and Availability Groups, which will hopefully answer the question.

 

4. Is it easy to remove the forcing of a given plan?

A: Yes, just use the “Unforce Plan” button in the UI, or use the stored procedure sp_query_store_unforce_plan (you supply the query_id and plan_id).

 

5. If you have 3+ plans how does SQL Server decide which plan to use?

A: I assume this is specific to the Automatic Plan Correction feature, and if so, it will force the last good plan (most recent plan that performed better than the current plan).  More details in my Automatic Plan Correction in SQL Server post.

 

6. What equivalent options we have for lower versions?

A:  There is an open-source tool called Open Query Store for versions prior to SQL Server 2016.

 

7. Why are the trace flags not on by default? given the issues with AlwaysOn and QS

A: Great question.  Trace flag 7752 will be default functionality in SQL Server 2019.  TF 7745 is not default functionality because, I suspect, of the potential for losing Query Store data…and SQL Server wants you to make a conscious choice about that.  More details in Query Store Trace Flags.

 

8. How would you use Query Store to troubleshoot Views?

A: Query Store does not differentiate between a query that references a view and a query that references a table.  It does not capture the object_id of the view and store that in Query Store (as happens for a stored procedure), so you have to look specifically for the view name in the query_sql_text column (within sys.query_store_query_text) to look for queries that reference the view.

 

9. Is there any way to make use of Query Store in readonly secondary AG replicas?

A: You can read data from the Query Store views on a read-only replica, but you cannot capture data in Query Store about queries executing against the read-only replica.  See my post referenced in #3, and then please up-vote this request: Enable Query Store for collection on a read-only replica in an Availability Group.

 

10. Is it possible that a query store run from one instance to another instance for example I want check the queries of production from dev instance?

A: If you can connect to the production instance from the dev instance, and have appropriate permissions, then you can query the Query Store data on the production instance (but the data exist in the production database).

 

11. If I execute a parameterized query with OPTION (RECOMPILE), will Query Store have the parameter values of every execution?

A: No.  The plan will have the values used for the initial execution that generated said plan, but values for every individual execution are not captured (it would generate excessive overhead to capture every execution).

 

12. Can Query Store supply the T-SQL to force plan?

A: The UI does not provide an option to script forcing a plan for a query, but if you are using Automatic Plan Correction, the T-SQL to force it can be found in sys.dm_db_tuning_recommendations.

 

13. Will there be any significant performance overhead by using query store?

A: See Query Store Performance Overhead: What you need to know

 

14. How does it function when queries span multiple databases?

A: As alluded to in question #2, cross-database queries are tricky.  You should work under the assumption that if you execute a query from Database_A, where Database_A has Query Store enabled, it will be captured.  If you execute a query from Database_A that queries both Database_A and Database_B, and both databases have Query Store enabled, it will ONLY be captured in Database_A.

 

15. It seems to be working for me, but sometimes not

A: I would love to help you out, just not sure of the behavior you’re seeing and what your question is.

 

16. How do you get the full query text from inside the ‘Top Resource Consuming Queries’ windows?

A: Click on the button with the grid and magnifying glass, which says “View the query text of the selected query in a query editor window” when you hover over it.

Button to display query text to help understand how query store works

Button to “View the query text of the selected query in a query editor window”

 

17. Is the data will be stored in Query Store After the adhoc/SP completed or it will do while is running?

A: Once the plan has been compiled for a query, the query text and plan are sent to Query Store.  When execution completes, the runtime statistics are sent to Query Store.

 

18. If we change the compatibility to SQL 2012 or lower, will that affect Query store?

A: No, Query Store functions in SQL Server 2016 and higher, and Azure SQL Database, regardless of your compatibility mode.

 

19. If we drop a SP, will that clear the history of that SP plans in the query store?

A: No, but…If you use DROP PROCEDURE syntax, then the object_id column in sys.query_store_query will no longer reference an existing object (in sys.objects).  The query and plans will stay in Query Store until they are aged out based on the retention policy.

 

Follow Up

Again, if any answers are unclear, leave a comment and I can clarify.  If you are interested in learning more about Query Store I would love to see you in my full day session at the PASS Summit!  It’s on Monday, November 5th, and you get more details here: Performance Tuning with Query Store in SQL Server