In this week’s Insider newsletter, Paul talked about an interesting customer issue I ran into last week related to Query Store, and an inadvertent change to a query’s text.  I thought I’d provide some demo code so that those of you using (or planning to use) Query Store could see how this shows up in your database.

Recreating the issue

I started by restoring a copy of the WideWorldImporters database, which you can download here.  I enabled Query Store and cleared out the data, just as a precaution.

USE [master];
GO
RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\Backups\WideWorldImporters-Full.bak' WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
NOUNLOAD,
REPLACE,
STATS = 5;
GO

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE = ON;
GO

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE CLEAR;
GO

I then created a stored procedure with just one query in it:

USE [WideWorldImporters];
GO

CREATE PROCEDURE [Sales].[usp_GetFullProductInfo]
@StockItemID INT
AS

SELECT
[o].[CustomerID],
[o].[OrderDate],
[ol].[StockItemID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID]
WHERE [ol].[StockItemID] = @StockItemID
ORDER BY [o].[OrderDate] DESC;
GO

Then I executed the stored procedure twice, with two different input parameters.  I purposely used WITH RECOMPILE here because I knew the values would generate different plans.

EXEC [Sales].[usp_GetFullProductInfo] 90 WITH RECOMPILE;
GO

EXEC [Sales].[usp_GetFullProductInfo] 224 WITH RECOMPILE;
GO

Within Object Explorer I then drilled into the Query Store reports (Databases | WideWorldImporters | Query Store) and double-clicked to run Top Resource Consuming Queries.  My query has two different plans:

Initial query execution, two plans

Plan_id 1 has a clustered index scan with a filter, that feeds into the probe phase of a hash match (with a columnstore index scan for the build). This was generated with the input value of 90.

Plan 1 for query_id 1

Plan_id 2 has the same columnstore index scan, but it feeds into a nested loop, with a clustered index seek for inner input, and was generated with the input value of 224.

Plan 2 for query_id 1

Now let’s suppose I decide to force one plan for that query. After analysis, I decide to force the second plan (plan_id 2), which I can do either through the UI, or using a stored procedure. Since change control and documentation is a good thing, we’ll use the stored procedure:

EXEC sp_query_store_force_plan @query_id = 3, @plan_id = 8;
GO

At this point, if I re-run the stored procedure with the two different input parameters and WITH RECOMPILE, I will get the plan with the nested loop.

EXEC [Sales].[usp_GetFullProductInfo] 90 WITH RECOMPILE;
GO

EXEC [Sales].[usp_GetFullProductInfo] 224 WITH RECOMPILE;
GO

SP execution after plan is forced

Now…let’s assume that this forced plan is used going forward and there are no issues. And then let’s assume that a developer decides to add some comments to the stored procedure, and they know they should use ALTER to keep the object_id the same…but somehow, a couple extra spaces end up in the query.

ALTER PROCEDURE [Sales].[usp_GetFullProductInfo]
@StockItemID INT
AS
/*
This is getting information based on ID
*may need to add more columns per BL
*/
SELECT
[o].[CustomerID],
[o].[OrderDate],
[ol].[StockItemID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID]
WHERE [ol].[StockItemID] = @StockItemID
ORDER BY [o].[OrderDate] DESC;

GO

I re-run the stored procedures again (WITH RECOMPILE) and now I get that other plan again for the input value of 90:

SP execution after ALTER, spaces inadvertenly added

If I look in Query Store, I can see I now have two queries that look nearly identical:

Two queries in QS after SP was changed

There are different query_id values in sys.query_store_query, as well as different entries for each query in sys.query_store_query_text. Both queries are still tied to the stored procedure (object_id 1483152329) which can be confusing if you don’t understand what occurred. In order to confirm which query has executed most recently, you can use the query below, which joins both system views (replace the query_ids as appropriate):

SELECT
[qst].[query_text_id],
[qst].[query_sql_text],
[qst].[statement_sql_handle],
[qs].[query_id],
[qs].[query_hash],
[qs].[last_execution_time],
[qs].[object_id]
FROM [sys].[query_store_query] [qs]
JOIN [sys].[query_store_query_text] [qst]
ON [qs].[query_text_id] = [qst].[query_text_id]
WHERE [qs].[query_id] IN (1, 14);
GO

Information for both queries from Query Store views

Summary

The take home message here is that you need to be careful when changing objects (stored procedures, functions) where you have queries with forced plans. Inadvertent changes in spacing or casing create entirely separate queries in Query Store, which means that the original query with a forced plan is no longer in use.  You’ll either have to ALTER the procedure again without the extra spaces in the query (which may not be as easy as it sounds), or get the new version of the query to execute the “right” plan, and then force that plan.  It may be appropriate to set up monitoring for object changes, or teach your developers how to identify what queries are forced and to what objects they below (hint: use the Queries With Forced Plans report that’s available in SSMS 17.x). Hope this helps, and if you want to learn more about Query Store, consider my full day pre-con at Summit next month where we’ll cover this type of scenario and a whole lot more! Feel free to reach out if you have questions about it 🙂

p.s. did you see that they announced the release date for SQL Server 2017? I’ll be running that build at my pre-con!