数据性能调校——查看最耗资源的各种SQL

从计划高速缓存中清除查询计划DBCC FREEPROCCACHE 清除缓存中的过程DBCC DROPCLEANBUFFERS清除内存中的数据SELECT DB_ID('你的数据库名')

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))*2ELSE statement_end_offset END -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle) where dbid = 6 -- koubei = 6 指定数据库ID)AS query_textFROM sys.dm_exec_query_statsORDER BY [avg_cpu_cost] DESC查询SQL改进版select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time,highest_cpu_queries.avg_cpu_cost,highest_cpu_queries.execution_count, q.dbid, q.objectid, q.number, q.encrypted, q.[text]from (select top 50 qs.plan_handle, qs.total_worker_time, qs.total_worker_time/execution_count AS avg_cpu_cost , qs.execution_count from sys.dm_exec_query_stats qs order by avg_cpu_cost desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q where dbid = 6 -- koubei = 6order by highest_cpu_queries.avg_cpu_cost desc

SELECT TOP 10 total_worker_time,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) where dbid = 6 -- koubei = 6)AS query_textFROM sys.dm_exec_query_statsORDER BY execution_count DESC改进版本：select highest_execution_count.plan_handle, highest_execution_count.total_worker_time, highest_execution_count.execution_count, q.dbid, q.objectid, q.number, q.encrypted, q.[text]from (select top 50 qs.plan_handle, qs.total_worker_time, qs.execution_count from sys.dm_exec_query_stats qs order by execution_count desc) as highest_execution_count cross apply sys.dm_exec_sql_text(plan_handle) as q where dbid = 6 -- koubei = 6order by highest_execution_count.execution_count desc

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))*2ELSE statement_end_offset END -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle) where dbid = 6 -- koubei = 6)AS query_textFROM sys.dm_exec_query_stats WHERE plan_generation_num>1 ORDER BY plan_generation_num DESC改进版：select highest_plan_count.plan_handle, highest_plan_count.total_worker_time, highest_plan_count.execution_count, highest_plan_count.plan_generation_num, q.dbid, q.objectid, q.number, q.encrypted, q.[text]from (select top 50 qs.plan_handle, qs.total_worker_time, qs.execution_count, qs.plan_generation_num from sys.dm_exec_query_stats qs WHERE plan_generation_num>1 order by plan_generation_num desc) as highest_plan_count cross apply sys.dm_exec_sql_text(plan_handle) as q WHERE dbid = 6 -- koubei = 6order by highest_plan_count.plan_generation_num desc

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) where dbid = 6 -- koubei = 6)AS query_text,plan_handleFROM sys.dm_exec_query_stats ORDER BY (total_logical_reads+total_logical_writes)desc改进版：select highest_reads_count.avg_logical_reads, highest_reads_count.avg_logical_writes, highest_reads_count.avg_phys_reads, highest_reads_count.execution_count, highest_reads_count.plan_handle, q.dbid, q.objectid, q.number, q.encrypted, q.[text]from (select top 50 (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, qs.execution_count,qs.plan_handle from sys.dm_exec_query_stats qs order by (total_logical_reads+total_logical_writes) desc) as highest_reads_count cross apply sys.dm_exec_sql_text(plan_handle) as q WHERE dbid = 6 -- koubei = 6order by (highest_reads_count.avg_logical_reads+highest_reads_count.avg_logical_writes) desc

SELECT t1.object_id,t2.user_seeks,t2.user_scans,t1.equality_columns,t1.inequality_columnsFROM sys.dm_db_missing_index_details AS t1,sys.dm_db_missing_index_group_stats AS t2,sys.dm_db_missing_index_groups AS t3WHERE database_id=6 --koubei库ID，查看某个库的ID，可以切换到那个库下面，然后SELECT DB_ID()AND object_id=OBJECT_ID('表名')AND t1.index_handle=t3.index_handleAND t2.group_handle=t3.index_group_handle改进版本：

SELECT mig.*,statement AS tableName,column_id,column_name,column_usage FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns(mid.index_handle)INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle=mid.index_handleWHERE database_id=6 AND object_id=OBJECT_ID('表名')ORDER BY mig.index_group_handle,mig.index_handle,column_id并发问题，查询阻塞和被阻塞的SQL。注意下面waitingsql和blockingsql两个字段。declare @lockinfo TABLE ( [waiting_time_ms] [bigint], [waiting_resource_type] [varchar](60) , [dbanme] [nvarchar](128), [objectname] [nvarchar](128), [waiting_request_lock_mode] [varchar](60) , [waiting_session_id] [int] , [waiting_loginame] [varchar](128) , [waiting_hostname] [varchar](128) , [waitingsql] [nvarchar](max), [blocking_session_id] [smallint], [blocking_loginame] [varchar](128) , [blocking_hostname] [varchar](128) , [blockingsql] [varchar](max), [createtime] [datetime])DECLARE @count intdeclare @sql nvarchar(max)set @sql = N''select@sql = @sql + N'union allselect '''+convert(nvarchar(100),database_id)+N''' as dbid,object_id,hobt_id from ['+name+N'].sys.partitions(nolock)'from sys.databases(nolock)where name not in ('model','tempdb') and state_desc = 'ONLINE'set @sql = substring(@sql,10,len(@sql))declare @objinfo table(dbid int,object_id int,hobt_id bigint)insert into @objinfo exec(@sql)INSERT INTO @lockinfo(waiting_time_ms,waiting_resource_type,dbanme,objectname,waiting_request_lock_mode,waiting_session_id,waiting_loginame,waiting_hostname,waitingsql,blocking_session_id,blocking_loginame,blocking_hostname,blockingsql)SELECT DISTINCT t2.wait_duration_ms waiting_time_ms, t1.resource_type waiting_resource_type, DB_NAME(t1.resource_database_id) dbanme, CASE t1.resource_type WHEN 'OBJECT' THEN OBJECT_NAME(t1.resource_associated_entity_id, t1.resource_database_id) WHEN 'DATABASE' THEN 'DATABASE' ELSE (SELECT OBJECT_NAME(object_id, t1.resource_database_id) FROM @objinfo WHERE hobt_id = t1.resource_associated_entity_id and dbid = t1.resource_database_id) END AS ObjectName, t1.request_mode waiting_request_lock_mode, t1.request_session_id waiting_session_id, s1.login_name waiting_loginame, s1.host_name+'-'+r3.client_net_address waiting_hostname, CASE WHEN st1.objectid IS NULL THEN st1.text ELSE OBJECT_NAME(st1.objectid,st1.dbid) END waitingsql, t2.blocking_session_id, s2.login_name blocking_loginame, s2.host_name+'-'+r2.client_net_address blocking_hostname, CASE WHEN st2.objectid IS NULL THEN st2.text ELSE OBJECT_NAME(st2.objectid,st2.dbid) END blockingsql FROM sys.dm_tran_locks AS t1 with(nolock) INNER JOIN sys.dm_os_waiting_tasks AS t2 with(nolock) ON t1.lock_owner_address = t2.resource_address INNER JOIN sys.dm_exec_requests r1 with(nolock) ON t1.request_session_id=r1.session_id INNER JOIN sys.dm_exec_sessions s1 with(nolock) ON s1.session_id=r1.session_id CROSS APPLY sys.dm_exec_sql_text(r1.sql_handle) st1 INNER JOIN sys.dm_exec_connections r2 with(nolock) ON t2.blocking_session_id=r2.session_id INNER JOIN sys.dm_exec_sessions s2 with(nolock) ON s2.session_id=r2.session_id CROSS APPLY sys.dm_exec_sql_text(r2.most_recent_sql_handle) st2 left JOIN sys.dm_exec_connections r3 with(nolock) ON t1.request_session_id=r3.session_id ORDER BY t2.wait_duration_ms DESCselect * from @lockinfo