数据库常规Top 10

查看数据库基本运行信息时 ： CPU IO Recompile Select 都是我们要查看的要点。可以利用一下语句进行查看

--CPU

SELECT TOP  10
	total_worker_time AS TotalTime,
	total_worker_time/execution_count AS avg_cpu_cost,
	plan_handle,
	execution_count,
	(SELECT SUBSTRING(text,statement_start_offset/2 + 1,
	(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),text))*2
	ELSE statement_end_offset END - statement_start_offset)/2)
	FROM sys.dm_exec_sql_text(sql_handle))AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

--IO

SELECT TOP 10
	(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_phys_reads,
	execution_count,
	(SELECT SUBSTRING(text,statement_start_offset/2 + 1,
	(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),text))*2
	ELSE statement_end_offset END - statement_start_offset)/2)
	FROM sys.dm_exec_sql_text(sql_handle))AS query_text
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads+total_logical_writes) DESC

--Recompile

SELECT TOP 10
	plan_generation_num,execution_count,
	(SELECT SUBSTRING(text,statement_start_offset/2 + 1,
	(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),text))*2
	ELSE statement_end_offset END - statement_start_offset)/2)
	FROM sys.dm_exec_sql_text(sql_handle))AS query_text
FROM sys.dm_exec_query_stats
WHERE plan_generation_num >1
ORDER BY plan_generation_num DESC 

--Select

SELECT TOP  10
	total_worker_time/execution_count AS avg_cpu_cost,plan_handle,
	execution_count,
	(SELECT SUBSTRING(text,statement_start_offset/2 + 1,
	(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),text))*2
	ELSE statement_end_offset END - statement_start_offset)/2)
	FROM sys.dm_exec_sql_text(sql_handle))AS query_text
FROM sys.dm_exec_query_stats
ORDER BY execution_count DESC