2022年SQLServer性能监控指标说明备课讲稿

1、SQLSer v er 性 能 监 控 指 标 说 明精品资料性能监控指标说明 -MSSQL SERVER 2022赵小艳2022 年 6 月 30 日仅供学习与沟通,如有侵权请联系网站删除 感谢2 精品资料前言从 MSSQL SERVER2022 开头,微软开头了漫长的向 ORACLE 学习之旅,本文以 oracle优化为基础,整理了一部分MSSQL SERVER2022 监控指标,这些指标不能全部反映 MSSQL SERVER2022 的性能信息,因此仍需要从更多的开发的角度后继完成优化过程,其中最重要的一环是设计；由于下一步的工作就是实际的优化,请大家指出文档中的不足和错误之处；本文整理

2、用到的资料：清华高校出版社的优,微软技术资料库,MSDN ；仅供学习与沟通,如有侵权请联系网站删除 感谢3 SQL SERVER 2022性能调精品资料目录1. 配置硬件 61.1. 内存 61.1.1. 物理地址空间 61.1.2. 虚拟地址空间 61.1.3. 虚拟内存治理器 61.1.4. 3GB 内存 7 1.1.5. /PAE 7 1.1.6. AWE 8 1.1.7. 挑选 /3GB 、/PAE、仍是 /AWE 8 1.1.8. 64bit 系统内存配置 9 1.1.9. 内存估量大小 9 1.2. I/O 9 1.2.1. 网络 IO 9 1.2.2. 磁盘 IO 10 1.2.

3、3. 储备设计 10 1.2.4. 配置分区 11 1.2.5. 扇区对齐 11 1.2.6. 卷的类型 111.2.7. NTFS安排空间的大小 111.2.8. 磁盘破裂 12 1.3. CPU 12 1.4. 32bit x86 处理器 12 1.4.1. 多核 13 1.4.2. 超线程 13 1.4.3. 64bit 14 1.4.4. X64 或 IA64 14 1.4.5. 高速缓存 14 1.5. 系统架构 15 1.5.1. SMP 15 1.5.2. NUMA 15 1.5.3. SOFT NUMA 15 系统监控指标 16 2.2.1. 内存 16仅供学习与沟通,如有侵权

4、请联系网站删除 感谢4 精品资料2.1.1. 外部压力 16 2.1.2. 内部内存压力 16 2.1.3. 确认内存瓶颈 17 2.2. 磁盘 172.2.1. 基于配置的磁盘瓶颈 2.2.2. 基于模式的磁盘瓶颈 18 182.2.3. 指标 182.2.4. 储备性能压力测试指标 182.2.5. 度量 IO 性能 19 2.3. Cpu 192.3.1. 基于配置的 2.3.2. 基于模式的cpu 瓶颈 20 cpu 瓶颈 202.4. 数据库镜像性能 203. 服务器设置 203.1. Cpu 204. 数据库等待大事 214.1. 常见等待类型 215. 数据库指标 225.1.

5、指标采集和排列 225.1.1. 操作系统资源 225.1.2. 重要统计信息 235.1.3. 等待大事 245.1.4. 数据文件和日志文件 255.1.5. TEMPDB使用 285.1.6. 系统当前资源高消耗sql 285.1.7. 当前堵塞和物理io 多的会话恳求信息 295.1.8. 查询方案多的sql 语句 315.1.9. 索引使用情形 32 6. 开发原就 33 6.1. 数据类型 33 6.2. 储备架构 33 6.2.1. 页 33 7. 优化方法 33 7.1. 统计信息 33 7.2. 绑定变量 33 7.3. 提示 33 8. 错误 34仅供学习与沟通,如有侵权请

6、联系网站删除 感谢5 精品资料1. 配置硬件1.1. 内存1.1.1. 物理地址空间物理地址是处理器用来拜访位于其总线上的全部部件的地址集合；PAE 地址总线是 36bit；64bit 服务器生产商将地址总线限制在 44bit；1.1.2. 虚拟地址空间在 32bit 系统中,进程有 4gb 虚拟地址空间,虚拟内存由内核和用户进程共享；内核和用户进程各使用 2g；1.1.3. 虚拟内存治理器虚拟内存治理器（ vmm）治理全部内存,并在系统中全部使用内存的进程间共享；虚拟内存治理器使每一个进程在需要内存时供应 觉,事实上全部进程争夺有限的物理内存；4gb 的虚拟内存的错操作系统的内存有限,为了让

