Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 3: Query Plan Metadata

Chapter 3: Query Plan Metadata

• Statistics and query plan metadata are extracted from the DMVs below:
• sys.dm_exec_query_stats – returns aggregated performance statistics for a cached query plan. Returns one row per statement within the plan.
• sys.dm_exec_procedure_stats – returns aggregated performance statistics for cached stored procedures. Returns one row per stored procedure.
• sys.dm_exec_cached_plans – 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.
• sys.dm_exec_query_optimizer_info – 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 … P75.
• 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 plan_handle, to one of the DMFs below:
• sys.dm_exec_query_plan – returns in XML format the query plan, identified by a plan_handle, for a SQL batch.
• sys.dm_exec_text_query_plan – returns in text format the query plan, identified by a plan_handle, for a SQL batch or, via the use of this DMF's offset columns, a specific statement within that batch.
• sys.dm_exec_plan_attributes – provides information about various attributes of a query plan, identified by a plan_handle, such as the number of queries currently using a given execution plan. It returns one row for each attribute … P76.
• 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 … P78.
• To retrieve the query plan, simply extract the plan_handle from the sys.dm_exec_query_stats DMV, then pass it as a parameter to one of the following DMFs:
• sys.dm_exec_query_plan, which accepts the plan_handle as its only parameter. Check last query of listing 3.2.
• sys.dm_exec_text_query_plan, which accepts the plan_handle and adds two additional parameters, statement_start_offset and statement_end_offset, which mark the start and end points of individual SQL statements within the batch or procedure … P79.
• You can save the extracted query plan to .SQLPLAN file, so you can compare it with the one after optimization … P82.
• To show only the “subplan” for each individual query in a batch, use listing 3.5. Unfortunately, the sys.dm_exec_query_plan DMF returns the plan in a form we can save and use, but not view in SSMS … P85.
• Listing 3.6: Retrieving the plans for compiled objects using the sys.dm_exec_cached_plans view. The following explains some columns in the result:
• refcounts – number of cache objects that reference this cached plan.
• usecounts – number of times the plan has been used since its creation.
• size_in_bytes – size of the plan.
• cacheobjtype – type of object in the cache. The domain is: Compiled Plan, Parse Tree, Extended Proc, CLR Compiled Func, CLR Compiled Proc.
• objtype – the type of object. The domain is: Proc (stored procedure, function), Prepared (prepared statement), Adhoc (query), Repl Proc (replication filter procedure), Trigger, View, Default, UsrTab (user table), SysTab (system table), CHECK, Rule … P88.
• Good plan reuse is one sign of a heathy system. Compiling a query plan can be a CPUintensive operation, especially with complex queries, so reuse is a very good thing. The greater the value in the usecount column for each of your plans, the greater the number of times query plans are reused, and the smaller the number of times a new query plan has to be recreated. Conversely, a usecount 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 … P90.
• Listing 3.8 shows the "distribution" of plan reuse on your system. The second column is the number of query plans that were [the value of the first column] number of times reused.
• If we can improve the efficiency of a plan that that is being reused many times, it could be highly beneficial. Listing 3.9 uses sys.dm_exec_cached_plans in conjunction with sys.dm_exec_query_plan and sys.dm_exec_sql_text 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. Alternatively, we can filter on the objectid, to examine plan reuse for a single procedure, as shown in Listing 3.10. … P94-95.
• Listing 3.11 identifies ad hoc queries with 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 the plan cache. If you determine that you have a mostly ad hoc workload with minimal reuse, check out the "optimize for ad hoc workloads" 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 … P97.
• The sys.dm_exec_query_stats DMV: 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. List of the columns returned by this DMV is in page P102.
• Listing 3.14: Gets the CPU-intensive queries. That query can be modified to get queries of excessive: logical writes, logical reads, physical reads, running time, or expensive CLR code using the columns listed in page 106.
• Listing 3.16: uses sys.dm_exec_procedure_stats to find out which CACHED stored procedures are generating the most total logical reads. 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 WHERE clause. Simply by selecting the total_physical_reads column, instead of total_logical_reads 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 "bad" queries (with no WHERE clauses, for example) that are causing lots of clustered index or table scans on large tables.
• Listing 3.17 uses sys.dm_exec_query_optimizer_info DMV to get statistics on all optimizations that have been performed by the optimizer. 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. The three columns returned by this DMV are as follows:
• counter – the type of operation that the optimizer has done
• occurrence – number of times the operation the counter represents has occurred
• value – may or may not have some value, but is typically an average of the values that were recorded when the counter was written to … P110-112

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476