{"id":490,"date":"2011-11-03T17:15:14","date_gmt":"2011-11-03T21:15:14","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=490"},"modified":"2014-11-13T14:02:47","modified_gmt":"2014-11-13T19:02:47","slug":"missing-graphical-showplan","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/","title":{"rendered":"Missing Graphical Showplan"},"content":{"rendered":"<p>\nThere are a few queries agains the DMVs that return execution plans in XML format. This is an example that lists the 10 the execution plans currently in cache that take up the most memory:\n<\/p>\n[sql]\nSELECT TOP ( 10 )<br \/>\n        cp.size_in_bytes ,<br \/>\n        cp.refcounts ,<br \/>\n        cp.usecounts ,<br \/>\n        cp.cacheobjtype ,<br \/>\n        cp.objtype ,<br \/>\n        QUOTENAME(DB_NAME(qp.dbid)) + '.'<br \/>\n        + QUOTENAME(OBJECT_SCHEMA_NAME(qp.objectid, qp.dbid)) + '.'<br \/>\n        + QUOTENAME(OBJECT_NAME(qp.objectid, qp.dbid)) AS object_name ,<br \/>\n        qp.number ,<br \/>\n        qp.encrypted ,<br \/>\n        qp.query_plan<br \/>\nFROM    sys.dm_exec_cached_plans cp<br \/>\n        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp<br \/>\nORDER BY cp.size_in_bytes DESC;<br \/>\n[\/sql]\n<p>\nStarting with version 2008 of SQL Server Management Studio, a single click opens that plan as a graphical plan, rather than just showing the XML for it in a new window.This functionality has been a great help for many of us.\n<\/p>\n<p>\nSince SP1 of SQL Server 2008 R2 this nice shortcut is broken, and it seems not to be high up on Microsofts list of things to fix. You can read all the details here:<br \/>\n<a href=\"http:\/\/sqlblog.com\/blogs\/aaron_bertrand\/archive\/2011\/10\/28\/getting-graphical-showplan-back-in-sql-server-2008-r2.aspx\">Getting graphical ShowPlans back in SQL Server 2008 R2<\/a>\n<\/p>\n<p>\nThere are several solutions mentioned in Aarons blog post. One involves hacking your installation, while another suggests to go back to the old way of saving the xml with a .showplan extension and then reopening it in SSMS.\n<\/p>\n<p>\nFor those of you that do not like either solution, Aaron mentions is a third solution. It involves a third party tool: <a href=\"http:\/\/www.sqlsentry.com\/plan-explorer\/sql-server-query-view.asp\">SQL Sentry Plan Explorer<\/a>.\n<\/p>\n<p>\nThis is a really great tool that shows a lot more information about the plans that SSMS. The best part about it is: It is free.\n<\/p>\n<p>\nSo, if you don't use Plan Explorer yet, try it out now to get your graphical plans back.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are a few queries agains the DMVs that return execution plans in XML format. This is an example that lists the 10 the execution plans currently in cache that take up the most memory: [sql] SELECT TOP ( 10 <a href=\"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/\">[more&#8230;]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5],"tags":[],"class_list":["post-490","post","type-post","status-publish","format-standard","hentry","category-general"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Missing Graphical Showplan - sqlity.net<\/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:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Missing Graphical Showplan - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"There are a few queries agains the DMVs that return execution plans in XML format. This is an example that lists the 10 the execution plans currently in cache that take up the most memory: [sql] SELECT TOP ( 10 [more...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2011-11-03T21:15:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T19:02:47+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Missing Graphical Showplan\",\"datePublished\":\"2011-11-03T21:15:14+00:00\",\"dateModified\":\"2014-11-13T19:02:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/\"},\"wordCount\":302,\"commentCount\":0,\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/\",\"name\":\"Missing Graphical Showplan - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2011-11-03T21:15:14+00:00\",\"dateModified\":\"2014-11-13T19:02:47+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/490\\\/missing-graphical-showplan\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Missing Graphical Showplan\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Missing Graphical Showplan - sqlity.net","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:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/","og_locale":"en_US","og_type":"article","og_title":"Missing Graphical Showplan - sqlity.net","og_description":"There are a few queries agains the DMVs that return execution plans in XML format. This is an example that lists the 10 the execution plans currently in cache that take up the most memory: [sql] SELECT TOP ( 10 [more...]","og_url":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2011-11-03T21:15:14+00:00","article_modified_time":"2014-11-13T19:02:47+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Missing Graphical Showplan","datePublished":"2011-11-03T21:15:14+00:00","dateModified":"2014-11-13T19:02:47+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/"},"wordCount":302,"commentCount":0,"articleSection":["General"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/","url":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/","name":"Missing Graphical Showplan - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2011-11-03T21:15:14+00:00","dateModified":"2014-11-13T19:02:47+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/490\/missing-graphical-showplan\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Missing Graphical Showplan"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-7U","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/490","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=490"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/490\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=490"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=490"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=490"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}