Optimize based on SQL execution duration

1) Clear the cache plan first, pay attention to the production environment use this statement with caution!

DBCC FREEPROCCACHE

2) re-execute the query, system use, etc.

3) Find the longest statement time, you may need to perform multiple times to determine the average time, the code is as follows:

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 ' logical read total number of times '

, Total_logical_writes N ' logical write total number of times '

, Execution_count N ' execution times '

, total_worker_time/1000 N ' Total CPU time MS '

, total_elapsed_time/1000 N ' total time spent 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 ' execute 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;

4) According to the 3rd step result, copy the maximum time statement, then execute, and call up the execution plan (CTRL+M)

View properties, output list, based on the longest percentage taken in the execution plan

5) Optimization According to the column of the output table, for example to build an index

This article is from the "Imagine the Sky" blog, please be sure to keep this source http://kinwar.blog.51cto.com/3723399/1548219

Optimize based on SQL execution duration