【基本优化实践】【1.0】查看最耗资源的各种SQL

【1】查询缓存找出最耗资源的SQL

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

（1.1）CPU消耗 前10

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) where dbid = 6 -- koubei = 6 指定数据库ID
)AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

-- 改进版

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 = 6
order by highest_cpu_queries.avg_cpu_cost desc

（1.2）执行次数最多 前10

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))*2
ELSE statement_end_offset END -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle) where dbid = 6 -- koubei = 6
)AS query_text
FROM sys.dm_exec_query_stats
ORDER 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 = 6
order by highest_execution_count.execution_count desc

（1.3）编译次数最多 前10

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) where dbid = 6 -- koubei = 6
)AS query_text
FROM 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 = 6
order by highest_plan_count.plan_generation_num desc

（1.4）IO使用最多 前10

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) where dbid = 6 -- koubei = 6
)AS query_text,plan_handle
FROM 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 = 6
order by (highest_reads_count.avg_logical_reads+highest_reads_count.avg_logical_writes) desc

（1.5）索引缺失

SELECT t1.object_id,t2.user_seeks,t2.user_scans,t1.equality_columns,t1.inequality_columns
FROM sys.dm_db_missing_index_details AS t1,
sys.dm_db_missing_index_group_stats AS t2,
sys.dm_db_missing_index_groups AS t3
WHERE database_id=6 --koubei库ID，查看某个库的ID，可以切换到那个库下面，然后SELECT DB_ID()
AND object_id=OBJECT_ID('表名')
AND t1.index_handle=t3.index_handle
AND 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_handle
WHERE database_id=6 AND object_id=OBJECT_ID('表名')
ORDER BY mig.index_group_handle,mig.index_handle,column_id

【2】并发问题

（2.1）查询阻塞和被阻塞SQL 

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 int
declare @sql nvarchar(max)
set @sql = N''
select
@sql = @sql + N'union all
select '''+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 DESC

select * from @lockinfo