Search for and optimize SQL statements using SYS. dm_exec_query_stats

Today, I am reading the new features of SQL Server 2012. when I see one, I find sys. the dm_exec_query_stats System View is upgraded. Because this attempt is always in use and is quite useful, it can be said that it is the calculator used to find and optimize SQL statements. Therefore, we have made a special record today.

 

The definition of SYS. dm_exec_query_stats view on msdn:Returns the aggregate performance statistics of the cache query plan in SQL Server 2012.Each query statement in the cache plan corresponds to a row in this view, and the row survival is associated with the plan itself.When a plan is deleted from the cache, the corresponding row is also deleted from the view.

To put it bluntly, this view stores the details of all the current execution plans, such as the total CPU usage of an execution plan. This view records the number of compilations, the total number of CPU resources used, and the number of executions in detail. Therefore, it can be said that the database server CPU provisioner is optimized.

 

This attempt is dynamic, so it must be always accurate. Maybe an execution plan has been re-compiled at the query time to obtain the deviation information. In addition, for sys. dm_exec_query_stats consumes the most resources and does not necessarily have performance problems. You must observe the number of executions and IO reads and writes at the same time. If execution is too frequent, considerProgramCache is added. This system cannot be used for emergency optimization, but it must be used as an important reference for routine optimization.

 

After talking about this for a long time, the following shows the optimized SQL:

Select s2.dbid, (select top 1 substring (s2.text, statement_start_offset/2 + 1, (case when statement_end_offset =-1 then (LEN (convert (nvarchar (max), s2.text )) * 2) else begin end)-statement_start_offset)/2 + 1) as SQL _statement, execution_count, plan_generation_num, metrics, min_worker_time, max_worker_time, latency, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writesfrom sys. dm_exec_query_stats as S1 cross apply sys. dm_exec_ SQL _text (SQL _handle) as S2 where s2.objectid is null order by s1.total _ worker_time DESC
 

For more information about the column meanings, see the end of this document.

 

next let's talk about the SQL Server 2012 pair sys. dm_exec_query_stats: four columns are added to help eliminate problems with long-running queries. you can use the total_rows, min_rows, max_rows, and last_rows aggregate row count columns to separate the problematic queries (which may be missing A large number of rows are returned.

The specific meaning is not difficult to see from the name. After my own trial, I found that this improvement is not very practical for some execution plans. Why, because the execution plan may accept parameters, the number of rows is closely related to the parameters. Therefore, this improvement is useless for execution plans with the number of returned rows and parameters closely related, it also serves as a reference.

 

SYS. dm_exec_query_stats Description: http://msdn.microsoft.com/zh-cn/library/ms189741 (V = SQL .110). aspx