SQL Server performance tuning and daily management maintenance notes

1. Import trace files, such as duration >5000ms, collected by SQL Server profile into the sample table analysis or with the query optimizer advisor

2. You can use the DMV dynamic management view to query analysis SQL Server performance, usage, such as querying the last 50 most time-consuming SQL.

　　

--The last SQL that consumes the most CPU:
SELECT TOP 20
total_worker_time/1000 as [total CPU time (ms)],
Execution_count [number of runs],
qs.total_worker_time/qs.execution_count/1000 as [average CPU time (ms)],
Last_execution_time as [last execution time],
max_worker_time/1000 as [Maximum execution Time (ms)],
SUBSTRING (Qt.text, QS.STATEMENT_START_OFFSET/2 + 1,
(case when qs.statement_end_offset =-1
Then Datalength (qt.text)
ELSE Qs.statement_end_offset
End-qs.statement_start_offset)/2 + 1) as [using CPU syntax],
Qt.text [Full syntax],
Qt.dbid,
dbname = db_name (qt.dbid),
Qt.objectid,
object_name (Qt.objectid, Qt.dbid) ObjectName
From Sys.dm_exec_query_stats Qs with (NOLOCK)
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt
WHERE Execution_count > 1
ORDER by Total_worker_time DESC;

-The last SQL that consumes the most CPU on average:
SELECT TOP 20
total_worker_time/1000 as [total CPU time (ms)],
Execution_count [number of runs],
qs.total_worker_time/qs.execution_count/1000 as [average CPU time (ms)],
Last_execution_time as [last execution time],
min_worker_time/1000 as [Minimum execution time (ms)],
max_worker_time/1000 as [Maximum execution Time (ms)],
SUBSTRING (Qt.text, QS.STATEMENT_START_OFFSET/2 + 1,
(case when qs.statement_end_offset =-1
Then Datalength (qt.text)
ELSE Qs.statement_end_offset
End-qs.statement_start_offset)/2 + 1) as [using CPU syntax],
Qt.text [Full syntax],
Qt.dbid,
dbname = db_name (qt.dbid),
Qt.objectid,
object_name (Qt.objectid, Qt.dbid) ObjectName
From Sys.dm_exec_query_stats Qs with (NOLOCK)
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt
WHERE Execution_count > 1
ORDER by (qs.total_worker_time/qs.execution_count/1000) DESC;

Find out 10 SQL for the longest execution (for SQL SERVER 2005 and later)

SELECT TOP 10
(total_elapsed_time/execution_count)/N ' Average time ms ',
total_elapsed_time/1000 N ' Total time spent in MS ',
total_worker_time/1000 N ' Total CPU time MS ',
Total_physical_reads N ' Total number of physical reads ',
Total_logical_reads/execution_count N ' Each logical read ',
Total_logical_reads N ' Logical reads total number of times ',
Total number of total_logical_writes N ' logical writes ',
Execution_count N ' execution times ',
Creation_time N ' statement compilation Time ',
Last_execution_time N ' Last Execution time ',
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 ',
Qp.query_plan
From Sys.dm_exec_query_stats as Qs
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) St
Cross APPLY sys.dm_exec_query_plan (qs.plan_handle) QP
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;

SQL Server performance tuning and daily management maintenance notes