数据库常规Top 10

来源：互联网发布：linux 用户管理编辑：程序博客网时间：2024/05/15 05:21

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

--CPU

SELECT TOP  10total_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))*2ELSE statement_end_offset END - statement_start_offset)/2)FROM sys.dm_exec_sql_text(sql_handle))AS query_textFROM sys.dm_exec_query_statsORDER 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))*2ELSE statement_end_offset END - statement_start_offset)/2)FROM sys.dm_exec_sql_text(sql_handle))AS query_textFROM sys.dm_exec_query_statsORDER BY (total_logical_reads+total_logical_writes) DESC

--Recompile

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

--Select

SELECT TOP  10total_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))*2ELSE statement_end_offset END - statement_start_offset)/2)FROM sys.dm_exec_sql_text(sql_handle))AS query_textFROM sys.dm_exec_query_statsORDER BY execution_count DESC

0 0