7、进程有使用 4gb 虚拟内存的错误,用交换空间来保证全部进程的虚拟内存空间的使用；仅供学习与沟通,如有侵权请联系网站删除 感谢6 精品资料假如系统有足够的物理内存,那么 内存即可；假如没有足够的物理内存,vmm 只要分发内存并在进程终止时清理 vmm 必需为每一个进程在需要时供应所需的内存； Vmm 通过页面文件暂存进程有一段时间没有拜访的数据来实现,这 个过程叫分页；进程的数据常常被分页或者交换到磁盘；1.1.4. 3GB 内存增加 32bit 系统中进程内存数量的一种方法是将安排给内核的一部分空间拿 来用作用户模式地址空间；/3GB 选项将边界移动到3GB,每个进程额外获得1GB 的 va

8、s；此时内核只有 1gb虚拟内存,有可能会产生问题导致崩溃；/USERVA 是 windows server2022新增的功能,供应从内核中猎取指定的不同地址空间数量的方式,使每个进程额外获得 方式相同,当然也会导致崩溃；1.1.5. /PAE 0gb 到 1gb 的地址空间,与 3GBIntel 引入用来突破 32bit 地址总线的限制,将地址总线扩展为 36bit；答应 32bit 最多拜访 64GB 内存,要求是：企业版或者更高版本的 windows 服务器,必需运行在 windows 2022 sp1之上的版本；这个配置也是在 boot.ini 中；也可以在 datacenter版本的

9、 os下,也会使用 PAE；仅供学习与沟通,如有侵权请联系网站删除 感谢7 精品资料1.1.6. AWE 地址窗口扩展（ AWE ）是 windows 的 api,答应 32bit 的进程映射虚拟地址空间以外的内存；进程可以使用物理内存储备数据,但是必需映射到 vas中；AWE 是内存到内存的传递 ,因此速度更快； 答应进程使用多于 2gb的内存,但是仍旧局限于 4GB；因此建议和 PAE 联合使用；MS 建议在 64bit 上也使用 AWE ；当然在 64bit 的 sql server上无法激活AWE ,假如账号有 LOCK PAGES IN MEMORY AWE 来拜访内存；的高级用户权

10、限,将自动使用1.1.7. 挑选 /3GB 、/PAE、仍是 /AWE 假如物理内存大于等于 16GB,不支持 3GB 和 PAE 联合使用；这是因 os要求内核地址空间必需要大于1gb,才能治理 16gb的内存空间；不要 3GB 和 PAE,这两个选项都修改资源数量,/3GB 将非分页池的大小 从 256mb 削减到 128mb,/PAE 不削减非分页池的大小,却将每次安排的大小 从 4 字节倍增到 8 字节；联合使用对系统进行了两次扼杀；少于 4GB 物理内存,均不适用；4GB 物理内存,假如内存是瓶颈,使用 3GB 4GB 物理内存以上,使用 /PAE 和/AWE ；尽量使用 64bit

11、；仅供学习与沟通,如有侵权请联系网站删除 感谢8 精品资料1.1.8. 64bit 系统内存配置X64 和 IA64 之间有稍微不同的内存布局,并供应不同大小的 VAS ；IA64 供应 7TB,X64 供应 8TB；Windows server 2022 sp1目前支持的最大物理内存为 2TB,对于 sql server而言,不用担忧使用 /PAE 或者 /3GB；他有足够的虚拟地 址空间使用；1.1.9. 内存估量大小min server memory 和 max server memory 内存估量大小 =（每个全部者 64 字节 + 32 字节） * 锁的数量 +3 * network

12、_packet_size（默认 4KB） + 94 KB* 会话数假如会话有多个活动结果集,那么内存使用量为 3 + 3 * num_logical_connections * network_packet_size + 94 KB Sql server启动时依据配置（系统最大物理内存数,系统最大线程数和其他参 数）设置缓冲区大小； Sys.dm_os_sys_info中记录当前占用操作系统资源的杂项 信息；1.2. I/O I/O 包含网络 io 和磁盘 io；数据库对磁盘性能相当敏锐；1.2.1. 网络 IO 设计时尽量削减返回给客户端的数据量；仅供学习与沟通,如有侵权请联系网站删除 感谢

13、9 精品资料1.2.2. 磁盘 IO 1.2.3. 储备设计磁盘驱动器有两种： SCSI或者 ATA ；ATA 驱动器是低端市场的桌面运算 的挑选； SCSI成本高,价值表达在数据完整性,可伸缩性,牢靠性和高占空比（预期磁盘驱动器用法）；高占空比意味着驱动器被设计为用于连续的 7*24 小 时使用；串行 ATASATA 和串行 SCSI（SAS）是现今最常见的新驱动器；串行代 表磁盘驱动器的接口,基本上与并行产品相同,不同在于磁盘内加了新的高速 接口；直连式储备（ DAS）是讲一个服务器直接连接到磁盘阵列上,供应正确的 性能,但是在企业级应用中的上百个服务器有不同的磁盘空间需求,但是受限于所能

