In SQL Server, what should you do if you want to know what statements are not performing efficiently? Here's how you can see the statements in SQL Server that are not performing very efficiently for your reference.
In the measurement function, the following command clears the SQL Server cache first
DBCC Freeproccache
Click on a button, after execution, then execute the following statement, you can know what the system is running SQL and how many times, its main slow statements are those;
SELECTCreation_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'each logical read Count', 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/ +N'total CPU time used by Ms', Total_elapsed_time/ +N'Total time spent Ms', (Total_elapsed_time/Execution_count)/ +N'Average Time Ms' ,SUBSTRING(St.text, (Qs.statement_start_offset/2)+ 1, (( CaseStatement_end_offset when -1 Then datalength(St.text) ELSEQs.statement_end_offsetEND -Qs.statement_start_offset)/2)+ 1) N'EXECUTE Statement' fromSys.dm_exec_query_stats asQS CrossAPPLY sys.dm_exec_sql_text (qs.sql_handle) Stwhere SUBSTRING(St.text, (Qs.statement_start_offset/2)+ 1, (( CaseStatement_end_offset when -1 Then datalength(St.text) ELSEQs.statement_end_offsetEND -Qs.statement_start_offset)/2)+ 1) not like '%fetch%'ORDER byTotal_elapsed_time/Execution_countDESC;
How to view statements with low execution efficiency in SQL Server