Initial SQL Server performance issues (4/4): List the most resource-intensive sessions

Original: Initial SQL Server performance issue (4/4): List the most resource-consuming sessions

In the last 3 articles, we discussed a list of different queries that reflect the current state of the server.

• Initial SQL Server performance issues (1/4): Server overview
• Initial SQL Server performance issues (2/4): List the sessions waiting for resources
• Initial SQL Server performance issues (3/4): List blocked sessions

In this article we look at the list of execution states from the plan cache.

1 /*****************************************************************************************2 List Heavy query based on Cpu/io. Change the ORDER BY clause appropriately3 ******************************************************************************************/4 SELECT TOP  -5 db_name(qt.dbid) asDatabaseName6,DATEDIFF(Mi,creation_time,GETDATE()) as [Age of the Plan (Minutes)]7, Last_execution_time as [Last execution time]8, Qs.execution_count as [Total Execution Count]9,CAST((Qs.total_elapsed_time)/ 1000000.0  as DECIMAL( -,2))[Total Elapsed time (s)]Ten,CAST((Qs.total_elapsed_time)/ 1000000.0/Qs.execution_count as DECIMAL( -,2)) as [Average Execution Time (s)] One,CAST((Qs.total_worker_time)/ 1000000.0  as DECIMAL( -,2)) as [Total CPU time (s)] A,CAST(Qs.total_worker_time* 100.0 /Qs.total_elapsed_time as DECIMAL( -,2)) as [% CPU] -,CAST((Qs.total_elapsed_time-Qs.total_worker_time)* 100.0 /Qs.total_elapsed_time as DECIMAL( -,2)) as [% Waiting] -,CAST((Qs.total_worker_time)/ 1000000.0/Qs.execution_count as DECIMAL( -,2)) as [CPU time average (s)] the,CAST((qs.total_physical_reads)/Qs.execution_count as DECIMAL( -,2)) as [AVG Physical Read] -,CAST((qs.total_logical_reads)/Qs.execution_count as DECIMAL( -,2)) as [AVG Logical Reads] -,CAST((qs.total_logical_writes)/Qs.execution_count as DECIMAL( -,2)) as [AVG Logical writes] - , Max_physical_reads + , Max_logical_reads - , Max_logical_writes +,SUBSTRING(QT.TEXT, (Qs.statement_start_offset/2)+ 1,(( Case  whenQs.statement_end_offset= -1 A     Then LEN(CONVERT(NVARCHAR(MAX), Qt.TEXT))* 2 at    ELSEQs.statement_end_offset -    END -Qs.statement_start_offset)/2)+ 1) as [individual Query] -Qt.TEXT  as [Batch Statement] - , Qp.query_plan -  fromSYS. Dm_exec_query_stats QS -  CrossAPPLY SYS. Dm_exec_sql_text (Qs.sql_handle) asQT in  CrossAPPLY SYS. Dm_exec_query_plan (qs.plan_handle) QP - WHEREQs.total_elapsed_time> 0 to ORDER  by  + [Total CPU time (s)]  - --[AVG physical Read] the --[Avg Logical Reads] * --[Avg Logical writes] $ --[Total Elapsed time (s)]Panax Notoginseng --[Total execution Count] - DESC 

each column of output results is described below:

• DatabaseName the database environment (database name) of the execution plan.
• The age of the Plan (Minutes) program caches the planned lifetime in minutes.
• Last execution the date and time of this plan's execution.
• Total execution count number of executions since the last compilation, the total number of executions (since the last compilation) in the execution plan lifetime [age of the Plan (Minutes)].
• Total Elapsed time (s) after the total number of executions of this plan, in seconds.
• Average execution Time (s) the average duration of each execution of the plan, in seconds.
• Total CPU time (s) after the total execution of this plan has been performed, in seconds.
• The% CPU is more CPU-intensive than total Elapsed time (s).
• % waiting is waiting for the resource to take longer than the total Elapsed time (s).
• The average CPU time, in seconds, that is executed per CPU for time average (s).
• Avg Physical read average physical readings per execution.
• Avg Logical Reads The average logical reading of each execution.
• Avg Logical writes the average number of logical writes per execution.
• Max_physical_reads a new maximum physical reading every time the execution is performed.
• Max_logical_reads a new maximum logical reading each time the execution is performed.
• Max_logical_writes a new maximum number of logical writes per execution.
• Individual some of the information in the query batch statement.
• Batch Statement batches query.
• Query_plan the execution plan in XML format, we can look at the diagram execution plan after clicking.

In general, we can analyze the specific statement information of the first 5 records (by modifying the collation). In most cases, we will find that problems occur in temporary table abuses, distinct statements, cursors, inappropriate table join conditions, inappropriate indexes, and so on. The other recurring problem is that the stored procedure has a large number of calls to the database (both CPU consumption and execution time are small). This need and developer feedback, modify the implementation of the specific way. If the data is often called, you can use the caching method in your program to avoid multiple interactions with the server. Some calls to the database simply check for changes to the resulting data. Some calls to the database are checked for new records in the database table and must be processed immediately. To do this, the program queries the table multiple times within 1 seconds to find the unhandled record. This can be resolved by inserting data from the program's asynchronous calls to and from the table, or by using the SqlDependency in the. NET Framework. (SqlDependency provides the ability to automatically update the data (or cache) by SqlDependency triggering an onchange event to notify the application when the data in the monitored database changes. ）

Initial SQL Server performance issues (4/4): List the most resource-intensive sessions