14、供应的磁盘规模,导致大量的空间铺张；储备区域网络（ SAN）供应了一个公共储备池,给网络供应健壮动态的存 储；使用主机总线适配器来进行数据传送,使用大量高速缓存；HBA 队列长度是在 SAN 中能够并行恳求的数目限制,默认在 8 到 32 之 间,由于 SQL SERVER 的 io 是密集型,因此该值应当设置为 64 或者更大；如 果没有使用 SAN,可以将设备治理器队列长度设置为 64 到 128；HBA 驱动器有两种： SCSIPort和 StorPort；StorPort比 SCSIPort新；数据文件布置一般建议将数据文件、日志文件和 轴上；仅供学习与沟通,如有侵权请联系网站删除 感

15、谢10 tempdb存放在不同的物理精品资料1.2.4. 配置分区磁盘安装后需要配置分区,目前有两种：MBR 和 GPT；现在只有 windows server 2022 sp1以上才支持 GPT,并且在 failover clustering 中不支持,目前仍使 用 MBR ,但是 GPT 是大势所趋；1.2.5. 扇区对齐扇区是磁盘最小储备空间,通常为 的,由扇区组成的圆周轨迹；每磁道有512 字节；磁道是位于磁盘一个圆周上 63 个扇区；块是扇区的规律称谓；由于不同储备供应商在定义磁道时的差异导致对齐错误,会显现写入跨过 2 个条带,从而影响性能；有两种方法进行扇区对齐；DISKPAR（

16、快对齐）和 DISKPART （KB 对齐）, windows 2022 sp1更合适的是 DISKPART ；1.2.6. 卷的类型卷是磁盘分区的规律抽象,分基本卷和动态卷；基本卷供应多年以来使用的简洁高效的储备方式；动态卷在windows 2022 引入,支持额外的特性,如跨区可扩展卷和软件驱动 RAID ；随着硬件的进展,动态磁盘被舍弃了；1.2.7. NTFS安排空间的大小在 windows 中格式化一个分区时,会指定NTFS 安排单元的大小, 微软试验室测试说明,对于数据文件和日志文件的分区,正确单元大小是 64KB；仅供学习与沟通,如有侵权请联系网站删除 感谢11 精品资料1.2.

17、8. 磁盘破裂在 SQL SERVER 中破裂可能一两种形式发生：发生在数据陈旧并经受了大量的插入、更新、删除时；发生在创建文件时,文件系统没有足够的连续空间在单独的片段中创建文件,结果是导致一个文件分布在磁盘说明的多个文件片段中；当 sql server文件一旦被创建后就不会变得更加破裂,假如创建时没有连续的空间,文件在多个片段中创建,假如此后进行了碎片整理,那么文件就不会呈碎片状,将来也不会；抱负的场景是对 sql server文件使用专用磁盘,并设置合适的大小,并禁用自动增长；假如没有专用的驱动器用于数据库,而且构建工作从头开头,那么最好的方法是：1. 安装 os 2. 整理磁盘碎片 3

18、. 安装全部应用程序 4. 整理磁盘碎片 5. 以最大大小创建数据文件和日志文件 6. 停止 sql server,检查碎片,有需要整理磁盘碎片 7. 禁用自增长,或者将增量设置大一些；8. 周期性进行磁盘碎片整理；1.3. CPU 1.4. 32bit x86 处理器以后会被 64bit 取代；仅供学习与沟通,如有侵权请联系网站删除 感谢12 精品资料1.4.1.多核18 个月就会翻番,现在有变为每10 个月就翻摩尔定律是处理器的才能每番的趋势；多核是指同一个芯片上有个 每个插槽,而不是每个内核；对用户来说 相当,但是许可成本减半；1.4.2. 超线程cpu；Ms 的 per-process

19、or的许可证发给 4 个双核 cpu 同 8 个单核服务器性能线程是运行在 cpu 上的执行单元, cpu 每次只能执行一个线程,它通过在线 程间切换产生并发的错觉；超线程是 intel 处理器的技术,试图通过复制架构状态来供应两个规律 cpu,完成同时使用处理器不同部件执行不同任务的工作；使用超线程供应理论上 30%性能的好处,实践中,仅仅是1.1 到 1.15 倍 cpu 的才能；假如工作负荷是从高速缓存中行紧致循环,那么超线程不会有好处；假如挑选并行方案时,并行方案所作的事情是假定每个处理器在给定的时 间内能够完成相同数量的工作这个前提下,将要做的工作分割并指派给可用的 处理器上,在使用

20、超线程时,任何不是当前执行的线程将被停止,所以并行计 划在超线程下得不到性能提高；在 windows server2022上意识不到超线程,但是2022 sp1可以使用,随着处理器的更新换代,超线程越来越好；有机会通过超线程获得好处,但是由于 测试成本以及应用程序的成本,一般来说客户会关闭超线程；仅供学习与沟通,如有侵权请联系网站删除 感谢13 精品资料1.4.3. 64bit 64bit 有一个潜在的缺点,就是缺少 64bit 的应用；1.4.4. X64 或 IA64 64bit 驱动程序；有些应用仍未开发出支持X64 处理器运行速度超过 3Ghz,IA64 在 1.6Ghz左右； IA6

