SQL Server2008中CPU性能监控

CPU性能诊断

CPU架构

　　目前的主流企业服务器基本可以分为三类：SMP(Symmetric Multi Processing，对称多处理架构)，NUMA(Non-Uniform Memory Access，非一致存储访问架构)和MPP(Massive Parallel Processing，海量并行处理架构)

　　SMP(Symmetric Multi Processing)

　　SMP是非常常见的一种架构。在SMP模式下，多个处理器均对称的连接在系统内存上，所有处理器都以平等的代价访问系统内存。它的优点是对内存的访问是平等、一致的;缺点是因为大家都是一致的，在传统的 SMP 系统中，所有处理器都共享系统总线，因此当处理器的数目增多时，系统总线的竞争冲突迅速加大，系统总线成为了性能瓶颈，所以目前 SMP 系统的处理器数目一般只有数十个，可扩展性受到很大限制。

▲

MPP (Massive Parallel Processing)

　　MPP则 是逻辑上将整个系统划分为多个节点，每个节点的处理器只可以访问本身的本地资源，是完全无共享的架构。节点之间的数据交换需要软件实施。它的优点是可扩展 性非常好;缺点是彼此数据交换困难，需要控制软件的大量工作来实现通讯以及任务的分配、调度，对于一般的企业应用而言过于复杂，效率不高。

▲

NUMA(Non-Uniform Memory Access)

　　NUMA架构则在某种意义上是综合了SMP和MPP的特点：逻辑上整个系统也是分为多个节点，每个节点可以访问本地内存资源，也可以访问远程内存资源，但访问本地内存资源远远快于远程内存资源。它的优点是兼顾了SMP和MPP的特点, 易于管理，可扩充性好;缺点是访问远程内存资源的所需时间非常的大。

　　不过目前世界上排名靠前的大型机都是基于MPP架构的，AMD最早支持NUMA架构，而尽管Intel一直是SMP的支持者，但从Xeon处理器和酷睿i7开始也逐步走向了NUMA架构之路。

　　SQL Server从2000版本的SP4起开始增加了对NUMA的支持，从2005版本起得到了全面的采用;SQL Server2008 R2版本起夜增加了对MPP架构的支持;不过从目前SQL Server的情况来看，主要是支持SMP和NUMA两种架构。

#p#

Windows性能监控

　　使用管理工具中的“性能”管理工具可以自动从本地或远程计算机收集性能数据。可以使用“系统监视器”查看记录的计算机数据，也可以将数据导出到电子表格程序或数据库进行分析并生成报告。该工具类似于linux下的top，iostat，vmstat等监控命令。

　　对于SQL Server占用CPU资源的监控主要集中在消耗时间的百分比和处理器队列长度上，下面提供了几种对象、计数器和相应的阈值及描述。

▲

SQL Server性能监控

　　SQL Server内部监控对象及其计数器可以通过性能工具进行收集和评估，但从SQL Server2005之后提供了一个叫sys.dm_os_performance_counters的系统视图，这个视图返回的信息列举与SQL Server直接相关的性能统计。

　　下面语句可以统计出SQL Server所有的监控对象和计数器情况。

SELECT object_name,COUNT(DISTINCT counter_name)

　　FROM sys.dm_os_performance_counters

　　GROUP BY object_name

　　ORDER BY object_name

在本机中共计27中SQLServer对象和334个计数器，其中加粗部分字体为最常关注的对象。

　 以下是对象类别、计数器数量和对象描述。

　　MSSQL$MYSQLSERVER:Access Methods 43 搜索并测量 SQL Server 数据库对象的分配(例如，索引搜索数或分配给索引和数据的页数)。

　　MSSQL$MYSQLSERVER:Broker Activation 6 提供有关已激活 Service Broker 的任务的信息。

　　MSSQL$MYSQLSERVER:Broker Statistics 37 提供 Service Broker 的常规信息。

　　MSSQL$MYSQLSERVER:Broker TO Statistics 9

　　MSSQL$MYSQLSERVER:Broker/DBM Transport 34 提供有关Service Broker网络的信息。

　　MSSQL$MYSQLSERVER:Buffer Manager 21 提供有关 SQL Server 所用的内存缓冲区的信息，如可用内存和 buffer cache hit ratio。

　　MSSQL$MYSQLSERVER:Buffer Node 9 提供有关SQL Server请求和访问可用页的频率的信息。

　　MSSQL$MYSQLSERVER:Buffer Partition 3 提供有关SQL Server请求和访问可用页的频率的信息。

　　MSSQL$MYSQLSERVER:Catalog Metadata 4

　　MSSQL$MYSQLSERVER:CLR 1 提供有关公共语言运行时 (CLR) 的信息。

　　MSSQL$MYSQLSERVER:Cursor Manager by Type 9 提供游标信息。

　　MSSQL$MYSQLSERVER:Cursor Manager Total 3 提供游标信息。

　　MSSQL$MYSQLSERVER:Databases 26 提供有关 SQL Server 数据库的信息，如可用的日志空间数量或数据库中活动事务数。这个对象可有多个实例。

　　MSSQL$MYSQLSERVER:Deprecated Features 1 对使用不推荐使用的功能的次数进行计数。

