SQL Server 2005性能故障排除

SQL Server 2005性能故障排除

建立基准: 影响性能的因素 性能基准的作用 建立性能基准

影响性能的因素: 系统硬件资源 网络系统 操作系统 数据库管理系统 数据库设计 客户端应用设计 工作负载

性能基准的作用: 设定性能目标 评估系统运行状况 分析系统性能趋势 量化性能调整效果

建立性能基准: 工具: Performance-->主要针对系统资源的基准获取 SQL Profiler主要针对服务器实例的事件获取 SQLDiag-->主要针对服务器实例的事件收集  要素: 时间 性能计数器的值 关键事件的相关信息

如何使用T-SQL创建跟踪,关联跟踪数据和Windows性能日志数据呢?

1.全选第一条语句--按执行之后就创建一个trace了 2.全选等待各项操作完成,确认SQL Trace中有事件写入的语句--按执行之后可以看到一共有2个Trace ID  一个是1 一个是2

全选根据上述语句返回的TraceID停止相应的Trace并关闭Trace的语句--按执行  然后通过C盘下的TraceInTSQL.trc文件来把它同步起来 全选显示Trace跟踪结果文件的内容的语句--按执行之后可以看到Trace跟踪文件的结果了

隔离问题: 瓶颈 CPU负载分析 内存负载分析 IO负载分析 Tempdb负载分析 问题原因分析

瓶颈: 资源不足 资源的负载不平均 资源故障 资源配置错误

CPU负载分析-1-->见下面列表:

       原因                                 检测方法                                            解决方法

                           性能计数器SQL Server: SQL Statistics      

                                     Batch Requests/sec                     

                                    SQL Compilations/sec                            使用DTA创建合理的索引

                                   SQL Recompilations/sec                

过度编译及重编译               SQL Server事件探查器                      在存储过程内使用RECOMPILE选项

                                        SP: Recompile

                           SQL: StmtRecompile                                使用限定名称Schema.Object                                   

                                         动态管理视图

                                 Sys.dm_os_schedulers

                               Sys.dm_exec_query_stats

CPU负载分析-2-->见下面列表:

       原因                       检测方法                        解决方法

                                                               使用DTA调整索引
                                动态管理视图      
                                                          使用具有明确范围的WHERE子句
                             Sys.dm_exec_query_stats
     查询计划                                                   更新统计数据
                             Sys.dm_exec_sql_text
     效率过低                                                考虑使用Query Hints
                             Sys.dm_exec_cached_plans

CPU负载分析-3-->见下面列表:

       原因                       检测方法                        解决方法

                                 事件探查器
                               RPC: Completed

    查询并行度                  动态管理视图
                             Sys_dm_exec_requests,             使用DTA调整索引
                            sys_dm_os_tasks,           
                            sys_dm_exec_sessions,              使用Query Hints
                            sys_dm_exec_sql_text,
                            sys_dm_exec_cached_plan
                             Sys_dm_exec_query_stats

CPU负载分析-4-->见下面列表:

       原因                       检测方法                        解决方法

                       性能计数器SQL Server: Cursor
                       Manager by Type

                       Cursor Requests /sec             使用基于数据集的操作替代游标
  错误的游标使用       事件探查器                       启用多活动数据集

                       RPC: Completed                   通过API使用更大的获取缓冲区

                       动态管理视图

                       Sys.dm_exec_connections

                       Sys.dm_exec_cursors

如何通过查询了解CPU负载呢?

  全选查找Query Plan的常规统计信息的语句--按执行之后可以看到有多条语句的信息

在选择数据库里面选择AdventureWorks这个数据库--全选SELECT * FROM HumanResources.Employee这条语句--按执行之后可以看到没有多条语句产生了 可以看到有一些统计信息 total_worker_time这一列就是CPU的工作时间

  全选总计耗费CPU时间最长的查询计划的语句--按执行之后可以看到耗费CPU时间最长的查询计划的详细信息了

全选SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations' OR counter = 'elapsed time'这条语句--按执行之后可以看到optimizations一共花费的时间是0.24秒左右

操作系统对内存的支持-->见下面列表:

          配置                       虚拟地址空间                最大物理内存              是否支持AWE

          32位                           2GB                         64GB                        支持
      带3gb参数32位                  3GB                         16GB                        支持

      x64下的32位(WOW)           4GB                         64GB                        支持

      IA64下的32位(WOW)         2GB                          2GB                        不支持

       x64下的64位                   8TB                          1TB                         支持

      IA64下的64位                  7TB                          1TB                          支持

内存压力的分析-->见下面列表:

          压力                       外部进程                                   内部进程

        物理内存       过多外部进程,导致可用物理内存           由于外部进程挤占内存所致
                       减少导致系统缩减工作集                      SQL Server设置改变
                                                              内存中各组件分布改变(高百分比的保留页面和窃取页面)

        虚拟内存       系统页面文件过小,由于物理内存                 页面空间碎片过多
                       无法换出导致内存分配失败。                         过度内存消耗