21、4 处理器的高速 缓存大于 X64 处理器的高速缓存； IA64 越来越在专用机器上显现,包括高性能 工作站,大型高可伸缩性系统；1.4.5. 高速缓存高速缓存产生的缘由在于处理器速度远远高于主储备器,储备器无法跟上 处理器对内存的希望；处理器设计者为明白决这一问题,增加了多层高速缓存；处理器分 L1,L2,L3 ,离处理器越远,规模越大速度更慢；L1 只储存地址查 找、数据和指令, L2,L3 储存内存的任意备份；Processor Cache被实现为透亮的 look-thru cache,芯片上有着掌握功能用来 治理填充高速缓存的过程,治理高速缓存条目；Sql server2022启动时占

22、用 29M 大小内存,使用 sqlcmd 连接到数据库并执行一个简洁的查询,内存增 令,内存增加 3m 左右；0.5m；SQL SERVER2022 启动就有 50m,同样的命仅供学习与沟通,如有侵权请联系网站删除 感谢14 精品资料Sql server2022的 sqlservr.exe大小为 8.9m,SQL SERVER2022 该文件大得 多,为 28m,增加的代码为高速缓存重用部分,包含查询方案重用,增加的内 存就是查询方案储备空间等；1.5. 系统架构主要是指机器架构,如单插槽,双插槽,四插槽,甚至对称多处理器（SMP）. 1.5.1. SMP 对称多处理器通过一个系统总线连接到一

23、个共享内存上；SMP 加剧了处理 器和内存之间的速度问题；系统处理器有数十个的时候,系统总线会成为瓶颈；1.5.2. NUMA 非一样性内存拜访（ NUMA ）是一种超越 SMP 的可伸缩性的架构, NUMA 系 统中有多个系统总线,每一个系统总线能连接的处理器最大为 4,最多 4 个 cpu 共享一块内存；以下语句确认系统是否使用 NUMA Select distinct memory_node_id from sys.dm_os_memory_clerks 返回 0 说明未使用 NUMA ；AMD 处理器都作为 NUMA 实现的；1.5.3. SOFT NUMA 这是 SQL SERVER

24、2022 的新特性,答应将cpu聚合成 soft-NUMA 节点,假如没有硬件 NUMA, 但是 cpu 个数许多,可以提高性能；仅供学习与沟通,如有侵权请联系网站删除 感谢15 精品资料建议使用 SOFT NUMA 来对 cpu进行分组；2. 系统监控指标留意：凡是 sqlserver的指标值,都可以使用以下sql 语句获得：select cntr_value fromsys.dm_os_performance_counters= total where object_name= MSSQL$ZXY:Buffer Managerand counter_namepages 以 2.1.1 第

25、2 点的 Sql server:Buffer managertotal pages为例,其中 蓝色 字体代表实例名称,将 红色字体替换为 Buffer manager,粉色字体替换为 total pages即 可；需要留意的是复制时留意空格要和原先一样；2.1. 内存2.1.1. 外部压力1、 Process working set 显示每一个进程虚拟地址空间中最近引用的页面规模；消耗内存最多的进程就是消耗大量内存的应用；指一个进程的4GB 虚拟地址空间中被映射到RAM 中的部分的大小,通常是该进程的虚拟内存中的活跃部分；2、 Sql serverSql server:Buffer manag

26、ertotal pages 显示 sql server 已获得的页面数3、 Sql serverSql server:buffer managertarget pages 显示 sql server 的 buffer pool 必需的抱负页面数；2.1.2. 内部内存压力内部压力通常是收缩缓冲池导致的；缓冲池规模变小,通常是 sp_configure的 max server memory的值减小导致；或者是其他数据库对象消耗了内存空间导致,如：作业,扩展储备过程,com对象, sqlclr,链接服务器；通过 dbcc memorystatus查看 buffer counts部分,确定内存是否存

27、在压 力；其中 committed memory和 target memory最重要； Committed memory 表示仅供学习与沟通,如有侵权请联系网站删除 感谢16 精品资料sql server已经得到的内存数量, target memory表示有效运行所需的内存数量；当两个存在差别过大,说明可能存在内存压力；2.1.3. 确认内存瓶颈1、SQLServer:Buffer Manager Page Life Expectancy 显示数据页在缓冲池中驻留的时间长度（单位是秒）；值越大,系统越健康；300s 或者更少；假如存在内存瓶颈,这个值会低于2、SQLServer:Buffer

