When I talk about Plan Forcing I always discuss how users should ALTER procedures when using Query Store, and not use DROP and CREATE. This is valid beyond Plan Forcing cases; it’s a best practice I recommend however you are using Query Store. Every query stored in Query Store has an object_id associated with it, which ties it back to its object (stored procedure, function, etc.). This is critical not just for plan forcing, but also when you want to look at historical performance for a query after a change to the object.
Setup
Within the WideWorldImporters database, create a stored procedure with multiple statements:
USE [WideWorldImporters];
GO
CREATE OR ALTER PROCEDURE [Sales].[usp_GetCustomerDetail]
@CustomerName NVARCHAR(100)
AS
CREATE TABLE #CustomerList (
[RowID] INT IDENTITY (1,1),
[CustomerID] INT,
[CustomerName] NVARCHAR (100)
);
INSERT INTO #CustomerList (
[CustomerID],
[CustomerName]
)
SELECT
[CustomerID],
[Customername]
FROM [Sales].[Customers]
WHERE [CustomerName] LIKE @CustomerName
UNION
SELECT
[CustomerID],
[CustomerName]
FROM [Sales].[Customers_Archive]
WHERE [CustomerName] LIKE @CustomerName;
SELECT
[o].[CustomerID],
[o].[OrderID],
[il].[InvoiceLineID],
[o].[OrderDate],
[i].[InvoiceDate],
[ol].[StockItemID],
[ol].[Quantity],
[ol].[UnitPrice],
[il].[LineProfit]
INTO #CustomerOrders
FROM [Sales].[Orders] [o]
INNER JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID]
INNER JOIN [Sales].[Invoices] [i]
ON [o].[OrderID] = [i].[OrderID]
INNER JOIN [Sales].[InvoiceLines] [il]
ON [i].[InvoiceID] = [il].[InvoiceID]
AND [il].[StockItemID] = [ol].[StockItemID]
AND [il].[Quantity] = [ol].[Quantity]
AND [il].[UnitPrice] = [ol].[UnitPrice]
WHERE [o].[CustomerID] IN (SELECT [CustomerID] FROM #CustomerList);
SELECT
[cl].[CustomerName],
[si].[StockItemName],
SUM([co].[Quantity]) AS [QtyPurchased],
SUM([co].[Quantity]*[co].[UnitPrice]) AS [TotalCost],
[co].[LineProfit],
[co].[OrderDate],
DATEDIFF(DAY,[co].[OrderDate],[co].[InvoiceDate]) AS [DaystoInvoice]
FROM #CustomerOrders [co]
INNER JOIN #CustomerList [cl]
ON [co].[CustomerID] = [cl].[CustomerID]
INNER JOIN [Warehouse].[StockItems] [si]
ON [co].[StockItemID] = [si].[StockItemID]
GROUP BY [cl].[CustomerName], [si].[StockItemName],[co].[InvoiceLineID],
[co].[LineProfit], [co].[OrderDate], DATEDIFF(DAY,[co].[OrderDate],[co].[InvoiceDate])
ORDER BY [co].[OrderDate];
GO
Enable Query Store with most of the default settings; this is a demo, I’m not worried about configuration. This is not what I would do for production, see this post for more details on settings.. I also would not remove all data from Query Store in production, but again…this is a demo.
ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE = ON;
GO
ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
INTERVAL_LENGTH_MINUTES = 10
);
GO
/*
Do not run in a Production database unless you want
to remove all Query Store data
*/
ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE CLEAR;
GO
Then, execute the stored procedure a few times:
EXEC [Sales].[usp_GetCustomerDetail] N'Alvin Bollinger';
GO 10
EXEC [Sales].[usp_GetCustomerDetail] N'Tami Braggs';
GO 10
EXEC [Sales].[usp_GetCustomerDetail] N'Logan Dixon';
GO 10
EXEC [Sales].[usp_GetCustomerDetail] N'Tara Kotadia';
GO 10
Understanding the Data
It’s critical to understand what data exists in Query Store for this stored procedure. The query below interrogates the Query Store views to list all the statements in this stored procedure (note that there is no way to see this with any of the default reports):
SELECT
[qsq].[query_text_id],
[qsq].[query_id],
[qsq].[object_id],
[qsq].[context_settings_id],
[qst].[query_sql_text]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
WHERE [qsq].[object_id] = OBJECT_ID(N'Sales.usp_GetCustomerDetail');
GO
There are multiple columns that, when combined, create a unique query in Query Store. Three of those columns are query_text_id, context_settings_id, and object_id.
Drop and recreate the stored procedure, using:
DROP PROCEDURE IF EXISTS [Sales].[usp_GetCustomerDetail];
GO
Then execute the exact same CREATE OR REPLACE code from the previous section, and run the stored procedure again. Check the data in Query Store with the previous query:
Notice that the query_id and object_id have changed. The query_text_id is the same for all three queries (as is the context_settings_id), but the object_id changed – because we DROPPED the stored procedure, rather than ALTERing it – and therefore the query_id also changed.
The good news is that the original queries (and their plans and runtime statistics) are still in Query Store; they’re just harder to find:
SELECT
[qsq].[query_text_id],
[qsq].[query_id],
[qsq].[object_id],
OBJECT_NAME([qsq].[object_id]) AS [ObjectName],
[qsq].[context_settings_id],
[qst].[query_sql_text]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
WHERE [qsq].[object_id] > 0;
GO
Unless the object_id for the queries is saved/noted somewhere before the procedure is dropped and created, the only way to find the original queries is to query for object_id > 0 (which likely returns a lot of data) or search by the query_sql_text (which is not going to be fast):
SELECT
[qsq].[query_text_id],
[qsq].[query_id],
[qsq].[object_id],
OBJECT_NAME([qsq].[object_id]) AS [ObjectName],
[qsq].[context_settings_id],
[qst].[query_sql_text]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
WHERE [qst].[query_sql_text] LIKE '%INTO #CustomerOrders%';
GO
Action Item: Use ALTER
If you’re using Query Store, or planning to use it, you really need to ALTER procedures, rather than running DROP and CREATE statements. Note: You need to use ALTER PROCEDURE specifically. If you use CREATE OR ALTER procedure, while the object_id stays the same…a new query_id shows up in Query Store (even with no change to the query text). This is something I’m following up on – I don’t think this is expected behavior.
One thought on “ALTER Procedures When Using Query Store”