SQL Server Performance Analysis-SQL Execution count and logic count

At present, I want to analyze the SQL server system performance through the database layer during project optimization.Code, Changed the title and dmvs code. The following code can be used to analyze the SQL statements that are busy in the system 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 ' Number of executions '
, Total_worker_time / 1000 N ' Total CPU time (MS) used '
, Total_elapsed_time / 1000 N ' Total time (MS) '
, (Total_elapsed_time / Execution_count) / 1000 N ' Average time in 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 ;