Information about how queries have been optimized since the server has been restarted. Note that counters are only affected when there is some sort of optimization event, not on every query.<\/p>\n
Type<\/b>: View<\/p>\n
Data<\/b>: Accumulating reset at restart<\/p>\n
Columns:<\/b><\/p>\n Example:<\/b><\/p>\n As an example, on a low use server, I restarted the server and executed:<\/p>\n select counter,occurrence, value You will get values like:<\/p>\n counter\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 occurrence\u00a0\u00a0\u00a0 value This tells you the number of statements that have been optimized and a plan created (the same value is for all three of the displayed counters), the average elapsed time spent compiling in the value column for the elapsed time row, and finally, the average final cost in the final cost row. Using these counters and some of the others you will discover will help you to determine how often statements ae being compiled.<\/p>\n Execute it again, it will give you the same values (assuming you are the only user, of course.) Now, execute the same query, this time making it uppercase, forcing another complication, since plans must match in every way:<\/p>\n SELECT COUNTER, OCCURRENCE, VALUE You will see that the occurrences have increased, and there may be some difference in the average time it took:<\/p>\n counter\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 occurrence\u00a0\u00a0\u00a0 value —————————————<\/p>\n Note:\u00a0 This is part of an ongoing project to write a book about all of the dynamic management views for Red-Gate<\/a>.\u00a0It will be freely distributable once complete as an ebook.\u00a0Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book.\u00a0 Also, I will begin maintaining the following web page once the book is closer to completion: http:\/\/drsql.org\/dmvbook.aspx<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" Information about how queries have been optimized since the server has been restarted. Note that counters are only affected when there is some sort of optimization event, not on every query. Type: View Data: Accumulating reset at restart Columns: counter – Name of the query occurrence \u2013 Number of times the counter was recorded to……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82018","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82018","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82018"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82018\/revisions"}],"predecessor-version":[{"id":85666,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82018\/revisions\/85666"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82018"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
\nfrom sys.dm_exec_query_optimizer_info<\/p>\n
\n—————– ————- ——————–
\noptimizations\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01
\nelapsed time\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.0235776097702821
\nfinal cost\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4.1957E-05<\/p>\n
\nFROM SYS.DM_EXEC_QUERY_OPTIMIZER_INFO<\/p>\n
\n—————– ————- ——————–
\noptimizations\u00a0\u00a0\u00a0\u00a0\u00a02\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01
\nelapsed time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.020978588737036
\nfinal cost\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4.1957E-05<\/p>\n