-- 任务管理器检查高CPU是否由SQL SERVER引起，是否有批处理/备份任务执行

-- 检查当前高CPU进程及SQL：
SELECT st.text, qp.query_plan, rq.*
FROM sys.dm_exec_requests RQ CROSS APPLY sys.dm_exec_sql_text(rq.sql_handle) as st
CROSS APPLY sys.dm_exec_query_plan(rq.plan_handle) as qp
-- AND DB_NAME(rq.[database_id])='xdb' 
order by RQ.CPU_time desc

-- 查看当前SQL等待信息：
SELECT TOP 10
 [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句', 
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 INNER JOIN [sys].[dm_os_wait_stats] AS dows 
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

-- 检查进程数：
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

-- 检查指定应用库的进程：
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 
--AND DB_NAME([dbid])='xdb'

-- 检查系统进程是否异常：
select * from sys.sysprocesses where spid<51 order by cpu desc

-- 查找最耗CPU的SQL
 SELECT TOP 10
    total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
    execution_count,sql_handle,
    (select text
    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
 FROM sys.dm_exec_query_stats
 ORDER BY [avg_cpu_cost] DESC

-- 查找最耗CPU的SQL plan_handle
  select top 50
         SUM(qs.total_worker_time) as total_cpu_time,
         SUM(qs.execution_count) as total_execution_count,
         COUNT(*) as number_of_statements,
         qs.plan_handle
   from sys.dm_exec_query_stats qs
   group by qs.plan_handle
   order by SUM(qs.total_worker_time) desc


-- 查看SQL执行计划
set statistics profile on

-- 查看是否有并行执行SQL
select r.session_id,r.request_id,MAX(ISNULL(exec_context_id,0)) as number_of_workers,
r.sql_handle,r.statement_start_offset,r.statement_end_offset,r.plan_handle
 from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id=t.scheduler_id
join sys.dm_exec_sessions s on r.session_id=s.security_id
where s.is_user_process = 0x1
group by r.session_id,r.request_id,r.sql_handle,r.plan_handle,r.statement_start_offset,r.statement_end_offset
having MAX(ISNULL(exec_context_id,0))>0


-----------------------------------------
-- 解决方法：

-- 查看过期统计信息：
USE [xdb]
DECLARE
@day_before int = 30;

SELECT
Object_name = OBJECT_NAME(object_id)
,Stats_Name = [name]
,Stats_Last_Updated = STATS_DATE([object_id], [stats_id])
FROM sys.stats WITH(NOLOCK)
WHERE STATS_DATE([object_id], [stats_id]) <= DATEADD(day, -@day_before, getdate())
order by Stats_Last_Updated desc;

-- 更新统计信息使用UPDATE STATISTICS语句，查看统计信息
select * from run.sys.stats where object_id=object_id('run.dbo.T1')

-- 更新表统计信息
update statistics test.dbo.T1 with fullscan

-- 更新数据库统计信息
exec sp_updatestats
-- 启动数据库统计信息自动更新
ALTER DATABASE [xdb] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT

-- 固定执行计划使用plan_handle
execute sp_create_plan_guide_from_handle 
@name =N'Sample_PG1',
@plan_handle = @plan_handle,
@statement_start_offset =NULL;
go 

-- 创建缺失索引：检查是否有缺失索引：
SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

-- 检查是否有过度编译：
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc



-----------------------------------------------------------------
-- 历史高CPU查询：
SELECT
    HIGHEST_CPU_QUERIES.PLAN_HANDLE,
    HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME,
    Q.DBID,
    Q.OBJECTID,
    Q.NUMBER,
    Q.ENCRYPTED,
    Q.[TEXT],max_WORKER_TIME,last_execution_time
FROM
    (SELECT TOP 50
        QS.PLAN_HANDLE,
        QS.TOTAL_WORKER_TIME,
                QS.execution_count,max_WORKER_TIME,last_execution_time
    FROM
        SYS.DM_EXEC_QUERY_STATS QS where datediff(d, last_execution_time,getdate())<1.5
    ORDER BY QS.TOTAL_WORKER_TIME DESC) AS HIGHEST_CPU_QUERIES
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS Q
ORDER BY HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME DESC

-- Performance Monitor
-- 游标使用：
SQL Server:Cursor Manager By Type- Cursor/sec
-- CPU time:
Processor:%Processor Time
-- 重新编译次数应该很低：
SQL Statistics：Batch Requests/sec
SQL Statistics: SQL Compiulation/sec
SQL Statistics: SQL Recompilations/sec