{"id":408,"date":"2008-03-08T15:41:56","date_gmt":"2008-03-08T15:41:56","guid":{"rendered":"\/blogs\/conor\/post\/Query-Optimization-DMV-du-jour-sysdm_exec_query_optimizer_info.aspx"},"modified":"2013-01-01T19:24:12","modified_gmt":"2013-01-01T19:24:12","slug":"query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/","title":{"rendered":"Query Optimization DMV du jour – sys.dm_exec_query_optimizer_info"},"content":{"rendered":"

So as you proceed up the river into the jungle, searching for answers about how the query optimizer works, I’ll ask you one question: Did you know that there’s actually a lot of stuff that the Optimizer team just tells you?  It’s in the product, and I’m constantly suprised by how little attention they get.  You can learn all sorts of things by looking at the data.  Now, not all of it is documented, but that’s usually just because one needs leeway to change the internals rather than some deep, dark secret that needs to be kept.<\/p>\n

Now that you’re hooked ;), I’ll tell you a bit about an optimizer DMV called sys.dm_exec_query_optimizer_info.  It tells you all sorts of things about how your querys are optimized.  It’s a bag of counters for all sorts of things that, with a trained eye, can give you lots of insight into what is happening.<\/p>\n

This guy is actually documented<\/a>, at least partially, on MSDN.  <\/p>\n

<\/p>\n

(That’s from my SQL 2008 install, btw).<\/p>\n

Some of these fields are “undocumented”.  search 0, 1, and 2 are in that category.  I won’t talk about them except to say that the names aren’t really obfuscated too much.  <\/p>\n

To learn about a particular query, you find a nice, quiet server and: <\/p>\n

1. select * from this table, store the results somewhere
2. optimize a query of interest,
3. select from this table again, then compare the current totals to the originals.<\/p>\n

I think that this is one use of this DMV – trying to figure out why a query takes a long time to optimize.<\/p>\n

The other use of the DMV is to get a good statistical picture of a running system.  Say that I’m a DBA and I want to know how many queries in my application have hints or _need_ hints to work well.  Well, this will tell you.  Granted, it doesn’t separate recompiles from compiles, and if you have a system where plans are getting kicked out of the cache things may be a bit skewed, but I can tell you that this is far better than simply guessing.  Often the DB application developer doesn’t realize that they’ve built an application that requires a lot of hinting or a lot of compilations, and you can see this in more detail than you get with the performance counters.<\/p>\n

I’ve already talked about “trivial plans”, which are not documented in this DMV but are widely known in the other outputs of the system.  I’ll let you guys guess about the search 0, 1, and 2 stuff – if you can back up your guess with a public post, book, or other form of comment I’ll confirm if you get it right.<\/p>\n

Have a great weekend, ya’ll.<\/p>\n

Conor Cunningham<\/p>\n","protected":false},"excerpt":{"rendered":"

So as you proceed up the river into the jungle, searching for answers about how the query optimizer works, I’ll ask you one question: Did you know that there’s actually a lot of stuff that the Optimizer team just tells you?  It’s in the product, and I’m constantly suprised by how little attention they get.  […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"class_list":["post-408","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008"],"yoast_head":"\nQuery Optimization DMV du jour - sys.dm_exec_query_optimizer_info - Conor Cunningham<\/title>\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\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Optimization DMV du jour - sys.dm_exec_query_optimizer_info - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"So as you proceed up the river into the jungle, searching for answers about how the query optimizer works, I’ll ask you one question: Did you know that there’s actually a lot of stuff that the Optimizer team just tells you?  It’s in the product, and I’m constantly suprised by how little attention they get.  […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-08T15:41:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-01T19:24:12+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlskills.com\/blogs\/conor\/content\/binary\/qodmv.jpg\" \/>\n<meta name=\"author\" content=\"Conor Cunningham\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Conor Cunningham\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/\",\"name\":\"Query Optimization DMV du jour - sys.dm_exec_query_optimizer_info - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-03-08T15:41:56+00:00\",\"dateModified\":\"2013-01-01T19:24:12+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Optimization DMV du jour – sys.dm_exec_query_optimizer_info\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\",\"name\":\"Conor Cunningham\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\",\"name\":\"Conor Cunningham\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"caption\":\"Conor Cunningham\"},\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Query Optimization DMV du jour - sys.dm_exec_query_optimizer_info - Conor Cunningham","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\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/","og_locale":"en_US","og_type":"article","og_title":"Query Optimization DMV du jour - sys.dm_exec_query_optimizer_info - Conor Cunningham","og_description":"So as you proceed up the river into the jungle, searching for answers about how the query optimizer works, I’ll ask you one question: Did you know that there’s actually a lot of stuff that the Optimizer team just tells you?  It’s in the product, and I’m constantly suprised by how little attention they get.  […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/","og_site_name":"Conor Cunningham","article_published_time":"2008-03-08T15:41:56+00:00","article_modified_time":"2013-01-01T19:24:12+00:00","og_image":[{"url":"http:\/\/www.sqlskills.com\/blogs\/conor\/content\/binary\/qodmv.jpg"}],"author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/","name":"Query Optimization DMV du jour - sys.dm_exec_query_optimizer_info - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-03-08T15:41:56+00:00","dateModified":"2013-01-01T19:24:12+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/query-optimization-dmv-du-jour-sys-dm_exec_query_optimizer_info\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"Query Optimization DMV du jour – sys.dm_exec_query_optimizer_info"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/","name":"Conor Cunningham","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3","name":"Conor Cunningham","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","caption":"Conor Cunningham"},"url":"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/408","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/comments?post=408"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/408\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}