SQL Server2008CPU性能监控

　　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 提供了有关资源调控器工作负荷组统计的信息。

　　关于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

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