查看数据库基本运行信息时 : 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