[转] sql server 性能优化精华（系统工具级别sql）

--SQL Server常用的性能计数器
SQLServer:Plan Cache ----Cache Hit Ratio
SQLserver:Buffer Manager----Buffer Cache Hit Ratio
SQLServer:Locks---- Lock Waits/sec
SQLServer:Access Methods----Full Scans/sec
SQLServer:Access Methods----Lazy Writes/sec


--正在执行中的SQL语句
SELECT
es.session_id,
database_name = DB_NAME(database_id),
er.cpu_time,
er.reads,
er.writes,
er.logical_reads,
login_name,
er.status,
blocking_session_id,
wait_type,
individual_query = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2),
parent_query = qt.text,
program_name,
host_name,
nt_domain,
start_time
FROM
sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE
es.session_id > 50
AND es.session_Id NOT IN (@@SPID)
ORDER BY
1, 2


dbcc inputbuffer（id）

--执行次数最多的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time,
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
execution_count DESC

--执行时间最长的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time,
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
max_elapsed_time DESC


--消耗CPU最多的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time,
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
total_worker_time DESC

--消耗IO读最多的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time,
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
total_logical_reads DESC

--消耗IO写最多的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time,
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
total_logical_writes DESC


--查看缺失索引
SELECT * FROM sys.dm_db_missing_index_details ORDER BY object_id
SELECT * FROM sys.dm_db_missing_index_details where object_id = OBJECT_ID('t_market_bankproduct')

--查询sql执行时间
SET STATISTICS IO Off
SET STATISTICS TIME Off