28、ManagerBuffer cache hit ratio 显示数据库内存命中率,所请求的数据或者说页面在缓冲池（物理内存）被找到的次数；假如这个值很低,说明3、内存不足； 这个值至少大于98%；和 max server 缓冲池的大小由两个参数打算：sp_configure 的 min server memorymemory ；当其他应用使用了这些内存后,操作系统就不会再将这些内存分给sql server. 4、SQLServer:Buffer ManagerStolen pages 显示了内存被其他进程挪用的页面；当5、这个指标与目标内存页面数比例较大时可能存在问题；SQLServer:Me

29、mory ManagerMemory Grants Pending 显示等待内存授权的进程队6、列； 这个指标值为0 时抱负状态；SQLServer:Buffer ManagerCheckpoint pages/sec 显示检查点操作每秒写入磁盘7、的脏页数目； 假如这个值很高,说明缺少内存；SQLServer:Buffer ManagerLazy writes/sec 显示每秒将脏页从小写到磁盘的次8、数； 这个值应当尽可能接近0,当大于 20 或者更多,确信缓冲池不够；Memtoleave大小基于工作者线程的数目动态变化,是一个独立的内存地址范畴,供链接服务器,扩展储备过程,com 对象或

30、第三方ddl 使用；2.2. 磁盘1、 PhysicalDisk AVG.Disk Aueue Length 显示每一个磁盘的队列长度；不要挑选 _TOTAL对象,而是查看某驱动盘符的队列长度,假如参数值连续大于 2,就可能影响性能；2、 PhysicalDisk AVG.Disk sec/Read显示每次读取的平均磁盘时间；3、 PhysicalDisk AVG.Disk sec/Write 显示每次写入的平均磁盘时间；4、 假如存在磁盘性能问题,掌握器使用率等问题,上面两个计数器的平均值高于正常值； 抱负情形下值小于 10 毫秒 ；5、 SQL Server Access Methods

31、Full scans/sec 显示每秒恳求完全索引扫描或者全表扫描的数目, 假如扫描频率每秒大于 1,那么说明索引缺少或者索引比较差；6、 SQL Server Access Methods Page Splits/sec 显示每秒页面拆分的次数；可以通过适当的索引保护或者好的填充因子来防止发生；仅供学习与沟通,如有侵权请联系网站删除 感谢17 精品资料2.2.1. 基于配置的磁盘瓶颈1、 数据文件和日志文件布局（将这两类文件隔离,并且将每一类文件也隔离）2、 数据文件和日志文件自动增长治理（手动治理）3、 Tempdb 配置（指定初始化大小）4、 Tempdb 内部争用5、 磁盘 /掌握器争

32、用（用储备厂商的性能监控工具）2.2.2. 基于模式的磁盘瓶颈1、 索引不当或者缺少索引,使用数据库优化顾问（者查看 sys.dm_db_index_usage_stats；database tuning advisor ）来解决；或2、 文件和文件组,将读取数据量大且频繁的表单独放到文件或者组合到文件组中,可以适当降低 io 并便于移植以提高性能；3、 分区,降低 io 4、 索引 /表碎片,当索引和表的碎片较多时会产生页拆分（类似于 oracle 的行链接和行迁移）；产生的原意有两种：内部和外部；内部碎片是指8kb 数据页包含数据很少；外部碎片是指数据不是次序存放的；在dm_db_inde

33、x_physical_stats供应当前碎片级别的全部信息；只有有聚簇索引的表才能重建索引；使用2.2.3. 指标alter index 命令重建索引；1、 SQL ServerGeneral StatisticsTemp Tables Creation Rate 显示每秒创建的暂时表和暂时变量的数目；2、 SQL ServerGeneral StatisticsTemp Tables For Destruction 显示不再需要的正在等到销毁的暂时表的数目；3、 Sys.dm_db_task_space_usage 和 sys.dm_exec_requests 查看每一个服务器进程使用的te

34、mpdb 空间,具体见 5.1.5；2.2.4. 储备性能压力测试指标1. IOPS 是每秒能够为输入输出恳求供应服务的度量；得到的子系统实际吞吐量和 io 恳求的大小的函数；2. Block size 供应 sql 执行的 IO 的大小的指示3. MB/sec 每秒有多少 MB 的数据进入或者离开子系统；4. Latency 为每一个 io 完成所必需等待的时间长度；5. DISK Queue length 磁盘队列的长度,说明当前 io 恳求时排队等候的 io 恳求的数目；仅供学习与沟通,如有侵权请联系网站删除 感谢18 精品资料2.2.5. 度量 IO 性能%DISK Read Time

