Changes to query text and the effects in Query Store

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
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 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
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
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
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
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
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!

8 thoughts on “Changes to query text and the effects in Query Store

  1. It looks like “Plan 2 for query_id 1” image does not show the plan that you are talking about, but the first one.

    1. Hi Gavin-

      You want to know if *what* will happen if Query Store is disabled? If Query Store is disabled you can’t force a query plan, so this scenario is moot. If you change the text for a stored procedure (using ALTER for the SP), then the query with changed text is a new query so that would go through compilation/optimization. Not really sure what you’re asking so feel free to clarify.

      Erin

  2. I’ve seen similar cases where a procedure was ALTERed without any changes at all, and still new query_id values were generated. Bug or feature?

    1. Alex-

      I’d argue that’s a bug, can you reproduce it? Because if so, I’d create a User Voice item.

      Erin

      1. Did some digging — this only happens for queries that reference temp tables or table variables. These statements get a new generated batch_sql_handle on every ALTER, which is part of the key used to identify a query.

        I kind of understand why they had to do that, but I wish there was a better way, since this means the Query Store doesn’t correctly keep track of such queries and forced plans become ineffective incorrectly.

        1. Alex, nice work finding that – I haven’t seen that yet, but I would bet it’s quite common. Do you have a blog? You should write about that! Also, I would recommend creating a User Voice (formerly Connect) item to raise the concern to Microsoft and see if they can provide an alternative. Happy to help you get it up-voted and raise awareness, just let me know.

          Erin

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.