Database consuming queries with higher CPU

Recently saw someone else about the database query CPU occupied high SQL statement (I SQL is not good), so query the information, record, easy to understand and apply.

First, put the statement here

SELECT TOP 10
--Average CPU time   Total_worker_time/execution_count as Avg_cpu_cost,  
   Plan_handle,   Execution_count,   (SELECT SUBSTRING (text, STATEMENT_START_OFFSET/2 + 1, case when      statement _end_offset = 1 Then         LEN (CONVERT (nvarchar (max), text)) * 2         ELSE statement_end_offset      End-statement_start _offset)/2) from   sys.dm_exec_sql_text (sql_handle)) as Query_textfrom sys.dm_exec_query_statsorder by [avg_cpu_ Cost] DESC

And then, to explain, my understanding of this piece of code

First, the statement is decomposed, the inner layer is:

First of all: Convert Text to a string, then get the length.

Then case is the meaning of the else end:

When statement_end_offset =-1, the output text length of * *, if not, the output statement_end_offset size, the final output will be subtracted from the statement_start_offset, the final result. /2, needless to say, we all know. The text is Dm_exec_sql_text, that is, the final SQL statement is stored in this variable.

Then there is the substring () function. It can be seen that the use of Substring and its meaning, the first parameter is an expression, and then the beginning of the interception of the position, and then the length, so it is not difficult to understand the above statement.

Now, that's the module. First, you need to know what sys.dm_exec_sql_text (sql_handle) is.

Official explanation: Returns the text of the SQL batch identified by the specified sql_handle. That is, the SQL statement that executes is returned.

Well, now it's time to say Sys.dm_exec_query_stats official explanation: Returns aggregate performance statistics in the cached query plan for SQL Server. Each query statement in the cache plan corresponds to a row in that view, and the lifetime of the row is associated with the plan itself. When you delete a plan from the cache, the corresponding row is also removed from the view. Note: To get from what we call Azure SQL Data Warehouse or parallel Data Warehouse, use the name Sys.dm_pdw_nodes_exec_query_stats. The parameters used above are Sql_handle,statement_start_offset, andstatement_end_offset are produced by them.

Column Name	Data type	Describe
Sql_handle	varbinary (64)	Represents a tag that contains a skin query or stored procedure for a query. SQL handles as well as statement_start_offset and statement end offsets can be used to retrieve the SQL text of a query by invoking the Sys.dm_exec_sql_text dynamic management function.
Statement_start_offset	Int	Indicates the starting position of the query described by the row in its batch query or persisted object text (in bytes, starting at 0).
Statement_end_offset	Int	Indicates the end position (in bytes, starting at 0) of the query described by the row in its batch query or persisted object text. the previous version is SQL Server 2014, and a value of-1 indicates the end of the batch . Trailing annotations are no longer included.
Total_worker_time	bigint	The total amount of CPU time that this plan has been executing since it was compiled (reported in subtle units, but only accurate to milliseconds).
Execution_count	bigint	The number of times the schedule has been executed since the last compilation.
Plan_handle	varbinary (64)	Represents the markup for the compiled schedule to which the query belongs. This value can be passed to the Sys.dm_exec_query_plan dynamic management function to get the query plan. When a natively compiled stored procedure queries a memory-optimized table, this entry is always 0x000.
Execution_count	Bingint	The number of times this plan has been executed since it was compiled.

Referring to the table above, we are not difficult to understand the initial statement.

However, for myself, there are still many problems:

The first one: why start needs/2+1
The second: Len () Gets the length of what is required
Third: The final result why to/2
Fourth: How the results of plan_handle are interpreted

Ask someone, get the answer plus my own understanding, here to record the answer to the fourth (the top three to be studied):

For the fourth question, read an article basic can understand:https://www.cnblogs.com/huangxincheng/p/4279870.html

Database consuming queries with higher CPU