SQL Server性能监视器监视资源使用情况
Tags： MSSQLSQL Server优化性能性能监视性能监视器数据收集器
简介
如果运行的是 Microsoft Windows 服务器操作系统，可使用性能监视器图形工具来测量 SQL Server 的性能。 可以查看 SQL Server 对象、性能计数器以及其他对象的行为，这些对象包括处理器、内存、缓存、线程和进程。 每个对象都有一个相关的计数器集，用于测量设备使用情况、队列长度、延时情况，另外还有吞吐量及内部拥塞指示器。
使用性能监视器可以监视系统资源的使用率。 以计数器的形式收集实时性能数据并查看。 可将计数器用于服务器资源，如处理器和内存使用。 还可将其用于许多 Microsoft SQL Server 资源，如锁和事务。
性能监视器的优点
性能监视器可用于同时监视 Windows 操作系统和 SQL Server 计数器，以便确定 SQL Server 性能与 Windows 性能之间可能存在的关联。 例如，同时监视 Windows 磁盘输入/输出 (I/O) 计数器和 SQL Server 缓冲区管理器计数器可以揭示整个系统的行为。
通过性能监视器可获取当前 SQL Server 活动和性能的统计信息。 使用性能监视器可以：
- 同时查看任意多台计算机中的数据。
- 查看和更改图表以反映当前的活动，以及显示按用户定义的频率进行更新的计数器值。
- 从图表、日志、警报日志和报告将数据导出到电子表格或数据库应用程序中，以进行进一步的操作和打印。
- 添加系统警报，这些警报可在警报日志中列出事件，并可以通过发出网络警报来通知您。
- 当计数器值首次或每次超过或低于用户定义的值时，运行预定义的应用程序。
- 创建日志文件，其中包含有关来自不同计算机的各种对象的数据。
- 将其他现有日志文件中的选定区域追加到一个文件上，以形成长期存档。
- 查看当前活动报告，或从现有日志文件创建报告。
- 保存各个图表、警报、日志或报告设置或整个工作空间设置，以备再次使用。
性能监视器的性能
当监视 SQL Server 和 Microsoft Windows 操作系统以调查与性能有关的问题时，请首先注意以下三个主要方面：
- 磁盘活动
- 处理器利用率
- 内存使用率
监视运行性能监视器的系统会轻微地影响计算机性能。 因此，要么将性能监视器数据记录到另一个磁盘或计算机上，以便减少对所监视计算机的影响，要么从远程计算机上运行性能监视器。 只监视您感兴趣的计数器。 如果监视的计数器过多，将会增加监视过程中使用的资源开销，并影响所监视计算机的性能。
性能监视器任务
| 任务说明 | 主题 |
|---|---|
| 描述何时使用性能监视器，并讨论使用性能监视器时的性能开销。 | 运行性能监视器 |
| 描述如何监视磁盘计数器，以便确定其 SQL Server 组件生成的磁盘活动和 I/O 量。 | 监视磁盘用量 |
| 描述如何监视 Microsoft SQL Server 实例，以便确定 CPU 使用率是否在正常范围内。 | 监视 CPU 用量 |
| 描述如何监视 SQL Server 实例，以便确认内存使用量是否在正常范围内。 | 监视内存用量 |
| 描述如何创建一个在达到性能监视器计数器的阈值时发出的警报。 | 创建 SQL Server 数据库警报 |
| 描述如何创建图表、警报、日志和报表，以便监视 SQL Server实例。 | 创建图表、警报、日志和报告 |
| 列出性能监视器用于在运行 SQL Server 实例的计算机中监视活动的对象和计数器。 | 使用 SQL Server 对象 |
| 列出性能监视器用于监视内存中 OLTP 活动的对象和计数器。 | SQL Server XTP（内存中 OLTP）性能计数器 |
在 Windows 中启动性能监视器
在“开始”菜单上，指向“运行”，在“运行”对话框中键入“perfmon”，然后选择“确定” 。
监视磁盘使用情况
Microsoft SQL Server 使用 Microsoft Windows 操作系统输入/输出 (I/O) 调用来对您的磁盘执行读和写操作。 SQL Server 管理何时以及如何执行磁盘 I/O，但是 Windows 操作系统执行基础 I/O 操作。 I/O 子系统包括系统总线、磁盘控制卡、磁盘、磁带驱动器、CD-ROM 驱动器以及许多其他 I/O 设备。 磁盘 I/O 是导致系统瓶颈的最常见原因。
监视磁盘活动涉及两个主要方面：
- 监视磁盘 I/O 及检测过度换页
- 隔离 SQL Server 产生的磁盘活动
有关详细信息，请参阅监视磁盘使用情况。
有关如何在 SQL Server 中排查 I/O 问题的详细信息，请参阅低 I/O 性能 - SQL Server 和磁盘 I/O 性能。
监视 CPU 使用率
定期监视 Microsoft SQL Server 实例以确定 CPU 使用率是否在正常范围内。 持续的高 CPU 使用率可能表明需要升级 CPU 或需要增加多个处理器。 或者，高 CPU 使用率也可能表明应用程序的调整或设计不良。 优化应用程序可以降低 CPU 的使用率。
一个确定 CPU 使用率的有效方法是使用系统监视器中的 Processor:% Processor Time 计数器。 该计数器监视 CPU 执行非闲置线程所用的时间。 持续 80% 到 90% 的状态可能表明需要升级 CPU 或需要增加更多的处理器。 对于多处理器系统，应为每个处理器监视一个该计数器的独立实例。 这一值代表了在一个特定处理器上的处理器时间之和。 若要确定所有处理器的平均时间，请改用 System: %Total Processor Time 计数器。
另外还可以监视下列计数器来监视处理器的使用率：
Processor: % Privileged Time
对应于处理器执行 Microsoft Windows 内核命令（例如处理 SQL Server I/O 请求）所用时间的百分比。 如果 Physical Disk 计数器的值很高时该计数器的值也一直很高，则考虑安装速度更快或效率更高的磁盘子系统。
备注
不同的磁盘控制器和驱动程序所用的内核处理时间不同。 高效的控制器和驱动程序所用的特权时间较少，可留出更多的处理器时间给用户应用程序，从而提高总体的吞吐量。
Processor: %User Time
对应于处理器执行用户进程（例如 SQL Server）所用时间的百分比。
系统：Processor Queue Length
对应于等待处理器时间的线程数。 当一个进程的线程需要的处理器循环数超过可获得的循环数时，就产生了处理器瓶颈。 如果有很多进程在争用处理器时间，可能需要安装一个速度更快的处理器。 如果使用的是多处理器系统，则可以增加一个处理器。
检查处理器使用率时，需考虑 SQL Server 实例执行的工作类型。 如果 SQL Server 正在做大量的运算，例如包含聚合的查询，或受内存限制但不需要磁盘 I/O 的查询，此时所用的处理器时间可能是 100%。 如果这导致其他应用程序的性能降低，应尝试改变工作负荷。 例如，让计算机只运行 SQL Server实例。
若使用率为 100% 左右（表示在处理大量的客户端请求），可能表示进程正在排队，等待处理器时间，并因而导致出现瓶颈。 可以通过增加速度更快的处理器来解决这一问题。
监视内存使用量
配置 SQL Server 最大内存
默认情况下，随着时间的推移，SQL Server 实例可能会消耗服务器中大部分可用的 Windows 操作系统内存。 获取内存后，除非检测到内存压力，否则将不会释放内存。 这是由设计决定的，并不表示 SQL Server 进程中存在内存泄漏。 使用“最大服务器内存”选项以限制 SQL Server 在大多数情况下可获取的内存量。 有关详细信息，请参阅内存管理体系结构指南。
在 Linux 上的 SQL Server 中，通过 mssql-conf 工具和 memory.memorylimitmb 设置来内存限制。
监视操作系统内存
若要监视内存不足的情况，请使用下列 Windows Server 计数器。 许多操作系统内存计数器都可通过动态管理视图 sys.dm_os_process_memory 和 sys.dm_os_sys_memory 进行查询。
- Memory:Available Bytes
此计数器指示当前有多少内存（以字节为单位）可供进程使用。 Available Bytes 计数器的低值可指示操作系统内存总体不足。 此值可通过 T-SQL 使用 sys.dm_os_sys_memory.available_physical_memory_kb 进行查询。 - Memory:Pages/sec
此计数器指示由于硬页错误而从磁盘取回的页数，或由于页错误而写入磁盘以释放工作集空间的页数。 Pages/sec 计数器的比率高表示分页过多。 - Memory:Page Faults/sec 此计数器指示所有进程（包括系统进程）的页错误率。 到磁盘的分页率偏低但非零（以及由此产生的页错误）是正常的，即使计算机有大量的可用内存。 Microsoft Windows 虚拟内存管理器 (VMM) 在剪裁 SQL Server 和其他进程的工作集大小时会收走这些进程的页。 此 VMM 活动会导致页错误。
- Process:Page Faults/sec 此计数器指示给定用户进程的页错误率。 监视 Process:Page Faults/sec 以确定磁盘活动是否由 SQL Server 分页导致。 若要确定分页过多是由 SQL Server 还是由另一个进程导致，请监视 Process: Page Faults/sec 计数器，该计数器属于 SQL Server 处理实例。
有关如何解决分页过多的详细信息，请参阅操作系统文档。
隔离 SQL Server 所用的内存
若要监视 SQL Server 内存使用量，请使用以下 SQL Server 对象计数器。 许多 SQL Server 对象计数器都可通过动态管理视图 sys.dm_os_performance_counters 或 sys.dm_os_process_memory 进行查询。
默认情况下，SQL Server 根据可用的系统资源动态管理其内存要求。 如果 SQL Server 需要更多内存，它会查询操作系统以确定是否有可用的空闲物理内存，然后使用可用内存。 如果 OS 的空闲内存不足，SQL Server 会将内存释放回操作系统，直到内存不足的情况得以缓解，或者直到 SQL Server 达到“最小服务器内存”限制。 但是，你可以覆盖此选项通过“最小服务器内存”和“最大服务器内存”服务器配置选项来动态使用内存 。 有关详细信息，请参阅 服务器内存选项。
若要监视 SQL Server 使用的内存量，请检查下列性能计数器：
- SQL Server：Memory Manager:Total Server Memory (KB)
此计数器指示 SQL Server 内存管理器当前已提交到 SQL Server 的操作系统内存量。 此数字预计会根据实际活动的需要增长，并且在 SQL Server 启动后将会增长。 使用 sys.dm_os_sys_info 动态管理视图查询此计数器，观察 committed_kb 列。 - SQL Server：Memory Manager:Target Server Memory (KB)
此计数器指示根据最近的工作负载，SQL Server 可能使用的理想内存量。 在一段时间的典型操作后比较“总服务器内存”，以确定 SQL Server 是否分配了所需的内存量。 典型操作后，“总服务器内存”和“目标服务器内存”应类似 。 如果“总服务器内存”明显低于“目标服务器内存”，则 SQL Server 实例可能遇到内存不足的情况 。 在 SQL Server 启动后的一段时间内，随着“总服务器内存”的增长，“总服务器内存”预计会低于“目标服务器内存” 。 使用 sys.dm_os_sys_info 动态管理视图查询此计数器，观察 committed_target_kb 列。 有关配置内存的详细信息和最佳做法，请参阅服务器内存配置选项。 - Process:Working Set
此计数器指示当前进程正在使用的物理内存量（取决于操作系统）。 观察此计数器的 sqlservr.exe 实例。 使用 sys.dm_os_process_memory 动态管理视图查询此计数器，观察 physical_memory_in_use_kb 列。 - Process:Private Bytes
此计数器指示进程为自身使用而向操作系统请求的内存量。 观察此计数器的 sqlservr.exe 实例。 由于此计数器包含 sqlservr.exe 请求的所有内存分配（包括那些不受“最大服务器内存”选项限制的内存分配），因此，此计数器可报告大于“最大服务器内存”选项的值 。 - SQL Server：Buffer Manager:Database pages
此计数器指示缓冲池中包含数据库内容的页数。 不包括 SQL Server 进程中的其他非缓冲池内存。 使用 sys.dm_os_performance_counters 动态管理视图查询此计数器。 - SQL Server：Buffer Manager:Buffer Cache Hit Ratio
此计数器特定于 SQL Server。 需要 90 或更高的比率。 大于 90 的值表示内存中的数据缓存满足所有数据请求中 90% 以上的请求，无需从磁盘读取。 有关 SQL Server Buffer Manager 的详细信息，请参阅 SQL Server Buffer Manager 对象。 使用 sys.dm_os_performance_counters 动态管理视图查询此计数器。 - SQL Server：Buffer Manager:Page life expectancy
此计数器测量最早的页面在缓冲池中停留的时间（以秒为单位）。 对于使用 NUMA 体系结构的系统，这是所有 NUMA 节点的平均值。 此值会不断增加，越高越好。 突然下降表示在进出缓冲池的过程中有大量数据流失，这表明工作负载无法充分利用内存中已有的数据。 每个 NUMA 节点都有自己的缓冲池节点。 在具有多个 NUMA 节点的服务器上，使用 SQL Server:Buffer Node:Page life expectancy 查看每个缓冲池节点的页生存期。 使用 sys.dm_os_performance_counters 动态管理视图查询此计数器。
确定当前内存分配
以下查询返回有关当前分配内存的信息。
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT (total_physical_memory_kb/1024) AS Total_OS_Memory_MB, (available_physical_memory_kb/1024) AS Available_OS_Memory_MB FROM sys.dm_os_sys_memory; SELECT (physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB, (locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB, (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB, process_physical_memory_low, process_virtual_memory_low FROM sys.dm_os_process_memory; |
确定当前的 SQL Server 内存利用率
以下查询返回有关当前 SQL Server 内存利用率的信息。
1 2 3 4 5 | SELECT sqlserver_start_time, (committed_kb/1024) AS Total_Server_Memory_MB, (committed_target_kb/1024) AS Target_Server_Memory_MB FROM sys.dm_os_sys_info; |
确定页生存期
下面的查询使用 sys.dm_os_performance_counters 在整个缓冲区管理器级别和每个 NUMA 节点级别观察 SQL Server 实例当前的“页生存期”值。
1 2 3 4 | SELECT CASE instance_name WHEN '' THEN 'Overall' ELSE instance_name END AS NUMA_Node, cntr_value AS PLE_s FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy'; |
手工使用
当服务器没设置监控的时候、服务器较多的时候、收集或统计的计数器较多的时候、当时间紧迫的时候…怎样才能更方便更快地使用性能计数器收集数据呢？过去常用有一种方法，在性能计数器图像界面中右键 “将设置另存为…”。在需要跟踪的时候，可以很快将该设置拖动到计数器中，这是本地常用的方法。
对于批量收集计数器，把计数器写到配置文件中，如：MSSQL2012BaseLineCounters.config
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | "\Memory\Available MBytes" "\Memory\Free System Page Table Entries" "\Memory\Pages Input/sec" "\Memory\Pages/sec" "\SQLServer:Access Methods\Full Scans/sec" "\SQLServer:Access Methods\Page Splits/sec" "\SQLServer:Access Methods\Workfiles Created/sec" "\SQLServer:Access Methods\Worktables Created/sec" "\SQLServer:Buffer Manager\Buffer cache hit ratio" "\SQLServer:Buffer Manager\Checkpoint pages/sec" "\SQLServer:Buffer Manager\Free pages" "\SQLServer:Buffer Manager\Lazy writes/sec" "\SQLServer:Buffer Manager\Page life expectancy" "\SQLServer:Buffer Manager\Page reads/sec" "\SQLServer:Buffer Manager\Page writes/sec" "\SQLServer:Buffer Manager\Stolen pages" "\SQLServer:General Statistics\Logins/sec" "\SQLServer:General Statistics\Logouts/sec" "\SQLServer:General Statistics\User Connections" "\SQLServer:Latches\Average Latch Wait Time (ms)" "\SQLServer:Locks(_Total)\Average Wait Time (ms)" "\SQLServer:Locks(_Total)\Lock Requests/sec" "\SQLServer:Locks(_Total)\Number of Deadlocks/sec" "\SQLServer:Memory Manager\Target Server Memory (KB)" "\SQLServer:Memory Manager\Total Server Memory (KB)" "\SQLServer:SQL Statistics\Batch Requests/sec" "\SQLServer:SQL Statistics\SQL Compilations/sec" "\SQLServer:SQL Statistics\SQL Re-Compilations/sec" "\Paging File(_Total)\% Usage" "\Paging File(_Total)\% Usage Peak" "\PhysicalDisk(_Total)\Avg. Disk Read Queue Length" "\PhysicalDisk(_Total)\Avg. Disk sec/Read" "\PhysicalDisk(_Total)\Avg. Disk sec/Transfer" "\PhysicalDisk(_Total)\Avg. Disk sec/Write" "\PhysicalDisk(_Total)\Avg. Disk Write Queue Length" "\Process(sqlservr)\% Privileged Time" "\Process(sqlservr)\% Processor Time" "\Processor(_Total)\% Privileged Time" "\Processor(_Total)\% Processor Time" "\System\Context Switches/sec" "\System\Processor Queue Length" |
怎么找到可用的性能计数器名字呢？不会一个个拷贝出来吧？有一种方法，可用查看所有计数器，把计数器导出到文件中选择。
1 | typeperf -q > D:\counters.txt |
接下来以管理员身份运行命令行，执行以下命令，添加自定义数据收集。
1 | logman create counter SQL2012Perf -f bin -b 12/03/2015 00:00:00 -e 12/04/2015 00:00:00 -si 05 -v mmddhhmm -o "D:\SQL2012Perf" -cf "D:\MSSQL2012BaseLineCounters.config" |
参数信息说明如下：
1 2 3 4 5 6 7 8 9 10 | create ：创建计数器收集程序 SQL2012Perf ：计数器名称 -f bin ：二进制格式 -b 12/03/2015 00:00:00 ：起始时间 -e 12/03/2015 15:00:00 ：结束时间 -si 00:00:05 ：间隔5秒记录一次 -v mmddhhmm ：文件格式 -o "D:\SQL2012Perf" ：输出文件名 -cf "D:\MSSQL2012BaseLineCounters.config" ：计数器配置文件 |
当然还可以创建其他收集信息：参考 logman /?
创建完成后并没有启动，可以使用以下命令查看状态、启动或关闭：
1 | Logman queryLogman start SQL2012PerfLogman stop SQL2012Perf |
跟着一段时候后，可打开跟着文件（D:\SQL2012Perf_xxxxxxxx.blg），查看各指标的跟着情况。
为了方便分析数据，可以把计数器生成 csv 格式。
1 | relog -f csv D:\SQL2012Perf_04242136.blg -o D:\SQL2012Perf_04242136.csv |