35、,%Disk Time,%Disk Write Time %Disk Idle Time 说明采纳周期内磁盘读取,踩入,使用和闲暇各占的百分比；Avg.Disk Bytes/sec/Read, Avg.Disk Bytes/sec/Transfer, Avg.Disk Bytes/sec/Write说明每次读取、写入或者传送的平均字节数；Avg.Disk Queue length, Avg.Disk Read Queue length, Avg.Disk Write Queue length说明白平均磁盘队列长度,磁盘队列长度是等待被送到磁盘的恳求的数目；Avg.Disk sec/Read,

36、Avg.Disk sec/Transfer, Avg.Disk sec/Write说明 io 的推迟,每次读取或者写入所占用时间的长短；Disk Bytes/sec, Disk Read Bytes/sec, Disk Write Bytes/sec说明以 Bytes/sec为单位显示吞吐量；Disk Read/sec, Disk Transfer /sec, Disk Write/sec显示得到的 IOPS的数目；2.3. Cpu 1、 SystemProcessor Queue Length 显示系统队列长度,假如平均值大于3,那么说明cpu存在瓶颈 ；2、 Processor %Priv

37、ilege Time 显示操作系统内部操作所花费的时间；3、 Processor %User Time 显示用户模式操作花费的时间4、 Processor %User Time sqlservr 显示 sql server 进程消耗的 cpu 时间的准确数量；这个最好从性能监视器来看；5、 SQL Server SQL Statistics SQL Compilations/sec 显示查询方案的编译次数；6、 SQL Server SQL Statistics SQL Re-Compilations/sec 显示查询方案的重编译次数；7、 当 SQL Compilations/sec 和 S

38、QL Re-Compilations/sec 次数过高的话,说明可能未使用绑定变量导致方案重新编译,应用上的瓶颈；或者说重新编译次数比编译次数过高的话,那么说明存在仅供学习与沟通,如有侵权请联系网站删除 感谢19 精品资料2.3.1.基于配置的cpu 瓶颈很难有建议或者计数器来帮助sp_configure的 cpu 相关配置（ Affinity Mask,Hyper Threading,Priority Boost,Maximum Degree of Parallelism,Max Worker Threads）,故不加以描述；2.3.2.基于模式的cpu 瓶颈当 SQL Compilatio

39、ns/sec和 SQL Re-Compilations/sec次数过高的话,说明可能未使用绑定变量导致方案重新编译,那么说明存在应用上的瓶颈；2.4. 数据库镜像性能目前不关怀,以后补充；3. 服务器设置3.1. Cpu 建议不使用超线程或者说重新编译次数比编译次数过高的话,假如系统有 8 颗以上 cpu 并且没有硬件 NUMA, 那么建议配置 soft-NUMA ；系统是否 NUMA 判定如下：Select Case countdistinct parent_node_id when 1 then NUMA disabled else NUMA enabled end as a From s

40、ys.dm_os_schedulers where parent_node_id32 仅供学习与沟通,如有侵权请联系网站删除 感谢20 精品资料4. 数据库等待大事SQL ServerWait StatisticsLock Waits 显示进程等待获得锁的时间；SQL ServerWait StatisticsMemory Grant Queue Waits显示进程在队列中等待内存锁花费的时间；SQL ServerWait StatisticsPage I/O Latches 4.1. 常见等待类型WAITFOR: 是人工挑起的等待,不用关注；LAZYWRITER_SLEEP: 表示 lazy

41、witer 进程处于睡眠状态并等待运行的时间,不 用关注；SQLTRACE_BUFFER_FLUSH ：当系统等待 sql trace缓冲区被写到磁盘时显现 的等待；一般可以看做永久存在的特性；SLEEP_BPOOL_FLUSH:检查点等待时,会计数；RESOURCE_SEMAPHORE:全部 hash,sort,批量复制以及索引创建等操作均要使用工作区；一般来说工作区大小为CXPACKET ：显示并行正在发生,在25%到 70%之间；说明白内存压力；oltp 系统中,他不应当是主要等待大事,但是代表了主动生成cpu 时间的线程,假如是主要等待大事,说明存在cpu 瓶颈；SOS_SCHEDUL

42、ER_YIELD: 当任务自动产生处理器时间并等待再次被调度时发生,假如这个等待值比较高,说明存在 cpu 等待；RESOURCE_SEMAPHORE_QUERY_COMPILE 显示系统中并发编译的次数；这个次数过多说明需要查询参数化；仅供学习与沟通,如有侵权请联系网站删除 感谢21 精品资料I/O_COMPLETION,ASYNC_I/O_COMPLETION:显示等待非数据页面的I/O,或者长期运行的 I/O 绑定操作,说明白磁盘瓶颈；PAGEIOLATCH_* 显示将数据页从磁盘检索到内存的时间,说明白 IO 压力 LCK_M_* 等待获得某个资源的锁；LOGBUFFER：说明正在等待

