SQL Server Performance Analysis--execution of SQL times and number of logical times

Currently in a project optimization, want to analyze SQL Server system performance through the database layer, check the online code, modify the title and DMVs code, the following code can be used to analyze the system after a period of time, those statements are system busy SQL statement. As a reference.

Alternative use:

Once in the analysis of a reconciliation function, look at the system code, read half a day, write too irregular, and do not write comments, can not understand. Finally, with the following tips, and share with you:

When measuring the function, the following command clears the SQL Server cache:

DBCC Freeproccache

After clicking on a button and executing the following statement, you can know what SQL and how many times the system is running, and the main slow statements are those.

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'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, (case statement_end_offset when-1Then datalength (st.text) ELSE Qs.statement_end_offset End-Qs.statement_start_offset)/2) +1) N'EXECUTE Statement'From sys.dm_exec_query_stats as Qscross APPLY sys.dm_exec_sql_text (qs.sql_handle) StwhereSUBSTRING (St.text, (qs.statement_start_offset/2) +1, (case statement_end_offset when-1Then 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;

SQL Server Performance Analysis--execution of SQL times and number of logical times