{"id":1169,"date":"2020-10-08T11:36:29","date_gmt":"2020-10-08T18:36:29","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1169"},"modified":"2021-02-17T18:45:28","modified_gmt":"2021-02-18T02:45:28","slug":"alter-procedures-when-using-query-store","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/","title":{"rendered":"ALTER Procedures When Using Query Store"},"content":{"rendered":"\n

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<\/em> 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<\/em> a change to the object.<\/p>\n\n\n\n

Setup <\/h2>\n\n\n\n

Within the WideWorldImporters database, create a stored procedure with multiple statements:<\/p>\n\n\n\n

\n

USE [WideWorldImporters];
GO<\/code><\/p>\n\n\n\n

CREATE OR ALTER PROCEDURE [Sales].[usp_GetCustomerDetail]
@CustomerName NVARCHAR(100)
AS<\/code><\/p>\n\n\n\n

CREATE TABLE #CustomerList (
[RowID] INT IDENTITY (1,1),
[CustomerID] INT,
[CustomerName] NVARCHAR (100)
);<\/code><\/p>\n\n\n\n

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;<\/code><\/p>\n\n\n\n

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);<\/code><\/p>\n\n\n\n

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];<\/code><\/p>\n\n\n\n

GO<\/code><\/p>\n<\/div><\/div>\n\n\n\n

Enable Query Store with most of the default settings; this is a demo, I’m not worried about configuration. This is not <\/strong>what I would do for production, see this post<\/a> for more details on settings.. I also would not remove all data from Query Store in production, but again…this is a demo.<\/p>\n\n\n\n

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE = ON;
GO<\/code><\/p>\n\n\n\n

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
INTERVAL_LENGTH_MINUTES = 10
);
GO<\/code><\/p>\n\n\n\n

\/*
Do not run in a Production database unless you want
to remove all Query Store data
*\/<\/code><\/p>\n\n\n\n

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE CLEAR;
GO<\/code><\/p>\n\n\n\n

Then, execute the stored procedure a few times:<\/p>\n\n\n\n

EXEC [Sales].[usp_GetCustomerDetail] N'Alvin Bollinger';
GO 10<\/code><\/p>\n\n\n\n

EXEC [Sales].[usp_GetCustomerDetail] N'Tami Braggs';
GO 10<\/code><\/p>\n\n\n\n

EXEC [Sales].[usp_GetCustomerDetail] N'Logan Dixon';
GO 10<\/code><\/p>\n\n\n\n

EXEC [Sales].[usp_GetCustomerDetail] N'Tara Kotadia';
GO 10<\/code><\/p>\n\n\n\n

Understanding the Data<\/h2>\n\n\n\n

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):<\/p>\n\n\n\n

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<\/code><\/p>\n\n\n\n

\"Query
Query Information in QS<\/figcaption><\/figure>\n\n\n\n

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. <\/p>\n\n\n\n

Drop and recreate the stored procedure, using:<\/p>\n\n\n\n

DROP PROCEDURE IF EXISTS [Sales].[usp_GetCustomerDetail];
GO<\/code><\/p>\n\n\n\n

Then execute the exact same<\/strong> CREATE OR REPLACE code from the previous section, and run the stored procedure again. Check the data in Query Store with the previous query:<\/p>\n\n\n\n

\"Query
Query Information in QS after DROP AND CREATE<\/figcaption><\/figure>\n\n\n\n

Notice that the query_id<\/span> and object_id<\/span> 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<\/em> changed.<\/p>\n\n\n\n

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:<\/p>\n\n\n\n

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<\/code><\/p>\n\n\n\n

\"All
All queries in QS after DROP AND CREATE<\/figcaption><\/figure>\n\n\n\n

Unless the object_id for the queries is saved\/noted somewhere before<\/em> 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<\/em> of data) or search by the query_sql_text (which is not going to be fast):<\/p>\n\n\n\n

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<\/code><\/p>\n\n\n\n

\"Queries
Queries returned based on a search of query_sql_text<\/figcaption><\/figure>\n\n\n\n

Action Item: Use ALTER<\/h2>\n\n\n\n

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.<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"

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, […]<\/p>\n","protected":false},"author":7,"featured_media":1176,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46],"tags":[],"yoast_head":"\nALTER Procedures When Using Query Store - Erin Stellato<\/title>\n<meta name=\"description\" content=\"If you use Query Store, it's important to use ALTER when changing objects - like stored procedures - instead of using DROP and CREATE.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ALTER Procedures When Using Query Store - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"If you use Query Store, it's important to use ALTER when changing objects - like stored procedures - instead of using DROP and CREATE.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-08T18:36:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-02-18T02:45:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/10\/QueryInfo5.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1376\" \/>\n\t<meta property=\"og:image:height\" content=\"211\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/\",\"name\":\"ALTER Procedures When Using Query Store - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2020-10-08T18:36:29+00:00\",\"dateModified\":\"2021-02-18T02:45:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"If you use Query Store, it's important to use ALTER when changing objects - like stored procedures - instead of using DROP and CREATE.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ALTER Procedures When Using Query Store\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"ALTER Procedures When Using Query Store - Erin Stellato","description":"If you use Query Store, it's important to use ALTER when changing objects - like stored procedures - instead of using DROP and CREATE.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/","og_locale":"en_US","og_type":"article","og_title":"ALTER Procedures When Using Query Store - Erin Stellato","og_description":"If you use Query Store, it's important to use ALTER when changing objects - like stored procedures - instead of using DROP and CREATE.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/","og_site_name":"Erin Stellato","article_published_time":"2020-10-08T18:36:29+00:00","article_modified_time":"2021-02-18T02:45:28+00:00","og_image":[{"width":1376,"height":211,"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/10\/QueryInfo5.jpg","type":"image\/jpeg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/","name":"ALTER Procedures When Using Query Store - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2020-10-08T18:36:29+00:00","dateModified":"2021-02-18T02:45:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"If you use Query Store, it's important to use ALTER when changing objects - like stored procedures - instead of using DROP and CREATE.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-procedures-when-using-query-store\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"ALTER Procedures When Using Query Store"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1169"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=1169"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1169\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media\/1176"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}