SQLServer 维护脚本分享(06)CPU
来源:互联网 发布:qq防沉迷解除软件 编辑:程序博客网 时间:2024/06/06 09:59
--CPU相关视图SELECT * FROM sys.dm_os_sys_infoSELECT * FROM sys.dm_exec_sessionsSELECT * FROM sys.sysprocessesSELECT * FROM sys.dm_os_tasksSELECT * FROM sys.dm_os_workersSELECT * FROM sys.dm_os_threadsSELECT * FROM sys.dm_os_schedulersSELECT * FROM sys.dm_os_memory_objectsSELECT * FROM sys.dm_os_nodesSELECT * FROM sys.dm_os_memory_nodesexec sp_configure 'max degree of parallelism'--系统默认并行度exec sp_configure 'cost threshold for parallelism' --并发阈值exec sp_configure 'max worker threads'--系统最大工作线程数exec sp_configure 'affinity mask' --CPU关联--数据库系统 cpu,线程 数量select max_workers_count,scheduler_count,cpu_count,hyperthread_ratio,(hyperthread_ratio/cpu_count) AS physical_cpu_count,(max_workers_count/scheduler_count) AS workers_per_scheduler_limitfrom sys.dm_os_sys_info--执行的线程所遇到的所有等待的相关信息SELECT TOP 10 wait_type,waiting_tasks_count,signal_wait_time_ms FROM sys.dm_os_wait_stats ORDER BY signal_wait_time_ms DESC--正在等待某些资源的任务的等待队列的信息SELECT TOP 10 wait_type,wait_duration_ms,session_id,blocking_session_id FROM sys.dm_os_waiting_tasks ORDER BY wait_duration_ms DESC--CPU或调度器当前分配的工作情况SELECT scheduler_id,cpu_id,status,is_idle,current_tasks_count AS 当前任务数--在等待或运行的任务,runnable_tasks_count AS 等待调度线程数--已分配任务并且正在可运行队列中,current_workers_count AS 当前线程数--相关或未分配任何任务的工作线程,active_workers_count AS 活动线程数--在运行、可运行或挂起,work_queue_count AS 挂起任务数--等待工作线程执行FROM sys.dm_os_schedulersWHERE scheduler_id < 255--当前线程数select COUNT(*) as 当前线程数 from sys.dm_os_workers--非SQL server create的threadsselect * from sys.dm_os_threads where started_by_sqlservr=0 --即scheduler_id > 255--有task 等待worker去执行select * from sys.dm_os_tasks where task_state='PENDING'--计数器select * from sys.dm_os_performance_counters where object_name='SQLServer:SQL Statistics'select * from sys.dm_os_performance_counters where object_name='SQLServer:Plan Cache'------------------------------------------------------------------------------------------------------------------------------------------------------------1. 实例累积的信号(线程/CPU)等待比例是否严重SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats WITH (NOLOCK) OPTION (RECOMPILE); --2. SqlServer各等待类型的线程等待信息SELECT TOP 20 wait_type,waiting_tasks_count ,wait_time_ms,signal_wait_time_ms ,wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,CONVERT(NUMERIC(14,2),100.0 * wait_time_ms /SUM (wait_time_ms ) OVER( )) AS percent_total_waits ,CONVERT(NUMERIC(14,2),100.0 * signal_wait_time_ms /SUM (signal_wait_time_ms) OVER( )) AS percent_total_signal_waits ,CONVERT(NUMERIC(14,2),100.0 * ( wait_time_ms - signal_wait_time_ms )/SUM (wait_time_ms ) OVER( )) AS percent_total_resource_waits FROM sys .dm_os_wait_statsWHERE wait_time_ms > 0ORDER BY percent_total_signal_waits DESC--3. 闩锁(latch)等待的信息select top 20 latch_class,waiting_requests_count,wait_time_ms,max_wait_time_msfrom sys.dm_os_latch_statsorder by wait_time_ms desc--使用最多处理器时间的用户数据库;WITH DB_CPU_Stats AS (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms] FROM sys.dm_exec_query_stats AS qs CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS F_DB GROUP BY DatabaseID) SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num], DatabaseName, [CPU_Time_Ms], CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms])OVER() * 100.0 AS DECIMAL(5,2)) AS [CPUPercent] FROM DB_CPU_Stats WHERE DatabaseID > 4 -- system databases AND DatabaseID <> 32767 -- ResourceDB ORDER BY row_num OPTION (RECOMPILE);--缓存中最耗CPU的语句select total_cpu_time,total_execution_count,number_of_statements,[text] from (select top 20 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 total_cpu_time desc) eqs cross apply sys.dm_exec_sql_text(eqs.plan_handle) as estorder by total_cpu_time desc/*【ask 让出scheduler :worker yielding】1. worker读数据页超过4ms2. 64k结果集排序3. compile或recompile(常有)4. 客户端不能及时取走结果集5. batch 的每个操作完整*/--当前正在执行的语句SELECT der.[session_id],der.[blocking_session_id],sp.lastwaittype,sp.hostname,sp.program_name,sp.loginame,der.[start_time] AS '开始时间',der.[status] AS '状态',der.[command] AS '命令',dest.[text] AS 'sql语句', DB_NAME(der.[database_id]) AS '数据库名',der.[wait_type] AS '等待资源类型',der.[wait_time] AS '等待时间',der.[wait_resource] AS '等待的资源',der.[reads] AS '物理读次数',der.[writes] AS '写次数',der.[logical_reads] AS '逻辑读次数',der.[row_count] AS '返回结果行数'FROM sys.[dm_exec_requests] AS der INNER JOIN master.dbo.sysprocesses AS sp on der.session_id=sp.spidCROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest WHERE [session_id]>50 AND session_id<>@@SPID AND DB_NAME(der.[database_id])='platform' ORDER BY [cpu_time] DESC--实例级最大的瓶颈WITH Waits AS ( SELECT wait_type , wait_time_ms / 1000. AS wait_time_s , 100.* wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) )SELECT W1.wait_type , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s , CAST(W1.pct AS DECIMAL(12, 2)) AS pct ,CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn , W1.wait_type , W1.wait_time_s , W1.pct HAVING SUM(W2.pct) - W1.pct < 95 ; -- percentage threshold
0 0
- SQLServer 维护脚本分享(06)CPU
- SQLServer 维护脚本分享(07)IO
- SQLServer 维护脚本分享(10)索引
- SQLServer 维护脚本分享(03)数据库中记录CPU最近N分钟内的使用情况
- SQLServer 维护脚本分享(05)内存(Memory)
- SQLServer 维护脚本分享(08)临时数据库(tempdb)
- SQLServer 维护脚本分享(09)相关文件读取
- SQLServer 维护脚本分享(01)谁对数据库对象进行了DDL操作?
- SQLServer 维护脚本分享(02)数据库文件增长及收缩情况
- SQLServer 维护脚本分享(04)服务器角色和数据库角色相关操作
- SQLServer 维护脚本分享(11)部分DBCC及系统存储过程
- SQL Server 维护脚本分享(14)堵塞SQL跟踪
- SQL Server 维护脚本分享(12)查看数据库空间分配情况(准确)
- SQL Server 维护脚本分享(13)数据库内部日常巡检
- SqlServer 审核(脚本示例)
- sqlserver维护操作相关
- SQLSERVER维护常用命令
- sqlserver常用语句-维护
- POJ 3735 Training Little Cats
- 8天学通MongoDB——第二天 细说增删查改
- 超级牛B编码王(二):Sublime2之Zencoding快速上手
- Android面试题总结加强再加强版(三)
- 今年学习的重点
- SQLServer 维护脚本分享(06)CPU
- TCP/IP四层协议栈
- TCP的交互数据流
- 解决键盘遮挡输入框问题
- 单例模式
- bzoj1934【shoi2007】Vote善意的投票
- 【Android】《第一行代码—Android》第四章总结
- 笔试题目总结之二——常用数据结构与算法
- do{}while(0);详解