SQL Server2008CPU性能监控

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

CPU性能诊断

  CPU架构

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

  SMP(Symmetric Multi Processing)

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

CPU性能诊断

  MPP (Massive Parallel Processing)

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

CPU性能诊断

  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两种架构。

CPU性能诊断

  Windows性能监控

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

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

Windows性能监控

  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统计的编译/重编译计数器上。

CPU的资源消耗

     下面语句。

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的一些系统视图。

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;

 







本文转自baoqiangwang51CTO博客,原文链接:http://blog.51cto.com/baoqiangwang/512118,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
SQL 监控
OBCP第四章 SQL调优-SQL执行性能监控
OBCP第四章 SQL调优-SQL执行性能监控
87 0
|
2月前
|
Java 开发者 前端开发
Struts 2、Spring MVC、Play Framework 上演巅峰之战,Web 开发的未来何去何从?
【8月更文挑战第31天】在Web应用开发中,Struts 2框架因强大功能和灵活配置备受青睐,但开发者常遇配置错误、类型转换失败、标签属性设置不当及异常处理等问题。本文通过实例解析常见难题与解决方案,如配置文件中遗漏`result`元素致页面跳转失败、日期格式不匹配需自定义转换器、`&lt;s:checkbox&gt;`标签缺少`label`属性致显示不全及Action中未捕获异常影响用户体验等,助您有效应对挑战。
76 0
|
5月前
|
SQL 关系型数据库 分布式数据库
在PolarDB中,如果慢SQL导致了CPU升高,进而又产生了更多的慢SQL
【2月更文挑战第22天】在PolarDB中,如果慢SQL导致了CPU升高,进而又产生了更多的慢SQL
45 1
|
SQL 存储 缓存
ORACLE常用性能监控SQL【二】(下)
ORACLE常用性能监控SQL【二】(下)
482 0
|
5月前
|
SQL 监控 Oracle
ORACLE常用性能监控SQL【二】
ORACLE常用性能监控SQL【二】
106 0
|
5月前
|
SQL 监控 Oracle
ORACLE常用性能监控SQL【一】
ORACLE常用性能监控SQL【一】
129 0
|
SQL 关系型数据库 MySQL
慢sql导致mysql服务器的cpu飙升到100%
慢sql导致mysql服务器的cpu飙升到100%
592 0
|
SQL 存储 关系型数据库
记一次MySQL CPU被打满的SQL优化案例分析
记一次MySQL CPU被打满的SQL优化案例分析
215 0
|
SQL 存储 缓存
ORACLE常用性能监控SQL【二】(上)
ORACLE常用性能监控SQL【二】(上)
214 0
|
SQL 缓存 监控
ORACLE常用性能监控SQL【一】(下)
ORACLE常用性能监控SQL【一】(下)
137 0