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 the number of times the system runs. What are the main slow statements;
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
;