{"id":969,"date":"2010-08-17T00:00:00","date_gmt":"2010-08-17T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/dmvs-for-query-plan-metadata\/"},"modified":"2021-09-29T16:21:57","modified_gmt":"2021-09-29T16:21:57","slug":"dmvs-for-query-plan-metadata","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/dmvs-for-query-plan-metadata\/","title":{"rendered":"DMVs for Query Plan Metadata"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">The Dynamic Management Objects (DMOs) described in this article provide metadata and statistics regarding any query that has previously executed on your server, and the execution plan used to run it, provided that the plan for that query is in the plan cache. For example, if a stored procedure has been executed, and the plan for it is cached, then not only can we return and examine that plan, but we can also obtain statistics regarding execution of the stored procedure associated with the plan, such as total number of disk reads performed, total time spent executing, and so on.<\/p>\n<p>We&#8217;ll show how such statistics and query plan metadata can be used to answer questions such as those below (the relevant DMO is indicated in brackets).<\/p>\n<ul>\n<li>What are the &#8220;top x&#8221; most expensive queries in the cache in terms of CPU \/ IO \/ memory? (<span class=\"STCodeinTextChar\">query_stats<\/span>)<\/li>\n<li>Which are the most &#8220;expensive&#8221; stored procedures? (<span class=\"STCodeinTextChar\">procedure_stats<\/span><span class=\"STCodeinTextBold\">)<\/span><\/li>\n<li>Are plans getting reused? (<span class=\"STCodeinTextChar\">cached_plans and plan_attributes<\/span>)<\/li>\n<li>How many ad hoc, single-use queries are taking up space in the plan cache? (<span class=\"STCodeinTextChar\">cached_plans)<\/span><\/li>\n<\/ul>\n<p>In each section, we&#8217;ll provide T-SQL scripts to retrieve this data, and discuss how you can use it to get a feeling for certain aspects of your system&#8217;s performance.<\/p>\n<p>Whereas the <span class=\"STCodeinTextChar\">sys.dm_exec_requests<\/span> DMV can provide insight into &#8220;What is executing on my instance right now, and what is it doing?&#8221;, the DMOs in this article can give you a broader perspective on your typical query workload, based on the plans that are stored in the cache. The really big win for the average DBA is the fact that you can access the statistics on what queries were run, what plans and system resources were used, and so on, <span class=\"STItalic\">after the event<\/span>. Before DMOs, when a DBA was notified of a bad problem that occurred a little earlier, there were few tools to hand to help find out what happened, unless they happened to be running Profiler traces to capture the event. Now, by querying these DMOs, we can retrospectively examine the damage that a rogue query did, and the impact on the server of running such queries will generally be far less than using a tool such as Profiler.<\/p>\n<h1>Why Cached Plan Analysis with DMOs?<\/h1>\n<p>There are several strategies that one may use when seeking to resolve a SQL Server performance issue using the DMOs. However, regardless of the origin of an issue, whether it&#8217;s related to CPU, I\/O, memory, blocking, and so on, the final destination is most often a detailed examination of the queries that are causing the problem, and of possible ways to tune them.<\/p>\n<p>The execution plans generated by the SQL Server Query optimizer, and stored in memory in the plan cache, are the DBA&#8217;s premier window into the world of query optimization. If you are looking to improve your skills as a SQL Server programmer or DBA, then acquiring an understanding of <span class=\"STBold\">query plans<\/span> is an essential first step. There is a natural progression from being able to run a query, to understanding all the steps that SQL Server has to undertake in order to execute that query and serve up the results. In fact, I regard knowledge of query plans as a barometer by which to judge a programmer&#8217;s ability to write optimal queries.<\/p>\n<p>It is only with a deep knowledge of the operations that are being performed to execute a query, and the order in which these operations occur that you can really begin to look for opportunities to optimize the data access path for that query. Of course, the graphical execution plan is available by other, arguably simpler means, such as through SQL Server Management Studio (SSMS) or Profiler. However, with each of these tools, the actual, as opposed to the estimated, plan that was used to execute a given query can only be captured in real time, while the query is running. With the DMOs in this article, you can retrieve the actual plan for any query for which the plan is cached.<\/p>\n<p>Furthermore, the metadata and statistics that these DMOs can provide alongside the plan are, as we will demonstrate, very useful in helping direct your query tuning efforts; in working out whether you need to reduce the computational complexity of the query to reduce CPU usage; add indexes, replace cursors with set-based constructs, tuning the search predicate to return less data, to reduce I\/O; eliminate expensive sorts, reduce gratuitous use of temporary tables to reduce memory usage, and so on.<\/p>\n<p>Again, you may be thinking that a similar set of statistics is available from the performance reports that can be accessed in Management Studio by right-clicking on the Server node and selecting Reports | Standard Reports.<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>SSMS reports<\/b><br \/>\nThese reports are standard in SQL Server 2008 and are an add-in to SQL Server 2005, where they are referred to as Performance Dashboard Reports.<\/p>\n<\/div>\n<p>In fact, of course, these reports use the DMOs under the covers. The problem is that they have significant limitations on how you can filter them and use the output. In this article, we will look at techniques to build your own queries against the DMOs that will allow you to view only the data you want to see. Once you&#8217;ve developed a core set of scripts it is very easy to refine them as required, to save the results to your own tables, so that you can compare current results with previous results, track performance over time, and so on.<\/p>\n<p>Finally, but very importantly, remember that it&#8217;s not only ensuring that the plans are good that is critical; it is ensuring that these plans are used, and <span class=\"STBold\">reused<\/span> time and again. As noted, SQL Server stores plans for previously-executed queries in a shared memory area called the plan cache. Whenever a query is submitted for execution, SQL Server checks in the plan cache to see if there is an existing plan it can use to execute the query. Every time it cannot find a match, the submitted query must be parsed, optimized, and a plan generated.<\/p>\n<p>Parsing and optimizing SQL statements is a CPU-intensive process, so it is not a &#8220;high concurrency&#8221; operation. Furthermore, each time it does this, SQL Server acquires latches on the plan cache to protect the relevant area of memory from other updates. The more this happens, the more it will restrict other processes from accessing the cache. Well-designed SQL needs to promote plan reuse (&#8220;parse once, use many times&#8221;) as far as possible. If you have a lot of ad hoc, non-parameterized SQL in your workload, it will result in much higher CPU consumption and many more latches acquired, as a plan will be generated each time. At the same time, your cache will fill up with single-use plans that will probably never be used again.<\/p>\n<p>It&#8217;s even possible that, in such circumstances, useful plans could get flushed out of the cache. SQL Server flushes plans out of the cache when it needs space, based on an algorithm that considers the cost of recalculating the plan, how recently the plan was used, and other unpublicized factors. Generally speaking, the least interesting queries will be the ones removed from the cache first.<\/p>\n<p>In any event, the DMOs and scripts in this article will help you uncover these sorts of problems, and help you ensure that the plans for your day-to-day query workload are in the cache, and are getting used.<\/p>\n<h1>An Overview of DMOs for Query Plan Metadata<\/h1>\n<p>In addition to the <span class=\"STCodeinTextChar\">sys.dm_exec_requests<\/span> DMV and <span class=\"STCodeinTextChar\">sys.dm_exec_sql_text<\/span> DMF, we will extract our statistics and query plan metadata from the DMVs below, which belong to the &#8220;execution-related&#8221; category (which is why their names all begin with &#8220;<span class=\"STCodeinTextChar\">sys.dm_exec_<\/span>&#8220;).<\/p>\n<ul>\n<li><span class=\"STCodeinTextBold\">sys.dm_exec_query_stats<\/span> &#8211; returns aggregated performance statistics for a cached query plan. Returns one row per statement within the plan.<\/li>\n<li>sys.dm_exec_procedure_stats<b> <\/b>&#8211; returns aggregated performance statistics for cached stored procedures (SQL Server 2008 only). Returns one row per stored procedure.<\/li>\n<li><span class=\"STCodeinTextBold\">sys.dm_exec_cached_plans<\/span> &#8211; provides detailed information about a cached plan, such as the number of times it has been used, its size, and so on. Returns a row for each cached plan.<\/li>\n<li><span class=\"STCodeinTextBold\">sys.dm_exec_query_optimizer_info<\/span> &#8211; returns statistics regarding the operation of the query optimizer, to identify any potential optimization problems. For example, you can find out how many queries have been optimized since the last time the server was restarted.<\/li>\n<\/ul>\n<p>In order to return the query plan for a given batch, as well as some interesting attributes of these plans, we can pass the identifier for that plan batch, the <span class=\"STCodeinTextChar\">plan_handle<\/span>, to one of the DMFs below.<\/p>\n<ul>\n<li><span class=\"STCodeinTextBold\">sys.dm_exec_query_plan<\/span> &#8211; returns in XML format the query plan, identified by a <span class=\"STCodeinTextChar\">plan_handle<\/span>, for a SQL batch.<\/li>\n<li><span class=\"STCodeinTextBold\">sys.dm_exec_text_query_plan<\/span> &#8211; returns in text format the query plan, identified by a <span class=\"STCodeinTextChar\">plan_handle<\/span>, for a SQL batch or, via use of this DMF&#8217;s offset columns, a specific statement within that batch.<\/li>\n<li><span class=\"STCodeinTextBold\">sys.dm_exec_plan_attributes<\/span> &#8211; provides information about various <span class=\"STItalic\">attributes<\/span> of a query plan, identified by a <span class=\"STCodeinTextChar\">plan_handle<\/span>, such as the number of queries currently using a given execution plan. It returns one row for each attribute.<\/li>\n<\/ul>\n<p>To the query optimizer, a query and a query plan are not synonymous. When a batch is executed, it gets a plan. This plan comprises one or more individual queries, each of which will have a query plan of its own. The DMVs for query plans, such as <span class=\"STCodeinTextChar\">_cached_plans<\/span>, return one row per distinct batch or object. The DMVs for queries, such as <span class=\"STCodeinTextChar\">_query_stats<\/span>, return one row per independent query that is embedded in that plan. If, in our queries, we &#8220;join&#8221; from <span class=\"STCodeinTextChar\">_query_stats<\/span> to the <span class=\"STCodeinTextChar\">_query_plan<\/span> DMF, in order to return the plan, each row returned by <span class=\"STCodeinTextChar\">_query_stats<\/span> will contain a link to the plan for the batch to which the row (i.e. individual query) belongs. If, instead, we join to <span class=\"STCodeinTextChar\">text_query_plan<\/span>, it&#8217;s possible to extract from the batch the plan for only the individual query in question (but there are complications, as will soon be demonstrated).<\/p>\n<p>As we progress through the article, we&#8217;ll provide brief description s of some of the more useful (or confusing) columns that these DMOs provide, but we&#8217;ve tried to avoid &#8220;rehashing Books Online&#8221; as far as possible, and a complete listing and reference for the DMOs covered in this article <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms188068.aspx\">can be found here<\/a>.<\/p>\n<h1>Flushing the Cache?<\/h1>\n<p>Before we get started in earnest, it is important to realize that troubleshooting problem queries using the DMOs is not a perfect science. Firstly, we can only examine plans for queries that are in the cache; while the cache will usually hold plans for all the most active\/costly queries, less costly\/reused queries can fall out of the cache, and some queries, with nominal plans, are never cached in the first place; in short, some queries will be missed.<\/p>\n<p>Secondly, plans may remain in the cache from when they are first compiled until the object is dropped or recompiled, or the cache is flushed. This means that some plans, especially frequently used ones, may remain in the cache for a long time. If your SQL Server instance has been up and running for long, it will contain lots of plans of different ages which will make it hard to analyze the statistics. For example, if you&#8217;re looking for the plans for the most-CPU-intensive procedures, the results will be heavily skewed towards those procedures that have been cached for a long period, compared to plans that have, for some reason, been recently recompiled.<\/p>\n<p>One way around this might be to clear the cache, using <span class=\"STCodeinTextChar\">DBCC FREEPROCCACHE<\/span> for all databases on an instance, or using <span class=\"STCodeinTextChar\">DBCC<\/span> <span class=\"STCodeinTextChar\">FLUSHPROCINDB<\/span> for a single database, as shown in Listing 1.1.<\/p>\n<pre>--Determine the id of your database\r\nDECLARE @intDBID INTEGER\r\nSET @intDBID = ( SELECT dbid\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 master.dbo.sysdatabases\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 name = 'mydatabasename'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\r\n--Flush the procedure cache for your database\r\nDBCC FLUSHPROCINDB (@intDBID)\r\n<\/pre>\n<p class=\"caption\">Listing 1.1: Flushing the cache of plans belonging to a particular database.<\/p>\n<p>The flushed plans will go back into the cache the next time they are executed, the result being that most of the stored procedures that are run frequently and are part of your normal workload will have a similar cache time. This will make the statistics much easier to interpret (until, and unless, they get recompiled again for some other reason).<\/p>\n<p>Flushing the cache is a controversial suggestion and many DBAs avoid doing it on a production server. After all, clearing the cache means that new plans need to be determined and created, which has an associated cost. It&#8217;s true that recompiling all of the query plans will cause some extra work for your processor(s), but many modern processors shrug this off with little effect beyond a brief (few seconds) spike of CPU activity.<\/p>\n<h1>Viewing the Text of Cached Queries and Query Plans<\/h1>\n<p>The root cause of many performance problems is the fact that the plan you get when query execution is optimized under a full production server load can be very different from the one you saw in Management Studio while building the query. For example, on your development server, you may have only a single CPU, several orders of magnitude fewer rows, different amounts of RAM, statistics that are completely up to date, fewer concurrent users, and so on.<\/p>\n<p>Unfortunately, the excuse &#8220;it worked on my machine&#8221; is not acceptable with database applications, so it is very useful to be able to see the query plan that was actually used to optimize a query, when it was executed. This way you can find out why a query is performing as it is, and begin the process of optimization. This is also useful when troubleshooting poor performance caused by parameter sniffing, whereby the initial set of parameters chosen as a &#8220;guide&#8221; when first compiling the plan for a stored procedure turn out to be anomalous, and not representative of the parameters&#8217; values supplied during normal execution.<\/p>\n<p>To retrieve the plan for a given batch or procedure, we simply extract the <span class=\"STCodeinTextChar\">plan_handle<\/span> from the <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> DMV (covered in detail later in this article). The <span class=\"STCodeinTextChar\">plan_handle<\/span> uniquely identifies the query plan for a given batch or stored procedure. We then pass it as a parameter to one of the following DMFs:<\/p>\n<ul>\n<li><span class=\"STCodeinTextBold\">sys.dm_exec_query_plan<\/span>, which accepts the <span class=\"STCodeinTextChar\">plan_handle<\/span> as its only parameter and will return the plan for the identified batch or procedure, as XML. Note that BOL states that it returns the plan in &#8220;text format&#8221;. This is a little confusing since it refers to the type of column returned, not the format in which you will view the plan, which is XML.<\/li>\n<li><span class=\"STCodeinTextBold\">sys.dm_exec_text_query_plan<\/span>, which accepts the <span class=\"STCodeinTextChar\">plan_handle<\/span> and adds two additional parameters, <span class=\"STCodeinTextChar\">statement_start_offset<\/span> and <span class=\"STCodeinTextChar\">statement_end_offset<\/span>, which mark the start and end points of individual SQL statements within the batch or procedure. This DMF returns the plan in text format (a <span class=\"STCodeinTextChar\">nvarchar(max)<\/span> typed object) and is available in SQL Server 2005 SP2 and later.<\/li>\n<\/ul>\n<p>When returning the plan from the <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> DMF, SSMS will display a link to the plan, which you can click onto immediately to display the graphical plan. In any event, with either DMF, the output can be saved as a <span class=\"STCodeinTextChar\">.SQLPLAN<\/span> file that can be used to view the graphical plan in SSMS.<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>NOTE:<\/b><br \/>\nOne unfortunate limitation of these functions is that, in the absence of SSMS or another third-party tool that understands the <span class=\"STCodeinTextChar\">.SQLPLAN<\/span> format, they do not give you the type of easily readable output that you get when using the <span class=\"STCodeinTextChar\">SHOWPLAN_TEXT<\/span> setting in a query window.<\/p>\n<\/div>\n<p>Both of these functions return a <span class=\"STCodeinTextChar\">query_plan<\/span> column, containing the current cached query plan, along with <span class=\"STCodeinTextChar\">dbid<\/span>, <span class=\"STCodeinTextChar\">objectid<\/span>, and <span class=\"STCodeinTextChar\">encrypted<\/span> columns. The <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> function returns the plan as an XML typed value, which limits the size of the plan that can be returned, since the XML datatype does not support XML documents with more than 128 levels.The <span class=\"STCodeinTextChar\">sys.dm_exec_text_query_plan<\/span> object returns the plan as a <span class=\"STCodeinTextChar\">nvarchar(max)<\/span> typed object, so removing these restrictions.<\/p>\n<h2>Returning the plan using sys.dm_exec_query_plan<\/h2>\n<p>Using the <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> DMF, we can return the plan for a given batch or procedure (which will contain &#8220;subplans&#8221; for each query comprising the batch). As an example, let&#8217;s create a stored procedure, <span class=\"STCodeinTextChar\">ShowQueryText<\/span>, execute it, so the plan gets cached, and then extract the plan that was used to execute that stored procedure, as shown in Listing 1.2.<\/p>\n<pre>CREATE PROCEDURE ShowQueryText\r\nAS \r\n\u00a0\u00a0\u00a0 SELECT TOP 10\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.objects ;\r\n\u00a0\u00a0 --waitfor delay '00:00:00'\r\n\u00a0\u00a0\u00a0 SELECT TOP 10\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.objects ;\r\n\u00a0\u00a0\u00a0 SELECT TOP 10\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.procedures ;\r\nGO\r\nEXEC dbo.ShowQueryText ;\r\nGO\r\nSELECT\u00a0 deqp.dbid ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqp.objectid ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqp.encrypted ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqp.query_plan\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_query_stats deqs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp\r\nWHERE\u00a0\u00a0 objectid = OBJECT_ID('ShowQueryText', 'p') ;\r\n<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1118-DMV1.jpg\" alt=\"1118-DMV1.jpg\" \/><\/p>\n<p class=\"caption\">Listing 1.2: Retrieving the query plan for a cached stored procedure.<\/p>\n<p>You will see that we get three rows retunred by <span class=\"STCodeinTextChar\">query_stats<\/span>, and each row contains a link to a query plan; in each case this will be the same plan, i.e. the plan for the entire procedure.<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>Obtaining the <\/b><span class=\"STCodeinTextChar\">plan_handle:<\/span><br \/>\nAs well as <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span>, the <span class=\"STCodeinTextChar\">plan_handle<\/span> can also be retrieved from <span class=\"STCodeinTextChar\">sys.dm_exec_requests, sys.dm_exec_query_memory_grants<\/span> and <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plan<\/span>.<\/p>\n<\/div>\n<p>In SQL Server 2005 Management Studio, you can click on the link to the query plan, thus opening it up as an XML document in SSMS, and then save it with a <span class=\"STCodeinTextChar\">.SQLPLAN<\/span> extension (by default it will be saved with an XML extension). You can open it up in SSMS and view the graphical plan. SQL Server 2008 Management Studio realizes the schema is a SQL plan, and so allows us to simply click the XML output to get a display that shows the graphical version of the plan, as shown in Figure 1.1 (cropped to save space).<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1118-DMV2.jpg\" alt=\"1118-DMV2.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.1: Viewing the query plan in Management Studio.<\/p>\n<p>In this way, we can find out precisely how a stored procedure was executed, at the time of being cached.<\/p>\n<h2>Dissecting the SQL text<\/h2>\n<p>We can use the <span class=\"STCodeinTextChar\">sql_handle<\/span> from <span class=\"STCodeinTextChar\">sys.dm_exec_requests<\/span> to return the SQL text for the batch, from <span class=\"STCodeinTextChar\">sys.dm_exec_sql_text<\/span>, and then dissect the text using &#8220;statement offset&#8221; columns provided by the former, to get at the text of just the currently executing query.<\/p>\n<p>We can do the same thing when using <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span>, in order to extract query statistics about individual statements within a cached batch\/procedure. Let&#8217;s take a look at the <span class=\"STCodeinTextChar\">sql_handle<\/span>, <span class=\"STCodeinTextChar\">plan_handle<\/span> and associated SQL text, returned for each query in our stored procedure, as shown in Listing 1.3.<\/p>\n<pre>SELECT\u00a0 deqs.plan_handle ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqs.sql_handle ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 execText.text\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_query_stats deqs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText\r\nWHERE\u00a0\u00a0 execText.text LIKE 'CREATE PROCEDURE ShowQueryText%'\r\n<\/pre>\n<p class=\"caption\">Listing 1.3: Viewing the <span class=\"STCodeinTextChar\">sql_handle <\/span>and <span class=\"STCodeinTextChar\">plan_handle<\/span>.<\/p>\n<p>As you can see, each row has the same <span class=\"STCodeinTextChar\">sql_handle<\/span>, same <span class=\"STCodeinTextChar\">plan_handle<\/span> and the same SQL text (referring to the whole procedure). I&#8217;m only showing two of the rows here:<\/p>\n<pre>plan_handle\u00a0\u00a0\u00a0 sql_handle\u00a0\u00a0 text\r\n-------------- ------------- ----------------\r\n0x050002003... 0x03000200...CREATE PROCEDURE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ShowQueryText as...\r\n0x050002003... 0x03000200...CREATE PROCEDURE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ShowQueryText as...\r\n<\/pre>\n<p>Just as when you supply a <span class=\"STCodeinTextChar\">sql_handle<\/span>, you are identifying the batch or procedure to which a query belongs, so, when you supply a <span class=\"STCodeinTextChar\">plan_handle<\/span>, you are identifying the plan associated with a batch or procedure, and this batch or procedure may contain multiple queries. When you identify a plan for a batch, you identify the associated SQL text for the whole batch, and we&#8217;ll need to extract the text for individual queries in that batch, in order to work with the <span class=\"STCodeinTextChar\">query_stats<\/span> DMV. Listing 1.4 shows how to do this.<\/p>\n<pre>SELECT\u00a0 CHAR(13) + CHAR(10)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + CASE WHEN deqs.statement_start_offset = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND deqs.statement_end_offset = -1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN '-- see objectText column--'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE '-- query --' + CHAR(13) + CHAR(10)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + SUBSTRING(execText.text, deqs.statement_start_offset \/ 2,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ( CASE WHEN deqs.statement_end_offset = -1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN DATALENGTH(execText.text)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE deqs.statement_end_offset\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END ) - deqs.statement_start_offset ) \/ 2)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END AS queryText ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqp.query_plan\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_query_stats deqs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp\r\nWHERE\u00a0\u00a0 execText.text LIKE 'CREATE PROCEDURE ShowQueryText%'\r\n<\/pre>\n<p class=\"caption\">Listing 1.4: Extracting the SQL text for individual queries in a batch.<\/p>\n<p>We pass the <span class=\"STCodeinTextChar\">plan_handle<\/span> to the <span class=\"STCodeinTextChar\">sql_text<\/span> DMF (we could equally well pass the <span class=\"STCodeinTextChar\">sql_handle<\/span>), which returns the SQL text associated with the plan for that batch. We then extract the text for the individual queries, using the <span class=\"STCodeinTextChar\">SUBSTRING<\/span> function, and remembering to divide the offset values by 2 (the start offset, and then the difference between the end and start offsets in the <span class=\"STCodeinTextChar\">SUBSTRING<\/span> operation) before we use them, since they are stored in Unicode.<\/p>\n<p>This query should return the three separate queries, as shown in Figure 1.2.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1118-DMV3.jpg\" alt=\"1118-DMV3.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.2: Three queries returned by <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span>.<\/p>\n<p>Click on the plan links and you&#8217;ll see that the plan returned for each row is still the plan for the whole batch.<\/p>\n<h2>Returning the plan using sys.dm_exec_text_query_plan<\/h2>\n<p>If we want, for each row, to show only the &#8220;subplan&#8221; for each individual query, then it is a little trickier, and we have to use the <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> DMF, which returns the plan in text form and supports offset parameters, which we can use to dissect it. Unfortunately, the <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> DMF returns the plan in a form we can save and use, but not view in SSMS. Just for demo purposes here, we&#8217;ve cast the returned plan to an XML type, but this isn&#8217;t a &#8220;safe&#8221; application.<\/p>\n<pre>SELECT\u00a0 deqp.dbid ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqp.objectid ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(detqp.query_plan AS XML) AS singleStatementPlan ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqp.query_plan AS batch_query_plan ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --this won't actually work in all cases because nominal plans aren't\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- cached, so you won't see a plan for waitfor if you uncomment it\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER ( ORDER BY Statement_Start_offset )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0AS query_position ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN deqs.statement_start_offset = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND deqs.statement_end_offset = -1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN '-- see objectText column--'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE '-- query --' + CHAR(13) + CHAR(10)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + SUBSTRING(execText.text, deqs.statement_start_offset \/ 2,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ( CASE WHEN deqs.statement_end_offset = -1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN DATALENGTH(execText.text)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE deqs.statement_end_offset\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END ) - deqs.statement_start_offset ) \/ 2)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END AS queryText\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_query_stats deqs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqs.statement_start_offset,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqs.statement_end_offset)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS detqp\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText\r\nWHERE\u00a0\u00a0 deqp.objectid = OBJECT_ID('ShowQueryText', 'p') ;\r\n<\/pre>\n<p class=\"style9\">Listing 1.5: Returning the plan using <span class=\"STCodeinTextChar\">sys.dm_exec_text_query_plan<\/span>.<\/p>\n<p>This time, for each row returned, we get the individual plan for each query, as well as the batch plan, as shown in Figure 1.3.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1118-DMV4.jpg\" alt=\"1118-DMV4.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1.3: Seeing the individual query plans.<\/p>\n<h1>Cached Query Plan Statistics<\/h1>\n<p>In the previous section, we explained how to use the <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> function and <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> DMV to get the text of the plan for a given batch. In this section, we&#8217;ll start retrieving some meatier information about the query plans that have been used to execute queries on the server.<\/p>\n<p>The ability to retrieve the text of a query, or query plan, becomes more interesting when combined with data stored in the <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span> DMV. It returns basic metadata regarding each plan, such as its size and the type of object to which it&#8217;s attached, but also, and most interestingly a <span class=\"STCodeinTextChar\">usecounts<\/span> column that lets us investigate plan reuse.<\/p>\n<h2>The sys.dm_exec_cached_plans DMV<\/h2>\n<p>The <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span> DMV &#8220;supersedes&#8221; the <span class=\"STCodeinTextChar\">syscacheobjects<\/span> object, available in versions of SQL Server prior to 2005. In many ways, <span class=\"STCodeinTextChar\">syscacheobjects<\/span> is easier to work with as it includes the text of the query and several attributes about the cached plan, but it is more limited, in that it doesn&#8217;t quite give access to the rich array of information that is available through <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span>, especially when used in concert with <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span>,<span class=\"STCodeinTextChar\"> sys.dm_exec_sql_text<\/span> and <span class=\"STCodeinTextChar\">sys.dm_exec_plan_attributes<\/span>.<\/p>\n<p>The data that the <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span> view will return is a snapshot of values, based on the current contents of the plan cache. The columns in this view are all pretty useful for seeing how plans are being cached. Those columns are listed below.<\/p>\n<ul>\n<li><span class=\"STCodeinTextBold\">bucketid<\/span> &#8211; the id of the hash bucket where the plan is held. You can see the maximum number of buckets available in the <span class=\"STCodeinTextChar\">sys.dm_os_memory_cache_hash_tables<\/span> view. Ideally, cached items (such as plans, and anything else that SQL Server puts into cache) will be spread evenly amongst the hash buckets).<\/li>\n<li><span class=\"STCodeinTextBold\">refcounts<\/span> &#8211; number of cache objects that reference this cached plan.<\/li>\n<li><span class=\"STCodeinTextBold\">usecounts<\/span> &#8211; number of times the plan has been used since its creation. This counter is incremented every time the query is executed, and a match to the plan is made.<\/li>\n<li><span class=\"STCodeinTextBold\">size_in_bytes<\/span> &#8211; size of the plan.<\/li>\n<li><span class=\"STCodeinTextBold\">memory_object_address<\/span> &#8211; internal address of the cached object. Can be used to reference <span class=\"STCodeinTextChar\">sys.dm_os_memory_objects<\/span> and <span class=\"STCodeinTextChar\">sys.dm_os_memory_cache_entries<\/span> to see the objects in the SQL Server cache.<\/li>\n<li><span class=\"STCodeinTextBold\">cacheobjtype<\/span> &#8211; type of object in the cache. The domain is:\n<ul>\n<li><span class=\"STCodeinTextBold\">Compiled Plan<\/span><\/li>\n<li><span class=\"STCodeinTextBold\">Parse Tree<\/span><\/li>\n<li><span class=\"STCodeinTextBold\">Extended Proc<\/span><\/li>\n<li><span class=\"STCodeinTextBold\">CLR Compiled Func<\/span><\/li>\n<li><span class=\"STCodeinTextBold\">CLR Compiled Proc.<\/span><\/li>\n<\/ul>\n<\/li>\n<li><span class=\"STCodeinTextBold\">objtype<\/span> &#8211; the type of object. The domain is:\n<ul>\n<li><span class=\"STCodeinTextChar\">Proc<\/span> (stored procedure, function)<\/li>\n<li><span class=\"STCodeinTextChar\">Prepared<\/span> (prepared statement)<\/li>\n<li><span class=\"STCodeinTextChar\">Adhoc<\/span> (query)<\/li>\n<li><span class=\"STCodeinTextChar\">Repl<\/span> <span class=\"STCodeinTextChar\">Proc<\/span> (replication filter procedure)<\/li>\n<li><span class=\"STCodeinTextChar\">Trigger<\/span><\/li>\n<li><span class=\"STCodeinTextChar\">View <\/span><\/li>\n<li><span class=\"STCodeinTextChar\">Default<\/span><\/li>\n<li><span class=\"STCodeinTextChar\">UsrTab<\/span> (user table)<\/li>\n<li><span class=\"STCodeinTextChar\">SysTab<\/span> (system table)<\/li>\n<li><span class=\"STCodeinTextChar\">CHECK<\/span><\/li>\n<li><span class=\"STCodeinTextChar\">Rule<\/span>.<\/li>\n<\/ul>\n<\/li>\n<li><span class=\"STCodeinTextBold\">plan_handle<\/span> &#8211; can be used with query plan functions, including <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> and <span class=\"STCodeinTextChar\">sys.dm_exec_sql_text<\/span>, to get the plan of the query or the text of the query, respectively.<\/li>\n<li><span class=\"STCodeinTextBold\">pool_id<\/span><b> <\/b>&#8211; the resource governor pool to which the plan is tied. You can use <span class=\"STCodeinTextChar\">sys.resource_governor_resource_pools<\/span> to decipher the value, if you are utilizing resource governor. Note that this column is in SQL Server 2008 only and is part of the Enterprise-only resource governor feature.<\/li>\n<\/ul>\n<p>A few of these columns merit a bit more discussion. The <span class=\"STCodeinTextChar\">cachobtype<\/span> column describes the type of plan that is cached, which can be a normal SQL plan (compiled plan), a parse tree or the stored plan for an extended procedure or CLR object. A parse tree is a bare-bones plan for an object such as a view. It specifies the objects referenced by the view, but does not specify a full execution plan. Views are compiled into the query at run time; they do not have plans of their own.<\/p>\n<p>The <span class=\"STCodeinTextChar\">objtype<\/span> column, in turn, specifies the type of object, determing whether it is typical T-SQL or a compiled object, such as a stored procedure or prepared statement. Note that a prepared statement (an ad hoc statement from the client, where they have used the API to prepare a plan ahead of time) has a different plan than an ad hoc query.<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>Zero cost and stale plans<\/b><br \/>\nRemember, again, that not all queries that have been executed on your server will have saved a plan. Zero cost plans like <span class=\"STCodeinTextChar\">&#8220;SELECT<\/span> <span class=\"STCodeinTextChar\">&#8216;hi'&#8221;<\/span> would not, for sure. Also, as plans become stale, they could be removed from cache, particularly if the server is under memory pressure.<\/p>\n<\/div>\n<p>So, for example, you can retrieve the plans for any compiled object, using the query shown in Listing 1.6:<\/p>\n<pre>SELECT\u00a0 refcounts ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 usecounts ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 size_in_bytes ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cacheobjtype ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 objtype\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_cached_plans\r\nWHERE\u00a0\u00a0 objtype IN ( 'proc', 'prepared' ) ;\r\n<\/pre>\n<p class=\"caption\">Listing 1.6: Retrieving the plans for compiled objects.<\/p>\n<p>You are likely to find that there are compiled plans for both procedures and prepared statements, as well as some extended and CLR objects, if you use them.<\/p>\n<h2>Investigating plan reuse<\/h2>\n<p>Good plan reuse is one sign of a heathy system. Compiling a query plan can be a CPU-intensive operation, especially with complex queries, so reuse is a very good thing. The greater the value in the <span class=\"STCodeinTextChar\">usecount<\/span> column for each of your plans, the greater the number of times query plans are reused, and the fewer the number of times a new query plan has to be recreated. Conversely, a <span class=\"STCodeinTextChar\">usecount<\/span> of 1 for a large number of plans indicates that your cache space is being taken up with plans that were compiled and used once to execute an ad hoc query, then never used again. By querying <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span> and aggregating on the <span class=\"STCodeinTextChar\">usecount<\/span> column we can, as we will show, get a good overview of the extent to which plans are being reused in your system.<\/p>\n<p>The more you can use stored procedures or, at the very least, prepared SQL, the more likely you are to get plan reuse. The more reusable plans you have, the less work the optimizer needs to do.<\/p>\n<p>One of the most critical factors in determining reuse is the text of the query. If the text of a query submitted for execution matches the text of a query in the cache, then the plan for that cached query may be reused.<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>Criteria in determing plan reuse<\/b> <br \/>\nThe text of the query is not the only criterion in determing plan reuse. The attributes of the plan are figured in also, which we will cover in a later section, <span class=\"STItalic\">Query Plan Attributes<\/span>.<\/p>\n<\/div>\n<p>Of course, if you use stored procedures as the primary interface to your SQL Server data, then you have a much cleaner way of ensuring reuse.<\/p>\n<p>A database setting that can be helpful to plan reuse is forced parameterizaton (set using <span class=\"STCodeinTextChar\">ALTER DATABASE &lt;databaseName&gt; SET PARAMETERIZATION FORCED<\/span>). Normally, the query optimizer is very conservative when deciding what queries can be parameterized, but this setting makes the optimizer be more liberal in choosing parameters. For more reading on this subject, look for &#8220;Forced Parameterization&#8221; in Books Online.<\/p>\n<p>Finally, bear in mind that you need to be careful when interpreting the results of data based on plan reuse. After a restart, cache flush, procedure recreation, and so on, plan use counts will be low without it being indicative of a problem. Alternatively, you may have been doing some special processing that inflates the counts and makes things look better than normal. As is true with most of the DMVs, the data you get from the <span class=\"STCodeinTextChar\">cached_plans<\/span> DMV is not information until it has context. Context comes from tracking the data over long periods of time and comparing like time periods of server activity.<\/p>\n<p>As dicussed earlier, you might want, particularly for testing purposes, to consider clearing the cache at a fixed time (e.g. 12 a.m.) each day, to give the results more context.<\/p>\n<h3>The plan reuse &#8220;distribution curve&#8221;<\/h3>\n<p>A simple query, such as that shown in Listing 1.7, will show you how many plans are cached.<\/p>\n<pre>SELECT\u00a0 COUNT(*)\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_cached_plans ;\r\n<\/pre>\n<p class=\"caption\">Listing 1.7: Total number of cached plans.<\/p>\n<p>More useful though, is to get a feel for the &#8220;distribution&#8221; of plan reuse on your system. To do this, we can group on the <span class=\"STCodeinTextChar\">usecounts<\/span> column, and use the <span class=\"STCodeinTextChar\">CASE<\/span> expression to roll up bands of <span class=\"STCodeinTextChar\">usecounts<\/span> as shown in Listing 1.8.<\/p>\n<pre>SELECT\u00a0 MAX(CASE WHEN usecounts BETWEEN 10 AND 100 THEN '10-100'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 101 AND 1000 THEN '101-1000'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 1001 AND 5000 THEN '1001-5000'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 5001 AND 10000 THEN '5001-10000'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE CAST(usecounts AS VARCHAR(100))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END) AS usecounts ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) AS countInstance\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_cached_plans\r\nGROUP BY CASE WHEN usecounts BETWEEN 10 AND 100 THEN 50\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 101 AND 1000 THEN 500\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 1001 AND 5000 THEN 2500\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 5001 AND 10000 THEN 7500\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE usecounts\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\nORDER BY CASE WHEN usecounts BETWEEN 10 AND 100 THEN 50\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 101 AND 1000 THEN 500\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 1001 AND 5000 THEN 2500\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN usecounts BETWEEN 5001 AND 10000 THEN 7500\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE usecounts\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END DESC ;\r\n<\/pre>\n<p class=\"caption\">Listing 1.8: An overview of plan reuse.<\/p>\n<p>For a server that had been up for two days, we got the following results:<\/p>\n<pre>usecounts\u00a0\u00a0 countInstance\r\n----------- -------------\r\n169279\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n100911\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n18379\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n17817\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n16608\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a01\r\n10004\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n5001-10000\u00a0 19\r\n1001-5000\u00a0\u00a0 87\r\n101-1000\u00a0\u00a0\u00a0 127\r\n10-100\u00a0\u00a0\u00a0\u00a0\u00a0 359\r\n9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\r\n8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 55\r\n7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12\r\n6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 51\r\n5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18\r\n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 628\r\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 73\r\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 277\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1988\r\n<\/pre>\n<p>So, for example, we have five plans that have been reused over 10,000 times, 359 plans that have been reused between 10 and 100 times, 1,988 plans that have only been used once, and so on. Clearly, this is only an overview, and just to give you an overall feel for plan resue. It&#8217;s a query I like to run semi-regularly to see if there&#8217;s any noticable change in the &#8220;distribution&#8221; to more (or less) reuse.<\/p>\n<p>While it is clear from the listing that we are probably getting decent reuse (particularly on a few often-executed queries), what we cannot tell at this point is how many queries are being executed on the server (although we can find that out) or how many get flushed from cache. What you will be able to tell is the most important queries that get reused, and a place to look for improvements.<\/p>\n<p>The next step is to examine those plans at the extreme ends of the spectrum. If we can improve the efficiency of a plan that that is being reused many times, it could be highly beneficial. Likewise, we need to find out more information about the plans that are never reused, why this might be, and if there is anything we can do about it.<\/p>\n<h3>Examining frequently-used plans<\/h3>\n<p>Using <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span> in conjunction with <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> and <span class=\"STCodeinTextChar\">sys.dm_exec_sql_text<\/span>, we can construct a query to return the text of the plan, plus the text of the query that is associated with the plan and has the highest use counts. So, if we identify a query, using Profiler or one of the operating system-related DMOs, that is offensive for some performance reason (CPU pressure, duration, memory utilization, etc.), and then find that it is attached to a plan with a very high <span class=\"STCodeinTextChar\">usecount<\/span>, then we know we&#8217;ve found a good place to start our tuning efforts for the day.<\/p>\n<p>The script in Listing 1.9 returns, for the most-reused plans, the query plan itself, the type of object or query with which the plan is associated and the SQL text for that query or object.<\/p>\n<pre>SELECT TOP 2 WITH TIES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 decp.usecounts ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 decp.cacheobjtype ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 decp.objtype ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deqp.query_plan ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dest.text\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_cached_plans decp\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest\r\nORDER BY usecounts DESC ;\r\n<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1118-DMV5.jpg\" alt=\"1118-DMV5.jpg\" \/><\/p>\n<p class=\"caption\">Listing 1.9: Investigating the most-used plans.<\/p>\n<p>The real beauty of being able to do this from SQL, rather than some prebuilt report, is that you have the full power of the query engine in your hands. For example, if we want to look at only plans for stored procedures, we can simply filter on <span class=\"STCodeinTextChar\">objtype = &#8216;proc&#8217;<\/span>.<\/p>\n<p>Of course, this is only one piece of the puzzle and, later in the article, when we start to look at how we can see the individual queries from a batch or compiled object that make up the entire plan, it will become more useful to the tuning efforts.<\/p>\n<p>We can examine our frequently-reused plans for queries that contain certain text, as we did previously in Listing 1.3. Alternatively, we can filter on the <span class=\"STCodeinTextChar\">objectid<\/span>, to examine plan reuse for a single procedure, as shown in Listing 1.10.<\/p>\n<pre>SELECT\u00a0 usecounts ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cacheobjtype ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 objtype ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_NAME(dest.objectid)\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_cached_plans decp\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest\r\nWHERE\u00a0\u00a0 dest.objectid = OBJECT_ID('&lt;procedureName&gt;')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND dest.dbid = DB_ID()\r\nORDER BY usecounts DESC ;\r\n<\/pre>\n<p class=\"caption\">Listing 1.10: Examining plan reuse for a single procedure.<\/p>\n<p>In SQL Server 2008, we can use the <span class=\"STCodeinTextChar\">sys.dm_exec_procedure_stats<\/span> DMV to look exclusively at cached plans for stored procedures. We&#8217;ll examine this DMV a little later.<\/p>\n<h3>Examining ad hoc single-use plans<\/h3>\n<p>The script in Listing 1.11 uses the <span class=\"STCodeinTextBold\">sys.dm_exec_cached_plans<\/span> DMV and <span class=\"STCodeinTextBold\">sys.dm_exec_sql_text<\/span> DMF to retrieve the text for each single-use plan that is bloating the plan cache.<\/p>\n<pre>-- Find single-use, ad hoc queries that are bloating the plan cache\r\nSELECT TOP ( 100 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [text] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cp.size_in_bytes\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_cached_plans AS cp\r\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0CROSS APPLY sys.dm_exec_sql_text(plan_handle)\r\nWHERE\u00a0\u00a0 cp.cacheobjtype = 'Compiled Plan'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND cp.objtype = 'Adhoc'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND cp.usecounts = 1\r\nORDER BY cp.size_in_bytes DESC ;\r\n<\/pre>\n<p class=\"caption\">Listing 1.11: Examining single-use plans in the cache.<\/p>\n<p>This query will identify ad hoc queries that have a use count of 1, ordered by the size of the plan. It provides the text and size of single-use ad hoc queries that waste space in plan cache. This usually happens when T-SQL commands are built by concatenating a variable at the end of a &#8220;boilerplate&#8221; T-SQL statement. A very simplified example is shown in Listing 1.12.<\/p>\n<pre>-- Query 1\r\nSELECT\u00a0 FirstName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LastName\r\nFROM\u00a0\u00a0\u00a0 dbo.Employee\r\nWHERE\u00a0\u00a0 EmpID = 5\r\n\r\n-- Query 2\r\nSELECT\u00a0 FirstName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LastName\r\nFROM\u00a0\u00a0\u00a0 dbo.Employee\r\nWHERE\u00a0\u00a0 EmpID = 187\r\n<\/pre>\n<p class=\"caption\">Listing 1.12: Non-parameterized ad hoc SQL.<\/p>\n<p>Even though these two queries are essentially identical, they might each have a separate plan in the cache, just because the literal value is different in each case. Actually these two queries are so simple (with no joins) that SQL Server would probably parameterize them, even using just the default simple parameterization (as opposed to forced parameterization). However, in more complex cases, plan reuse would probably not be possible. While we can do our best to make sure queries are written in a way that enables plan reuse, users and utilities often execute batches that simply will have little reuse.<\/p>\n<p>For SQL Server 2008, if you determine that you have a mostly ad hoc workload with minimal reuse, check out the &#8220;optimize for ad hoc workloads&#8221; system option. This setting changes SQL Server behavior and does not store a plan on first usage, only the query text. If it matches a second time, the plan is stored. Note that &#8220;ad hoc&#8221;, in this sense, does vary from some people&#8217;s understanding of the term. Here, it simple refers to the use of random queries that don&#8217;t promote reuse.<\/p>\n<h1>Query Plan Attributes<\/h1>\n<p>In the previous sections, we&#8217;ve covered how we get the text of a query plan and then examine plan reuse on your system, using the <span class=\"STCodeinTextChar\">cached_plans<\/span> DMV. In addition, a DBA will sometimes want to find out &#8220;state&#8221; information about a particular plan, along with further details about how that plan is currently being used.<\/p>\n<p>The database engine takes into account several factors when considering a plan for reuse. One of these factors, as we discussed previously, is the text of the query. However, other attributes are important, too, the values for which are stored in the <span class=\"STCodeinTextChar\">sys.dm_exec_plan_attributes<\/span> function, described by BOL as follows:<\/p>\n<p><em>&#8220;Returns one row per plan attribute for the plan specified by the plan handle. You can use this table-valued function to get details about a particular plan, such as the cache key values or the number of current simultaneous executions of the plan.&#8221;<\/em><\/p>\n<p>Like <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span>, <span class=\"STCodeinTextChar\">sys.dm_exec_plan_attributes<\/span> takes a <span class=\"STCodeinTextChar\">plan_handle<\/span> as a parameter and returns the following columns:<\/p>\n<ul>\n<li><span class=\"STCodeinTextBold\">attribute<\/span> &#8211; name of the attribute<\/li>\n<li><span class=\"STCodeinTextBold\">value<\/span> &#8211; the current value assigned to the attribute<\/li>\n<li><span class=\"STCodeinTextBold\">is_cache_key<\/span> &#8211; indicates if the attribute is part of how SQL Server resolves the plan. A value of 1 indicates that it is, and 0 that it is not.<\/li>\n<\/ul>\n<p>This DMV returns one row per plan attribute, and available attributes include:<\/p>\n<ul>\n<li><span class=\"STCodeinTextBold\">set_options<\/span> &#8211; the options values (the ones that can be found using <span class=\"STCodeinTextChar\">@@options<\/span>, such as <span class=\"STCodeinTextChar\">SET NOCOUNT<\/span>) that were in use when the plan was built<\/li>\n<li><span class=\"STCodeinTextBold\">date_format<\/span> &#8211; the date format of the connection that created the plan<\/li>\n<li><span class=\"STCodeinTextBold\">inuse_exec_context<\/span> &#8211; the number of currently executing batches that are using the plan<\/li>\n<\/ul>\n<p>If the value of <span class=\"STCodeinTextChar\">is_cache_key<\/span> is 1 for a given attribute, then that attribute forms part of the &#8220;key&#8221; that is used when SQL Server searches the cache for a suitable plan. During the process of checking the cache for a match, the text of a submitted query is compared to the text of other queries that have been executed and for which a plan is stored. Even if the text of the queries matches exactly, the submitted query&#8217;s attributes would also need to match exactly those for the cached plan, for every attribute with a value of 1 for the <span class=\"STCodeinTextChar\">is_cache_key<\/span> column in <span class=\"STCodeinTextChar\">sys.dm_exec_plan_attributes<\/span>.<\/p>\n<p>Consider, for example, the code in Listing 1.13, which uses a <span class=\"STCodeinTextChar\">plan_handle<\/span> extracted from <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span>, on my test server, and supplies it as a parameter to <span class=\"STCodeinTextChar\">sys.dm_exec_plan_attributes<\/span>. Note the use of <span class=\"STCodeinTextChar\">TOP 1 &#8230; ORDER BY usecounts DESC<\/span> in the derived table to get the largest reused plan.<\/p>\n<pre>SELECT\u00a0 CAST(depa.attribute AS VARCHAR(30)) AS attribute ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(depa.value AS VARCHAR(30)) AS value ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 depa.is_cache_key\r\nFROM\u00a0\u00a0\u00a0 ( SELECT TOP 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0\u00a0\u00a0 sys.dm_exec_cached_plans\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY\u00a0 usecounts DESC\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) decp\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OUTER APPLY sys.dm_exec_plan_attributes(decp.plan_handle) depa\r\nWHERE\u00a0\u00a0 is_cache_key = 1\r\nORDER BY usecounts DESC ;\r\n<\/pre>\n<p class=\"caption\">Listing 1.13: Examining plan attributes.<\/p>\n<p>This returns the following results:<\/p>\n<pre>attribute\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 value\u00a0\u00a0\u00a0\u00a0 is_cache_key\r\n---------------------------- -------------------- -\r\nset_options\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 187\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nobjectid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 733550834\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\ndbid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\ndbid_execute\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\r\nuser_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nlanguage_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\ndate_format\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\ndate_first\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nstatus\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\r\nrequired_cursor_options\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nacceptable_cursor_options\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n<\/pre>\n<p>So, in order for a cached plan to be used to execute the query in Listing 1.13, all of the returned attribute values would have to match. For example, both the submitted query and the cached plan would need to have <span class=\"STCodeinTextChar\">set_options<\/span> <span class=\"STCodeinTextChar\">= 187<\/span>, <span class=\"STCodeinTextChar\">objectid = 733550834, dbid = 4<\/span>, and so on. If there was a mismatch of any kind, a new plan would be generated. So, for example, say you were testing the query in Management Studio in the context of a database different to the one used when an application first issued the query. The attributes of the existing plan for the application-issued query, and those for your SSMS-issued query, might match in terms of the SQL text and \u00a0maybe even the <span class=\"STCodeinTextChar\">objectid<\/span> (somewhat less likely), but the <span class=\"STCodeinTextChar\">dbid<\/span> would be different, so the plan would not get reused and a new one would be created. In such cases, the SQL text for the query, as identified by the <span class=\"STCodeinTextChar\">sql_handle<\/span>, would now be associated with more than one plan, i.e. there would be more than one <span class=\"STCodeinTextChar\">plan_handle<\/span> associated with a given <span class=\"STCodeinTextChar\">sql_handle<\/span>.<\/p>\n<p>This sounds rather horrible, but it&#8217;s important to realize that each of these attributes, including security attributes, is important for determining how the query will be executed. Furthermore, one shouldn&#8217;t really expect the same plan to be used, even if the SQL text and some of the attributes match. One database may have zero rows in table X, whereas the other one may have 1.5 billion; same exact query text, vastly different plans.<\/p>\n<p>Checking the attributes of a plan is not necessarily a very common thing to need to do as, most of the time, your client will have a very standard set of settings. However, on that rare occasion that you see no plan reuse, but you can clearly see multiple queries with the same query text, the <span class=\"STCodeinTextChar\">sys.dm_exec_plan_attributes<\/span> DMV will help you seek out the reasons.<\/p>\n<h1>Gathering Query Execution Statistics<\/h1>\n<p>Up to this point in the article, we&#8217;ve shown how to get the text of a cached plan, and how to return detailed information about those plans. One plan is stored for a batch or an object and we used <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span> to get plan information.<\/p>\n<p>Each plan has 1-to-N queries and, in this section, we&#8217;re going to explore how to get detailed information about the individual queries in the batch or object. To get these query statistics, be it for a standalone query or a query within a larger stored procedure or prepared statement, we use the <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> DMV.<\/p>\n<p>The <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> DMV will return one row per query that is executed within a batch or stored procedure, and provides columns such as <span class=\"STCodeinTextChar\">total_worker_time<\/span> (CPU), <span class=\"STCodeinTextChar\">total_physical_reads<\/span>, <span class=\"STCodeinTextChar\">total_logical_reads<\/span> and so on, which can give you a very useful overview of the system resources that your queries are eating up.<\/p>\n<p>Pre-SQL Server 2005, the only way to get this sort of information was through Profiler. However, Profiler only lets you collect the information <span class=\"STItalic\">as the queries occur<\/span>, not after the event. Now, the next time you hear about a problem second-hand, while walking down the hallway, which is too often how users report problems, you&#8217;ll be able to interrogate the <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> DMV and find out what happened. This is an exciting step forward for most DBAs, especially given that resorting to Profiler after the event often entailed capturing traces over long periods of time, in order to reobserve the problem, and this could be really costly in terms of server resources and programmer sanity, since doing the matching of query text after the fact is very difficult.<\/p>\n<h2>Overview of sys.dm_exec_query_stats<\/h2>\n<p>The <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> DMV contains quite a few columns that are incremented counters, and provide information about how many times a query has been executed and the resources that were used. It is described by BOL as follows:<\/p>\n<p>&#8220;Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows is tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.&#8221;<\/p>\n<p>As a whole, it provides a wealth of information regarding resource-hungry queries on your system, for which a plan is cached. The columns that can be returned are as follows:<\/p>\n<ul>\n<li><span class=\"STCodeinTextChar\">sql_handle<\/span> &#8211; identifies the batch or procedure to which a query belongs; it is used by <span class=\"STCodeinTextChar\">sys.dm_exec_sql_text<\/span> to get the text of the batch<\/li>\n<li><span class=\"STCodeinTextChar\">statement_start_offset<\/span> &#8211; the starting point of the query within the batch or object to which the query belongs<\/li>\n<li><span class=\"STCodeinTextChar\">statement_end_offset<\/span> &#8211; the end point of the query that is currently executing, within the batch or object to which the query belongs<\/li>\n<li><span class=\"STCodeinTextChar\">plan_generation_num<\/span> &#8211; indicates the version of the query plan that has been created after a recompile; used for comparisons because the actual plan may change (or even partially change for a statement-level recompile), even though the SQL stays the same<\/li>\n<li><span class=\"STCodeinTextChar\">plan_handle<\/span> &#8211; identifies the cached query plan for a batch or stored procedure that has been executed; used by <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> or <span class=\"STCodeinTextChar\">sys.dm_exec_text_query_plan<\/span> to get the plan of an executed query in XML format<\/li>\n<li><span class=\"STCodeinTextChar\">creation_time<\/span> &#8211; time the plan was created<\/li>\n<li><span class=\"STCodeinTextChar\">last_execution_time<\/span> &#8211; last time the execution plan was used to execute a query<\/li>\n<li><span class=\"STCodeinTextChar\">execution_count<\/span> &#8211; number of times the plan has been used to execute a query<\/li>\n<li><span class=\"STCodeinTextChar\">total_worker_time, last_worker_time, min_worker_time, max_worker_time<\/span> &#8211; total, last, min, and max amount of time spent in CPU utilization to execute the query, based on this plan<\/li>\n<li><span class=\"STCodeinTextChar\">total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads<\/span> &#8211; total, last, min, and max number of reads to the physical hard disk system<\/li>\n<li><span class=\"STCodeinTextChar\">total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes<\/span> &#8211; total, last, min, and max number of writes to the buffer cache to be written by lazy writer<\/li>\n<li><span class=\"STCodeinTextChar\">total_logical_reads, last_logical_reads, min_logical_reads, max_logical_reads<\/span> &#8211; total, last, min, and max number of reads from the SQL Server cache buffer that never had to go to the physical hard disk system to satisfy the current request; this data was read in previously and was still in cache<\/li>\n<li><span class=\"STCodeinTextChar\">total_clr_time, last_clr_time, min_clr_time, max_clr_time<\/span> &#8211; total, last, min, and max amount of time spent in the CLR processor for the query that utilized this plan<\/li>\n<li><span class=\"STCodeinTextChar\">total_elapsed_time, last_elapsed_time, min_elapsed_time, max_elapsed_time<\/span> &#8211; in ms, the total, last, min, and max amounts of time it took to execute the entire query.<\/li>\n<\/ul>\n<p><strong>Note: Time columns are in microseconds.<\/strong><\/p>\n<h2>Putting sys.dm_exec_query_stats to work<\/h2>\n<p>Once you are able to extract individual statements from a batch, as demonstrated in the earlier section, <span class=\"STItalic\">Dissecting the SQL Text,<\/span> you can use <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> to access the query statistics for individual SQL statements that are executing on your system. The sky is the limit with regard to how you might use this information to find where you have performance issues.<\/p>\n<p>Generally speaking, at the point where you would turn to <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span>, you would already have an idea of whether your system is IO bound, CPU bound, or having CLR performance issues, and so on, likely from using other DMVs or a tool such as the performance monitor (PerfMon). So, your goal at this stage would be to get a ranked list of queries that are having the biggest effect on the issue that you&#8217;ve identified.<\/p>\n<p>Before we look at the scripts, it is important to remember when using this DMV that there is usually, but not necessarily, a one-to-one relationship between a <span class=\"STCodeinTextChar\">sql_handle<\/span> and a <span class=\"STCodeinTextChar\">plan_handle<\/span>. One <span class=\"STCodeinTextChar\">sql_handle<\/span> can sometimes be associated with more than one <span class=\"STCodeinTextChar\">plan_handle<\/span>. This can be caused by statement-level recompilation (see <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ee343986%28SQL.100%29.aspx\">http:\/\/technet.microsoft.com\/en-us\/library\/ee343986(SQL.100).aspx<\/a> for more details), or by the exact same SQL text being executed with different attribute values, as discussed earlier.<\/p>\n<p>Let&#8217;s say that you&#8217;ve identified that your sytem is CPU bound. Using the <span class=\"STCodeinTextChar\">total_worker_time<\/span> column, you can find out which queries your server is spending the most time executing. However, it isn&#8217;t enough just to know that the server is spending a lot of time executing a particular query. In fact, without context, this piece of information is more or less meaningless. It might be that the query is run a million times, and no other query is executed more than a thousand times. So, to add the required context, we include the <span class=\"STCodeinTextChar\">execution_count<\/span>, along with a calculation of the average CPU time, as shown in Listing 1.14.<\/p>\n<pre>SELECT TOP 3\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 total_worker_time ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 execution_count ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 total_worker_time \/ execution_count AS [Avg CPU Time] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN deqs.statement_start_offset = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND deqs.statement_end_offset = -1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN '-- see objectText column--'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE '-- query --' + CHAR(13) + CHAR(10)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + SUBSTRING(execText.text, deqs.statement_start_offset \/ 2,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ( CASE WHEN deqs.statement_end_offset = -1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN DATALENGTH(execText.text)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE deqs.statement_end_offset\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END ) - deqs.statement_start_offset ) \/ 2)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END AS queryText\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_query_stats deqs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText\r\nORDER BY deqs.total_worker_time DESC ;\r\n<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1118-DMV6.jpg\" alt=\"1118-DMV6.jpg\" \/><\/p>\n<p class=\"caption\">Listing 1.14: Finding the CPU-intensive queries.<\/p>\n<p>The results, representing a set of queries from a busy server, were extensive, but we&#8217;ve only shown the first three queries here (we don&#8217;t get paid by the page, unfortunately). From here, we can start to get a feeling for what queries are hurting the CPU the most since the last system reboot. However, bear in mind the earlier discussion in the <span class=\"STItalic\">Flushing the Cache?<\/span> section, with regard to the different lengths of time plans will have been in the cache, and how this can skew the results.<\/p>\n<p>At this stage, it&#8217;s somewhat difficult to gauge which of these queries is the most pertinent to the performance issue, but these statistics make an excellent starting point for our investigations. The first query executed 527 times and is taking the most time, while the second one has been executed almost four times as frequently, but is taking one-third of the CPU time. The third query only ran once, but took a huge amount of CPU time. This is troublesome since it is impossible to know whether this was a one-off event, or if this query just hasn&#8217;t run that often since the server was restarted or <span class=\"STCodeinTextChar\">DBCC FREEPROCCACHE<\/span> was executed to clear the query cache. An interesting column to check, especially with regard to a plan with a single execution like this one, is <span class=\"STCodeinTextChar\">last_execution_time<\/span>, which will tell you exactly when the plan was last used. Along with the query text, this can help you to judge whether or not this execution was planned, and is part of you normal query workload, or is just an unfortunate ad hoc query execution.<\/p>\n<p>The next step would be to plug the <span class=\"STCodeinTextChar\">plan_handle<\/span> for each query (which I didn&#8217;t include in the results due to space constraints) into the <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> (see the section entitled <span class=\"STItalic\">Viewing the Text of Cached Queries and Query Plans<\/span>) and investigate how each query is being executed, and whether the query can be optimized.<\/p>\n<p>There is a lot more useful data that can be extracted from the <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> DMV using variations of the previously described techniques, based on &#8220;isolating&#8221; individual queries through the _offset values, filtering on specific objects, and so on. For example, you could write similar queries focusing on the following DMV columns:<\/p>\n<ul>\n<li><span class=\"STBold\">excessive logical writes<\/span> &#8211; data written to cache (which gets written to the physical hard disk via the lazy writer)<br \/>\n<span class=\"STCodeinTextChar\">total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes<\/span><\/li>\n<li><span class=\"STBold\">excessive logical reads<\/span> &#8211; queries that required the most data to be read from cache (possibly causing memory pressure) <br \/>\n<span class=\"STCodeinTextChar\">total_logical_reads, last_logical_reads, min_logical_reads, max_logical_reads<\/span><\/li>\n<li><span class=\"STCodeinTextChar\">\u00c2\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>&lt;![endif]&gt;<span class=\"STBold\"> excessive physical reads<\/span> &#8211; queries that forced the most physical hard disk access (generally caused by the need for more cache memory than was available, equating to heavy memory pressure since data could not be read from the cache)<br \/>\n<span class=\"STCodeinTextChar\">total_physical_reads, last_physical_reads, min_physical_reads , max_physical_reads<\/span><\/li>\n<li><span class=\"STCodeinTextChar\">\u00c2\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>&lt;![endif]&gt;<span class=\"STBold\"> long-running queries<\/span><br \/>\n<span class=\"STCodeinTextChar\">total_elapsed_time, last_elapsed_time, min_elapsed_time, max_elapsed_time<\/span><\/li>\n<li><span class=\"STBold\">expensive CLR code<\/span><br \/>\n<span class=\"STCodeinTextChar\">total_clr_time, last_clr_time, min_clr_time, max_clr_time.<\/span><\/li>\n<\/ul>\n<p>As discussed in the introduction to this article, it&#8217;s true that you can get similar information from the management reports in SSMS. The real win with the DMOs is the degree of control and granularity you can achieve through the <span class=\"STCodeinTextChar\">WHERE<\/span> clause. For example, we could easily modify the query in Listing 1.13 to return statistics only for batches that reference the <span class=\"STCodeinTextChar\">account<\/span> table, simply by adding the appropraite <span class=\"STCodeinTextChar\">WHERE<\/span> clause:<\/p>\n<pre>WHERE\u00a0\u00a0 execText.text LIKE '%account%'\r\n<\/pre>\n<p>Of course, we will probably have to be cleverer with the <span class=\"STCodeinTextChar\">LIKE<\/span> criteria if we have tables (not to mention columns) named <span class=\"STCodeinTextChar\">account<\/span> and <span class=\"STCodeinTextChar\">accountContact<\/span>, but this is just a SQL task. We could also use the entire definition of <span class=\"STCodeinTextChar\">queryText <\/span>and just look for the individual queries. This technique will come in handy many times when optimizing data access to a given object because, unlike most tools that come prebuilt, we can create queries that only look at a very small subsection of queries, eliminating noise that is perhaps not interesting to us during the current tuning process.<\/p>\n<p>Finally, there are times when we want to see the results at a complete query\/batch level, rather than for the individual parts of the query, which may have their own plans. This is complicated by the fact that, as discussed, one <span class=\"STCodeinTextChar\">sql_handle<\/span> may be associated with more than one <span class=\"STCodeinTextChar\">plan_handle<\/span>. Therefore, in order to see the total stats for all queries in the same batch, we need to group on the <span class=\"STCodeinTextChar\">sql_handle<\/span> and sum the values, as shown in Listing 1.15.<\/p>\n<pre>SELECT TOP 100\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(total_logical_reads) AS total_logical_reads ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) AS num_queries , --number of individual queries in batch\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --not all usages need be equivalent, in the case of looping\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --or branching code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX(execution_count) AS execution_count ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX(execText.text) AS queryText\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_query_stats deqs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS execText\r\nGROUP BY deqs.sql_handle\r\nHAVING\u00a0 AVG(total_logical_reads \/ execution_count) &lt;&gt; SUM(total_logical_reads)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/ SUM(execution_count)\r\nORDER BY 1 DESC \r\n<\/pre>\n<p class=\"caption\">Listing 1.15: Grouping by <span class=\"STCodeinTextChar\">sql_handle<\/span> to see query stats at the batch level.<\/p>\n<h1>Investigating Expensive Cached Stored Procedures<\/h1>\n<p>New to SQL Server 2008 is the <span class=\"STCodeinTextChar\">sys.dm_exec_procedure_stats <\/span>DMV that focuses only on stored procedures and will not require you to aggregate on <span class=\"STCodeinTextChar\">sql_handle<\/span> to get an overall view of your object&#8217;s performance characteristics. It is described by BOL as follows:<\/p>\n<p class=\"style19\">&#8220;Returns aggregate performance statistics for cached stored procedures. The view contains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span>.&#8221;<\/p>\n<p>It is similar in nature to <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span>, but with a few differences in the columns available. It only links to the plan of the procedure or object, not the individual statements in the procedure. This DMV allows you to discover a lot of very interesting and important performance information about your cached stored procedures.<\/p>\n<pre>-- Top Cached SPs By Total Logical Reads (SQL 2008 only).\r\n-- Logical reads relate to memory pressure\r\nSELECT TOP ( 25 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p.name AS [SP Name] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deps.total_logical_reads AS [TotalLogicalReads] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deps.total_logical_reads \/ deps.execution_count AS [AvgLogicalReads] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deps.execution_count ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ISNULL(deps.execution_count \/ DATEDIFF(Second, deps.cached_time,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GETDATE()), 0) AS [Calls\/Second] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deps.total_elapsed_time ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deps.total_elapsed_time \/ deps.execution_count AS [avg_elapsed_time] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 deps.cached_time\r\nFROM\u00a0\u00a0\u00a0 sys.procedures AS p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.dm_exec_procedure_stats\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0AS deps ON p.[object_id] = deps.[object_id]\r\nWHERE\u00a0\u00a0 deps.database_id = DB_ID()\r\nORDER BY deps.total_logical_reads DESC ;\r\n<\/pre>\n<p class=\"caption\">Listing 1.16: Investigating logical reads performed by cached stored procedures.<\/p>\n<p>Depending on what columns you include, and which column we order by, we can discover which cached stored procedures are the most expensive from several different perspectives. In this case, we are interested in finding out which stored procedures are generating the most total logical reads (which relates to memory pressure). This query is especially useful if there are signs of memory pressure, such as a persistently low page life expectancy and\/or persistent values above zero for memory grants pending. This query is filtered by the current database, but we can change it to be instance-wide by removing the <span class=\"STCodeinTextChar\">WHERE<\/span> clause.<\/p>\n<p>Simply by selecting the <span class=\"STCodeinTextChar\">total_physical_reads<\/span> column, instead of <span class=\"STCodeinTextChar\">total_logical_reads<\/span>, in this query, we can perform the same analysis from the perspective of physical reads, which relates to read, disk I\/O pressure. Lots of stored procedures with high total physical reads or high average physical reads, could indicate severe memory pressure, causing SQL Server to go to the disk I\/O subsystem for data. It could also indicate lots of missing indexes or &#8220;bad&#8221; queries (with no <span class=\"STCodeinTextChar\">WHERE<\/span> clauses, for example) that are causing lots of clustered index or table scans on large tables.<\/p>\n<p>Be aware though, that there are a couple of caveats with these queries. The big one is that you need to pay close attention to the <span class=\"STCodeinTextChar\">cached_time<\/span> column as you compare rows in the result set. If you have stored procedures that have been cached for different periods of time, then this will skew the results. One easy, but perhaps controversial, solution to this problem is to periodically clear your procedure cache, by running <span class=\"STCodeinTextChar\">DBCC<\/span> <span class=\"STCodeinTextChar\">FREEPROCCACHE<\/span> with a SQL Agent job, as previously discussed.<\/p>\n<p>The second caveat is that only cached stored procedures will show up in these queries. If you are using <span class=\"STCodeinTextChar\">WITH RECOMPILE<\/span> or <span class=\"STCodeinTextChar\">OPTION(RECOMPILE)<\/span>, which is usually not a good idea anyway, then those plans won&#8217;t be cached.<\/p>\n<h1>Getting Aggregate Query Optimization Statistics for All Optimizations<\/h1>\n<p>In this section, we will look at how we can get statistics on all optimizations that have been performed by the optimizer, regardless of whether or not a plan has been stored. This data is available from the <span class=\"STCodeinTextChar\">sys.dm_exec_query_optimizer_info<\/span> DMV and it will allow us to get a feel for how queries have been optimized, and how many of them have been optimized, since the last time the server was restarted.<\/p>\n<p>The data available from this DMV is often not attainable by looking at actual plan usage, and it can be very useful in gaining an overall understanding of the performance of a server. For example, trivial plans are not stored in cache, so we can&#8217;t get any information on them from the cached plan views but <span class=\"STCodeinTextChar\">_query_optimizer_info<\/span> can tell us the number of times a trivial plan was obtained.<\/p>\n<p>This view also provides a lot of other information that can only be found here (or attained in real time, using Profiler), such as the types of statements that are being optimized, the number of hints used, and so on.<\/p>\n<p>The <span class=\"STCodeinTextChar\">sys.dm_exec_query_optimizer_info<\/span> DMV provides information on optimizer activity in the form of a set of counters. Every time a type of optimization occurs, the counter will be incremented and, in some cases, a current value will be included in an average. The counters are only incremented when a new query plan is created, so when query plans are matched and reused, there is no need for a compilation, so there would be no change to these counters. The three columns returned by this DMV are as follows:<\/p>\n<ul>\n<li><span class=\"STCodeinTextBold\">counter<\/span> &#8211; the type of operation that the optimizer has done<\/li>\n<li><span class=\"STCodeinTextBold\">occurrence<\/span> &#8211; number of times the operation the counter represents has occurred<\/li>\n<li><span class=\"STCodeinTextBold\">value<\/span> &#8211; may or may not have some value, but is typically an average of the values that were recorded when the counter was written to.<\/li>\n<\/ul>\n<p>The view will return one row for each of the possible counter types. The domain of counter values includes <span class=\"STCodeinTextChar\">optimizations<\/span>, which is the total count of optimizations since system start; <span class=\"STCodeinTextChar\">elapsed time<\/span> which is the average elapsed time to complete the optimization of an individual statement, in seconds (averaged over total count of optimizations) and a lot of other interesting values that can tell you the number of insert, delete, or update statements that have been optimized, queries optimized with subqueries, and many others.<\/p>\n<p>For example, on a freshly restarted server, let&#8217;s take a look at three of the counters, as shown in Listing 1.17.<\/p>\n<pre>SELECT\u00a0 counter ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 occurrence ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 value\r\nFROM\u00a0\u00a0\u00a0 sys.dm_exec_query_optimizer_info\r\nWHERE\u00a0\u00a0 counter IN ( 'optimizations', 'elapsed time', 'final cost' ) ;\r\n\r\n<\/pre>\n<p class=\"caption\">Listing 1.17: Examine optimizer counters<\/p>\n<p>his returns something along the lines of:<\/p>\n<pre>counter\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 occurrence\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 value\r\n------------------- -----------------------\r\noptimizations\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nelapsed time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.0074\r\nfinal cost\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.0225006594\r\n<\/pre>\n<p>From this data we can see that:<\/p>\n<ul>\n<li>there have been a total of five statements optimized, and and for which a plan has been created (the value column for optimizations is documented as having no meaning in Books Online)<\/li>\n<li>the average average elapsed time spent optimizing each of the five statements was 0.0074 seconds<\/li>\n<li>the average cost of producing an optimized plan in each case was ~ 0.023.<\/li>\n<\/ul>\n<p>Using these counters, and some of the others, we can determine how often statements are being compiled. Of course, the usual caveats apply in that, since the values start accumulating as of the start of the server, there may be more or less information to be gathered from the data. I certainly won&#8217;t make the claim that you can necessarily discern anything from these values without any other data. It can help to track the accumulating counter type values over time.<\/p>\n<p>Assuming there is only one user on the system, if we re-execute the query in Listing 1.17 the values will probaly remain the same, as we just executed the same exact statement and a plan already exists. However, try making the query upper case, as shown in Listing 1.18.<\/p>\n<pre>SELECT\u00a0 COUNTER ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OCCURRENCE ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUE\r\nFROM\u00a0\u00a0\u00a0 SYS.DM_EXEC_QUERY_OPTIMIZER_INFO\r\nWHERE\u00a0\u00a0 COUNTER IN ( 'optimizations', 'elapsed time', 'final cost' ) ;\r\n<\/pre>\n<p class=\"caption\">Listing 1.18: Trivial changes to query text can affect plan reuse.<\/p>\n<p>Now the query text does not match that of the previous query so, when we execute it, a new plan will have to be created and we should see the <span class=\"STCodeinTextChar\">occurrence<\/span> column incremented in value, and there may be some difference in the average times.<\/p>\n<pre>counter\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 occurrence\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 value\r\n------------------- ---------------------- \r\noptimizations\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nelapsed time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.03867\r\nfinal cost\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.10053\r\n<\/pre>\n<p>Note that the number of optimizations didn&#8217;t increment by just 1. When a server is starting up there are many things going on. I also started up Agent when I restarted the server and there were compilations for those queries as well.<\/p>\n<h1>Summary<\/h1>\n<p>With the information in this article, you can really start to get a picture of a system&#8217;s query health, even if you have not had long-term access to the server. The scripts presented are especially useful when you need to start to drill down into a performance bottleneck, having established a high-level idea of what is the biggest performance bottleneck.<\/p>\n<p>One of the biggest advantages of examining cached queries and plans is that the DBA can take a longer term view of performance diagnosis, beyond simply responding to what is happening right now on the server. By running these scripts regularly, the DBA can proactively tune queries that may be hogging resources, but not yet to the extent that it is being noticed by an end-user (thereby raising it to the level of national emergency).<\/p>\n<p>We&#8217;ve seen how to extract the query plan and SQL text from <span class=\"STCodeinTextChar\">sys.dm_exec_sql_text<\/span> and <span class=\"STCodeinTextChar\">sys.dm_exec_query_plan<\/span> functions, and how to investigate plan reuse, using <span class=\"STCodeinTextChar\">sys.dm_exec_cached_plans<\/span> and <span class=\"STCodeinTextChar\">sys.dm_exec_plan_attributes<\/span>. However, the centerpieces of our diagnostic efforts, in this article, were the <span class=\"STCodeinTextChar\">sys.dm_exec_query_stats<\/span> and <span class=\"STCodeinTextChar\">sys.dm_exec_procedure_stats<\/span> views, from which we obtained extremely valuable information regarding the time and resources used by our most expensive queries and stored procedures. Bear in mind that the plans don&#8217;t stay in the cache forever, so it isn&#8217;t a perfect science, but larger-cost plans do tend to stick around.<\/p>\n<p>Lastly, we looked at the <span class=\"STCodeinTextChar\">sys.dm_exec_query_optimizer_info<\/span> DMV that lets you get an understanding of the overall performance of the optimizer, which includes all optimizations, including ones that create trivial plans that are never stored in the cache.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Before you can tackle any performance issues with a working database, you need to know which queries to work on first: The ones that are taking the most time in total, and which are the most expensive in terms of cache, CPU and disk. Although SQL Server Management Studio can help, it isn&#8217;t long before you need an armoury of DMVs to provide you the statistics to find the culprits.  &hellip;<\/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":[143531],"tags":[4168,4750,5215,4150,4151,4252,5214],"coauthors":[19684,48580],"class_list":["post-969","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-dynamic-management-views","tag-louis-davidson","tag-sql","tag-sql-server","tag-t-sql-programming","tag-tim-ford"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/969","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=969"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/969\/revisions"}],"predecessor-version":[{"id":73714,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/969\/revisions\/73714"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=969"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}