　　MSSQL$MYSQLSERVER:Exec Statistics 4 提供了有关执行统计信息的信息。

　　MSSQL$MYSQLSERVER:General Statistics 24 提供有关服务器范围内的常规活动的信息，如连接到 SQL Server 实例的用户数。

　　MSSQL$MYSQLSERVER:Latches 7 提供有关加在 SQL Server 所用的内部资源(如数据库页)上的闩锁的信息。

　　MSSQL$MYSQLSERVER:Locks 8 提供有关 SQL Server 的单个锁请求的信息，如锁超时和死锁。这个对象可有多个实例。

　　MSSQL$MYSQLSERVER:Memory Manager 14 提供有关 SQL Server 内存的使用信息，如当前分配的锁结构总数。

　　MSSQL$MYSQLSERVER:Plan Cache 5 提供有关 SQL Server 高速缓存的信息，该高速缓存用于存储如存储过程、触发器和查询计划这样的对象。

　　MSSQL$MYSQLSERVER:Resource Pool Stats 15 提供了有关资源调控器资源池统计的信息。

　　MSSQL$MYSQLSERVER:SQL Errors 1 提供有关 SQL Server 错误的信息。

　　MSSQL$MYSQLSERVER:SQL Statistics 11 提供有关 SQL查询各个方面的信息，如 SQL Server 收到的 Transact-SQL 语句的批数。

　　MSSQL$MYSQLSERVER:Transactions 14 提供了有关 SQL Server 中活动事务的信息，如事务总数和快照事务数。

　　MSSQL$MYSQLSERVER:User Settable 1 执行自定义监视。每个计数器可以是一个自定义的存储过程或任何返回一个被监视值的Transact-SQL语句。

　　MSSQL$MYSQLSERVER:Wait Statistics 12 提供有关等待的信息。

　　MSSQL$MYSQLSERVER:Workload Group Stats 12 提供了有关资源调控器工作负荷组统计的信息。
 

#p#

关于CPU的资源消耗基本来自于两个方面，首先是低效率的查询计划，再次是过度编译和重编译。

　　过度编译和重编译，SQL语句的编译和重编译都是CPU密集的活动，发生大量的重编译，则CPU利用率会增加。所以对SQL Server在CPU上的关注会集中在这些SQL统计的编译/重编译计数器上。

▲

下面语句。

SELECT top 25

　　qt.text,

　　qs.plan_generation_num,

　　qs.execution_count,

　　dbid,

　　objectid

　　FROM sys.dm_exec_query_stats qs

　　CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt

　　WHERE plan_generation_num >1

　　ORDER BY qs.plan_generation_num

　 SQL Server中，我们也可以确定平均或累计占用CPU时间最多的查询，SQL Server优化器是基于成本的，通过该查询语句的定位，再进一步分析，是统计信息问题还是索引和连接难题。

SELECT top 50

　　qt.text AS SQL_text ,

　　SUM(qs.total_worker_time) AS total_cpu_time,

　　SUM(qs.execution_count) AS total_execution_count,

　　SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,

　　COUNT(*) AS number_of_statements

　　FROM sys.dm_exec_query_stats qs

　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

　　GROUP BY qt.text

　　ORDER BY total_cpu_time DESC –统计总的CPU时间

　　–ORDER BY avg_cpu_time DESC –统计平均单次查询CPU时间

以下为SQL Server 2008关于CPU的一些系统视图。

▲

　　dm_os_nodes提供了一个名为 SQL OS 的内部组件可创建模拟硬件处理器位置的节点结构。

　　dm_os_schedulers 对于 SQL Server(每个计划程序都映射到其中的单个处理器)中的每个计划程序，相应地返回一行。使用此视图可以监视计划程序的情况或标识失控任务

　　dm_os_workers 则对于系统中的每个工作线程，相应地返回一行。

　　dm_os_threads 对于系统中的所有SQLOS工作线程，相应地返回一行。

　　sys.dm_os_tasks 为 SQL Server 实例中的每个活动任务返回一行。

　　计算可运行状态下的工作进程数量，来观察CPU压力

SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id

　　FROM sys.dm_os_workers AS o

　　INNER JOIN sys.dm_os_schedulers AS s

　　ON o.scheduler_address=s.scheduler_address

　　AND s.scheduler_id<255

　　WHERE o.state=’RUNNABLE’

　　GROUP BY s.scheduler_id

　　也可以查找用户会话和操作系统线程的对照关系

　SELECT STasks.session_id, SThreads.os_thread_id

　　FROM sys.dm_os_tasks AS STasks

　　INNER JOIN sys.dm_os_threads AS SThreads

　　ON STasks.worker_address = SThreads.worker_address

　　WHERE STasks.session_id IS NOT NULL

　　ORDER BY STasks.session_id;
 

上文中详细介绍了SQL Server2008中CPU性能监控的知识，内容比较多，希望大家不要漏过任何一个重要的知识点，相信对大家是百益而没有害处的。

【编辑推荐】

1. SQL Server2008数据挖掘的9种算法
2. SQL Server2008数据库镜像实施笔记
3. SQL Server 数据库给CPU分配线程的操作流程
4. 如何解决CPU过度消耗问题