数据库性能分析语句

CPU
--执行最慢的50条语句
select top 50
sum(qs.total_worker_time) as TotalCPUTime,
sum(qs.execution_count) as TotalExecutionCount,
count(*) as NumberOfStatements,qs.plan_handle
from sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

--Find the top 25 recompiled stored procedures
select top 25
SQLText.text,sql_handle,plan_generation_num,execution_count,dbid,objectid
from sys.dm_exec_query_stats a
Cross apply sys.dm_exec_sql_text(sql_handle) as SQLText
where plan_generation_num>1
order by plan_generation_num desc

--find the time used for query optimization
select * from sys.dm_exec_query_optimizer_info

--look for cpu intensive operators through
select * from sys.dm_exec_cached_plans

--Find query plans that may run in parallel
select p.*, q.*,cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

--Parallel query use more CPU time than the elapsed time
Select EventClass, StmtText
from ::fn_trace_gettable('c:/temp/high_cpu_trace.trc', default)
Where EventClassin (10, 12)
--RPC:Completed, SQL:BatchCompleted
And CPU > Duration/1000
--CPU is in milliseconds, Duration in microseconds

Memory

DBCC memorystatus

I/O
--Find out queries generating most IO
select top 5 (total_logical_reads/execution_count) as
Avg_logical_reads,
(total_logical_writes/execution_count) as
Avg_logical_writes,
(total_physical_reads/execution_count) as Avg_physical_reads,
Execution_count, statement_start_offset, sql_handle,
plan_handle
from sys.dm_exec_query_stats
order by (total_logical_reads+ total_logical_writes)/execution_count
Desc

-- Pending IO request
select database_id, file_id, io_stall,io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

--Physical IO wait when reading and writing buffer pages
select wait_type, waiting_tasks_count, wait_time_ms
from sys.dm_os_wait_stats
where wait_type like '%PAGEIOLATCH%'
order by wait_type