43、日志缓存写入事务日志文件,大的值说明日志文 件磁盘 io 瓶颈；LOGMGR_RESERVE_APPEND ：日志文件太小,添加日志文件；PAGELATCH_* 说明在任务等待不处于 I/O 恳求中的缓冲区闩锁时发生,说明有连接同时想拜访缓存中同一个数据页；LATCH_* 5. 数据库指标5.1. 指标采集和排列5.1.1. 操作系统资源5.1.1.1. 采集方式下面的语句获得 SQL Server 可用资源及其已占用资源的有用杂项信息的指标,可以每 15 分钟采集一回；select cpu_count as 系统的规律 CPU数, hyperthread_ratio as 规律内核数与物理内

44、核数的比 , round physical_memory_in_bytes / 1024 / 1024 , 0 as 物理内存, round virtual_memory_in_bytes / 1024 / 1024 , 0 as 虚拟内存, bpool_committed as 缓存池已提交内存页数 , bpool_commit_target as 缓存池目标内存页数 , bpool_visible as 进程虚拟地址空间可用内存页数 , max_workers_count 仅供学习与沟通,如有侵权请联系网站删除 感谢22 精品资料as 最大工作线程数-,process_kernel_tim

45、e_ms as 内核态毫秒数,process_user_time_ms as 用户态毫秒数 ,sqlserver_start_time as 启动时间from sys.dm_os_sys_info5.1.1.2. 指标说明指标名称 数据类型 指标说明系统的规律 CPU 数 int 我感觉是是否使用超线程的标志规律内核数与物理内核数的比 int 物理内存 Int 可用物理内存的数量虚拟内存 int 对用户模式进程可用的虚拟内存的数量 ,可以用来判定是否使用 /3GB 开关；缓存池已提交内存页数 int 此数量表示缓冲池中的已提交物理内存页数,单位为 8KB；不包括缓冲池中的保留内存缓存池目标内存

46、页数int 目标数量是使用诸如系统当前状态（包括它的负载、当前进程所恳求的内存、运算机上安装的内存量以及配置参数）等多种输入计进程虚拟地址空间可用内存页int 算得到的；假如 bpool_commit_target 大于bpool_committed 值,就缓冲池将尝试获得额外内存；假如 bpool_commit_target 小于bpool_committed 值,就缓冲池将收缩；不使用地址窗口化扩展插件 AWE 时,假如数缓冲池已获得它的内存目标bpool_committed = bpool_commit_target,就 bpool_visible 的值等于 bpool_committe

47、d 的值；在 32 位版本的 SQL Server 上使用 AWE 时,bpool_visible 表示在拜访缓冲池所安排的物理内存时使用的 AWE 映射窗口的大小；此映射窗口的大小由进程地址空间绑定,因此,可见数量将小于提交数量,并且通过为数据库页之外的其他用途而消耗内存的内部组件会进一步削减可见数量；假如 bpool_visible 的值太低,就可能收到内存不足错误；最大工作线程数 int 5.1.2. 重要统计信息5.1.2.1. 采集方式下面语句获得高速缓冲区命中率的值,采集可以每 30s 采集一次select round cast sum case when counter_name

48、 =Buffer cache hit ratiothen cntr_value else 0 end as decimal 38 , 16 / sum case when仅供学习与沟通,如有侵权请联系网站删除 感谢23 精品资料counter_name=Buffer cache hit ratio basethen cntr_value likeelse 0 end* 100 , 2 as b where counter_name Buffer fromsys.dm_os_performance_counterscache hit ratio% 下面的语句返回方案缓冲区不同池的命中率,可以每3

49、0s采集一次 ：select instance_name , round cast sum case when counter_name =Cache Hit Ratio then cntr_value else 0 end as decimal 38 , 16 / sum casewhen counter_name =Cache Hit Ratio Base then cntr_value else 0 end * 100 , 2 as b from sys.dm_os_performance_counters whereobject_name =MSSQL$ZXY:Plan Cache g

50、roup by instance_name having sum case when counter_name =Cache Hit Ratio Base thencntr_value else 0 end 0下面语句获得每秒索引扫描和全部扫描次数,可以每30s采集一次 ：select sum case when counter_name =Full Scans/sec then cntr_value else 0 end as full_table_scans , sum case when counter_name =Range Scans/sec then cntr_value else

51、 0 end as range_scans from sys.dm_os_performance_counters where counter_name in Full Scans/sec , Range Scans/sec 5.1.2.2. 指标说明5.1.3. 等待大事依据平均等待大事来说明数据库等待大事5.1.3.1. 采集方式selectwait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms from sys.dm_os_wait_statswhere waiting_tasks_c

