SQL Server Performance Analysis-SQL Execution count and logic count

During project optimization, I want to analyze SQL server system performance through the database layer. I checked the online code and modified the title and dmvs code, the following code analyzes the SQL statements that are busy after the system runs for a period of time. For reference.

Alternative use:

When analyzing a reconciliation function, I checked the system code for half a day and did not write comments. Finally, I used the following tips to share with you:

When measuring the function, run the following command to clear the SQL Server cache:

 

dbcc freeProcCache

 

After clicking a button and executing the following statement, you can know what SQL statements and how many slow statements the system runs.

Select creation_time n 'Statement compilation time'
, Last_execution_time n 'last execution time'
, Total_physical_reads n 'Total number of physical reads'
, Total_logical_reads/execution_count n 'number of logical reads per time'
, Total_logical_reads n 'Total number of logical reads'
, Total_logical_writes n' total number of logical writes'
, Execution_count n 'execution times'
, Total_worker_time/1000 n' total CPU time Ms'
, Total_elapsed_time/1000 n' total consumed time Ms'
, (Total_elapsed_time/execution_count)/1000 N 'average time Ms'
, Substring (St. Text, (Qs. statement_start_offset/2) + 1,
(Case statement_end_offset
When-1 then datalength (St. Text)
Else Qs. statement_end_offset end
-Qs. statement_start_offset)/2) + 1) n'execution statement'
From SYS. dm_exec_query_stats as Qs
Cross apply SYS. dm_exec_ SQL _text (Qs. SQL _handle) ST
Where substring (St. Text, (Qs. statement_start_offset/2) + 1,
(Case statement_end_offset
When-1 then datalength (St. Text)
Else Qs. statement_end_offset end
-Qs. statement_start_offset)/2) + 1) Not like '% fetch %'
Order by total_elapsed_time/execution_count DESC;