内存负载分析 - 1-->见下面列表:

  事件号                       事件描述

   701           There is insufficient system memory to run this query.

   802           There is insufficient memory available in the buffer pool.

   8651          Could not perform the requested operation because the minimum
                 query memory is not available. Decrease the configured value for
                 the 'min memory per query' server configuration option.

内存负载分析 - 2-->见下面列表:

  计数器对象                                              计数器

   Process                                            Working Set
                                                         Private Bytes

                                                       Available MBytes
                                                        System Cache
   Memory                                          Resident Bytes
                                                      Committed Bytes
                                                         Commit Limit

                                                    Buffer Cache Hit Ratio
                                                    Page Life Expectancy
SQL Server: Buffer Manager               Checkpoint Pages/sec
                                                    Lazy Writes/sec

内存负载分析 - 3-->Sys.dm_os_memory_clerks  Sys.dm_os_memory_cache_clock_hands  Sys.dm_os_memory_cache_counts  Sys.dm_os_virtual_address_dump

解决内存问题的常见手段-->减少SQL Server主机本地消耗内存的非SQL Server相关进程  确认SQL Server的内存配置: Min memory per query  Min/max server memory  Awe enabled   减少无谓的工作负载: 利用客户端Recordset减轻服务器负载  减少不必要的查询返回数据

全选开启高级配置的语句--按执行  全选显示各项内存相关运行值的语句--按执行之后可以看到相关的运行值 例如: 我全选sp_configure 'min server memory'这条语句--按执行之后可以看到最小内存是多少 最大内存是多少  全选关闭高级配置的语句--按执行

全选显示系统相关信息的语句--按执行之后可以看到系统的相关信息  全选显示SQL Server的内存分配情况的语句--按执行之后可以看到SQL Server 2005内存对象的分配情况

全选显示各种对象占用内存的数量的语句--按执行之后就可以看到各种对象占用内存的数量值了  全选由多页分配器分配的内存总量的语句--按执行之后可以看到用户使用的空间范围有多大  全选统计各种类型Memory_Clerk由多页分配器分配的内存总量的语句--按执行之后可以看到各种类型Memory_Clerk分配的内存总量情况

全选如果rounds_count和remove_rounds_count不断增长, 代表内存面临压力的语句--按执行之后可以看到rounds_count和remove_rounds_count不断增长的结果

IO负载分析-1-->见下面列表:
                计数器对象                           计数器

                                                    %Disk Time
                                              Avg.Disk Queue Length  
                                                 Avg.Disk sec/Read
                                                 Avg.Disk sec/Write
                                                 Avg.Disk Reads/sec
               Physical Disk                 Avg.Disk Writes/sec

IO负载分析-2-->Sys.dm_os_wait_stats  Sys._dm_io_pending_io_requests  Sys._dm_io_virtual_file_stats  Sys._dm_exec_query_stats

解决IO问题的常见手段-->确认内存不是瓶颈  调整硬件  调整查询  调整数据库物理设计  调整数据库逻辑设计

  全选各种由于I/O Latch申请而导致等待的信息的语句--按执行之后可以看到等待的详细信息了  全选查看有挂起IO的数据库或文件的语句--按执行之后可以看到是否有那个IO的数据库或IO的文件正在挂起

  全选寻找IO最频繁的5个查询的语句--按执行之后可以看到5个查询的详细信息

  全选重新启动SQL Server确保所有缓存被清除的语句--按执行之后--然后把消息里面的信息全部复制出来  全选下面的重新启动SQL Server确保所有缓存被清除的语句--按执行之后--把消息里面的全部信息复制出来  最后把上面语句的消息里面的全部信息和下面语句的消息里面的全部信息粘贴到记事本里面进行对比一下  可以看到里面的信息有一些变化了

Tempdb-->常见问题  空间消耗  常见手段

Tempdb的常见问题: Tempdb空间不足  由于tempdb的IO限制导致查询变慢  过度的DDL操作  分配争抢

Tempdb的空间消耗-->见下面列表:

                                表和索引
                               全局临时表

        用户对象            本地临时表
                               表值变量                         

                        临时工作文件(Hash Join)

        内部对象               排序

                     工作表(游标,临时的大型对象数据)

                              数据行版本
        版本信息
                              联机索引操作

解决Tempdb问题的常见手段: 尽量将存储过程中的临时表变为固定表,避免每次执行存储过程都导致DDL 调整索引,使用索引覆盖策略 使用与CPU数量相同的数据文件,合理分布 设置足够大的Tempdb数据文件大小

问题原因分析: 服务器>数据库>单个对象>故障原因

性能问题分类: 硬件资源限制 服务器配置不合理 应用程序设计不合理 物理设计不合理 逻辑设计不合理

性能调整: 排除性能问题的常见策略 分层优化 数据库设计优化

排除性能问题的常规策略: Server Tuning <-- Locking <-- Indexing <-- Query Optimization <-- Schema Design

分层优化规则-->见下图:

           

数据库设计优化手段: 数据分区  泛化和范化  调整查询