{"id":1051,"date":"2019-09-12T11:54:58","date_gmt":"2019-09-12T18:54:58","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1051"},"modified":"2019-09-12T11:55:14","modified_gmt":"2019-09-12T18:55:14","slug":"why-you-need-query-store-part-iii-proactively-analyze-your-workload","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/","title":{"rendered":"Why You Need Query Store, Part III: Proactively analyze your workload"},"content":{"rendered":"

 <\/p>\n

The amount of data collected by Query Store pales in comparison to the amount of data available in all of SQL Server that could<\/em> be captured.\u00a0 But the quality<\/em> of the data overshadows the quantity, or lack thereof.\u00a0 While most people target the execution statistics when viewing historical data in Query Store, I would argue that the query text and query plan information are equally as valuable when it comes to taking the time to analyze your workload.<\/p>\n

The query text and plan are stored in two separate tables, sys.query_store_query_text and sys.query_store_plan, respectively.\u00a0 The text is stored as a nvarchar(max) data type, and the plan is stored as varbinary(max).\u00a0 This means that the data can be mined, looking for patterns and explicit use of objects.\u00a0 Want to know what queries use an index?\u00a0 Look for it in the plans. \u00a0Want to know what queries have a RECOMPILE<\/a> hint on them?\u00a0 Look for it in the query text.\u00a0 Anything you want to find with regard to query text or plans is there, you just have to know how to query the data.<\/p>\n

Mining Query Store: Queries<\/h2>\n

But wait, can\u2019t we just search the text?\u00a0 Sure, with the query_text field you can just wildcard your way through it looking for things.\u00a0 For example, the query below will help you find every statement in Query Store that has RECOMPILE in the text.<\/p>\n

\r\nSELECT\r\n     [qsq].[query_id],\r\n     [qsp].[plan_id],\r\n     [rs].[last_execution_time],\r\n     [rs].[avg_duration],\r\n     [rs].[avg_logical_io_reads],\r\n     [qst].[query_sql_text],\r\n     TRY_CONVERT(XML, [qsp].[query_plan]) AS [QueryPlan_XML]\r\nFROM [sys].[query_store_query] [qsq]\r\nJOIN [sys].[query_store_query_text] [qst]\r\n     ON [qsq].[query_text_id] = [qst].[query_text_id]\r\nJOIN [sys].[query_store_plan] [qsp]\r\n     ON [qsq].[query_id] = [qsp].[query_id]\r\nJOIN [sys].[query_store_runtime_stats] [rs]\r\n     ON [qsp].[plan_id] = [rs].[plan_id]\r\nWHERE [qst].[query_sql_text] LIKE '%RECOMPILE%';\r\nGO\r\n<\/pre>\n
Mining Query Store: Plans<\/h2>\n
Query plans have a specific format, defined by Microsoft<\/a>, which can be searched using wildcards, but the performance is abysmal and you\u2019re better served using XQuery.\u00a0 That\u2019s right, I said XQuery.\u00a0 If you don\u2019t know, XQuery and I are frenemies.\u00a0 I love the information I get when using it, but writing the T-SQL always makes me want to compulsively eat M&Ms and animal crackers until I feel sick.<\/p>\n
Fortunately for me, Jonathan writes XQuery and creates the foundation of the queries I manage to cobble together for Query Store.\u00a0 In his post, Finding what queries in the plan cache use a specific index<\/a>, Jonathan has the code to look through the plan cache and find what queries use a specific index.\u00a0 The challenge is that the plan cache can be volatile, particularly in an ad hoc workload.\u00a0 Even in a parameterized\/procedural workload, there is churn and the plans that are cache change as different business processes run throughout the day, week, and month.<\/p>\n
Consider the scenario where you use sys.dm_db_index_usage_stats<\/a> to determine if an index is being used, and when you find one that it isn\u2019t\u2026.how do you really<\/em> know?\u00a0 As we know, the plan cache is transitory, so a query that uses it might not be in cache at that moment.\u00a0 But it would be in Query Store, depending on your retention settings.\u00a0 If we modify the query from Jonathan\u2019s post, we can interrogate the Query Store data:<\/p>\n
\r\nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\nGO\r\n\r\nDECLARE @IndexName AS NVARCHAR(128) = '[FK_Sales_InvoiceLines_InvoiceID]';\r\n-- Make sure the name passed is appropriately quoted\r\n\r\nIF (LEFT(@IndexName, 1) &lt;&gt; '[' AND RIGHT(@IndexName, 1) &lt;&gt; ']') SET @IndexName = QUOTENAME(@IndexName);\r\n--Handle the case where the left or right was quoted manually but not the opposite side\r\nIF LEFT(@IndexName, 1) &lt;&gt; '[' SET @IndexName = '['+@IndexName;\r\nIF RIGHT(@IndexName, 1) &lt;&gt; ']' SET @IndexName = @IndexName + ']';\r\n;WITH XMLNAMESPACES\r\n(DEFAULT 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan')\r\nSELECT\r\n     stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,\r\n     obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName,\r\n     obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName,\r\n     obj.value('(@Table)[1]', 'varchar(128)') AS TableName,\r\n     obj.value('(@Index)[1]', 'varchar(128)') AS IndexName,\r\n     obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind,\r\n     query_plan\r\nFROM\r\n(\r\n     SELECT query_plan\r\n     FROM\r\n     (\r\n          SELECT TRY_CONVERT(XML, [qsp].[query_plan]) AS [query_plan]\r\n          FROM sys.query_store_plan [qsp]) tp\r\n          ) AS tab (query_plan)\r\n     CROSS APPLY query_plan.nodes('\/ShowPlanXML\/BatchSequence\/Batch\/Statements\/StmtSimple') AS batch(stmt)\r\n     CROSS APPLY stmt.nodes('.\/\/IndexScan\/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)\r\nOPTION(MAXDOP 1, RECOMPILE);\r\n<\/pre>\n
Summary<\/h2>\n
Beyond looking at performance metrics over time for a query, take advantage of the plethora of data in the Query Store tables.\u00a0 Use both the text and plan information to proactively look for anti-patterns in your workload: use of index hints, RECOMPILE, NO LOCK\u2026all the things that may cause problems, and understand both object access and expensive operations in the plans.\u00a0 There is no limit to the patterns, and anti-patterns, you can find.\u00a0 You just have to write the code.<\/p>\n","protected":false},"excerpt":{"rendered":"
  The amount of data collected by Query Store pales in comparison to the amount of data available in all of SQL Server that could be captured.\u00a0 But the quality of the data overshadows the quantity, or lack thereof.\u00a0 While most people target the execution statistics when viewing historical data in Query Store, I would […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[52,46],"tags":[],"yoast_head":"\nWhy You Need Query Store, Part III: Proactively analyze your workload - Erin Stellato<\/title>\n<meta name=\"description\" content=\"The data captured by Query Store is extremely helpful for troubleshooting performance issues, and it's invaluable if you want to analyze your workload.\" \/>\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\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Why You Need Query Store, Part III: Proactively analyze your workload - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"The data captured by Query Store is extremely helpful for troubleshooting performance issues, and it's invaluable if you want to analyze your workload.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-12T18:54:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-12T18:55:14+00:00\" \/>\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=\"4 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\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/\",\"name\":\"Why You Need Query Store, Part III: Proactively analyze your workload - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-09-12T18:54:58+00:00\",\"dateModified\":\"2019-09-12T18:55:14+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"The data captured by Query Store is extremely helpful for troubleshooting performance issues, and it's invaluable if you want to analyze your workload.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Why You Need Query Store, Part III: Proactively analyze your workload\"}]},{\"@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":"Why You Need Query Store, Part III: Proactively analyze your workload - Erin Stellato","description":"The data captured by Query Store is extremely helpful for troubleshooting performance issues, and it's invaluable if you want to analyze your workload.","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\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/","og_locale":"en_US","og_type":"article","og_title":"Why You Need Query Store, Part III: Proactively analyze your workload - Erin Stellato","og_description":"The data captured by Query Store is extremely helpful for troubleshooting performance issues, and it's invaluable if you want to analyze your workload.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/","og_site_name":"Erin Stellato","article_published_time":"2019-09-12T18:54:58+00:00","article_modified_time":"2019-09-12T18:55:14+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/","name":"Why You Need Query Store, Part III: Proactively analyze your workload - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-09-12T18:54:58+00:00","dateModified":"2019-09-12T18:55:14+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"The data captured by Query Store is extremely helpful for troubleshooting performance issues, and it's invaluable if you want to analyze your workload.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-iii-proactively-analyze-your-workload\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Why You Need Query Store, Part III: Proactively analyze your workload"}]},{"@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\/1051"}],"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=1051"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1051\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}