52、ount=1 order by wait_time_ms/waiting_tasks_count desc仅供学习与沟通,如有侵权请联系网站删除 感谢24 精品资料5.1.3.2.指标说明数据类型指标说明指标名称wait_typenvarchar60 等待大事名称waiting_tasks_countbigint等待次数bigint该等待类型的总等待时间wait_time_msbigint（毫秒）；该时间包括signal_wait_time_ms；max_wait_time_ms最长等待时间（毫秒）signal_wait_time_ms bigint该等待大事从收到信号到运行等待时间（毫秒）5

53、.1.3.3.等待大事说明参见第四章；5.1.4. 数据文件和日志文件5.1.4.1. 采集方式下面分别说明数据库空间,数据库空间使用等信息,采集周期可以是每天一次,或者每周一次；数据库空间 ：selectnameas数据库空间 , data_space_id as空间编号 , typeas空间类型, is_default as默认数据空间标志fromsys.data_spaces 数据库空间使用信息 ：sp_helpdb返回信息如下：指标名称数据类型指标说明name 数据库名称；sysname db_size nvarchar13 数据库总计大小；dbid smallint 数据库 ID ；

54、仅供学习与沟通,如有侵权请联系网站删除感谢25 精品资料status nvarchar600 指定数据库具体空间使用信息：sp_helpdb 数据库名称取返回的其次个结果集,具体信息如下：name 指标名称数据类型指标说明规律文件名；nchar128 fileid smallint 文件 ID ；filename nchar260 操作系统文件名（物理文件名称）；文件所属的文件组；filegroup nvarchar128 NULL = 文件为日志文件；size nvarchar18 它决不是文件组的一部分；文件大小 MB ；文件大小可达到的最大值；maxsize nvarchar18 此字段

55、中的 UNLIMITED 值表示文件可以始终增长到磁盘已满；文件的增量；表示每次需要growth nvarchar18 新的空间时给文件增加的空间大小；文件用法；对于数据文件,usage varchar9 该值为 data only,对于日志文件,该值为 log only；全部数据库对应的数据文件和日志文件 io 信息 ：select dbid , eqp . fileid , NumberReads , BytesRead /1024/1024 as bytesread , IoStallReadMS , Numberwrites , Byteswritten /1024/1024 as b

56、ytewitten , IoStallwriteMS , IoStallMS from select * from sysfiles aa cross apply fn_virtualfilestats null, aa . fileid eqp全部数据库暂时表空间文件空间安排信息：select b . nameas数据库 , c . nameas文件的规律名称, c . filenameas文件物理名称, version_store_reserved_page_count 感谢26 as统一区总页数, unallocated_extent_page_count 未安排取总页数, 仅供学习与沟

57、通,如有侵权请联系网站删除精品资料user_object_reserved_page_count as统一区用户对象用户对象总页数, internal_object_reserved_page_count as 统一区文件内部用户对象总页数 , mixed_extent_page_count as 混合区总页数from sys.dm_db_file_space_usage a inner join sysdatabases b ona . database_id =b. dbid inner join sysfiles c on a . file_id =c . fileid 5.1.4.2.

58、 指标说明数据库空间：指标名称数据类型数据空间类型：指标说明数据库空间名称nvarchar 空间编号int 空间类型char2 FG = 文件组默认数据空间标志bit PS = 分区方案1 = 这是默认数据空间；当未在 CREATE TABLE 或 CREATE INDEX 语句中指定文件组或分区方案时,将使用此数据空间；0 = 这不是默认数据空间；全部数据库对应的数据文件和日志文件io 信息 ：指标说明 数据库编号 文件编号 文件读次数 文件读字节数指标名称数据类型dbidsmallintFileid smallintNumberReadsbigintBytesReadbigintIoSta

59、llReadMS bigint用户等待文件的读取 I/O 完成所费的总时间（以毫秒为单位）；Numberwrites bigint文件写次数 I/O 完成所费的总时间Byteswritten bigint文件写字节数IoStallwriteMS bigint用户等待文件的写（以毫秒为单位）；IoStallMS bigint等待文件读写完成总时间（以毫秒为单位）全部数据库暂时表空间文件空间安排信息：指标名称数据类型指标说明,不包含已安排区数据库名称Varchar20 文件的未安排区中的总页数文件的规律名称Varchar20 文件物理名称Varchar260 未安排区总页数bigint的未使用页仅

60、供学习与沟通,如有侵权请联系网站删除 感谢27 精品资料统一区总页数 bigint 统一区总页数统一区用户对象用户对象总 bigint 统一区用户对象总页数包含已安排未使用页数 页；统一区文件内部用户对象总 bigint 统一区文件中内部对象总页数,包含已安排页数 未使用页；混合区总页数 bigint 混合区已安排和未安排总页数之和；5.1.5. TEMPDB使用5.1.5.1. 采集方式select obj.objectid,sumtmp.user_objects_alloc_page_count asuser_objects_alloc_page_count, sumtmp.user_ob