SQL Server性能分析与优化

概述

本文主要介绍使用阿里云ECS实例搭建并使用SQL Server时，SQL Server性能相关的分析与优化。

详细信息

请根据现场实际情况，参考以下对应的解决方案。

前提条件

在进行系统服务排查之前，需满足以下条件：

• 系统登录使用正常，无明显系统故障。

• 性能监控工具正常。

• 在性能分析之前，由于相关分析需要依赖缓存数据，请确保相关数据未清理。

排查项目

• SQL Server整体性能分析。

• SQL Server慢SQL分析。

• SQL Server CPU/内存/IO分析。

排查方案

SQL Server整体性能分析

• 使用“性能监视器”查看使用情况。

  • Processor 查看CPU性能。

  • PhysicalDisk 查看IO情况。

  • Memory 查看内存情况。

  • SQLServer:Plan Cache 查看高速缓存使用情况。

  • SQLServer:Transactions 查看大事务使用情况。

  • SQLServer:Databases 查看数据库整体分析情况。

  • SQLServer:Database Mirroring 查看镜像同步情况。

  • SQLServer:Buffer Manager 查看内存使用情况。

  • SQLServer:Latches 查看Latches使用情况。

  • SQLServer:Locks 查看Locks使用情况。

  • SQLServer:Wait Statistics 查看资源等待情况。

• 查看sys.sysprocesses视图分析等待类型。

• 查看sys.dm_db_index_physical_stats视图，分析索引碎片率。

SQL Server慢SQL分析

• sys.dm_exec_query_stats 查看查询缓存的统计信息。

• sys.dm_exec_sql_text 查看缓存查询SQL。

• sys.dm_exec_query_plan 查看缓存查询计划。

SQL Server CPU/内存/IO分析

• sys.dm_os_wait_stats 查看等待资源统计信息。

• sys.dm_os_buffer_descriptors 查看对象缓存计数情况。

• sys.dm_os_performance_counters 查看内存命中率。

• sys.dm_io_virtual_file_stats 查看IO使用情况。

优化建议

• 索引碎片率大，影响查询的速度。如果索引碎片率在5%-30%之间，推荐重组索引；如果碎片率大于30%，推荐重建索引。

• 根据TOP SQL提供优化建议。

排查步骤

查看整体主机性能数据

通过性能监视器可以看到内存、磁盘、CPU使用情况。

查看当前进程执行情况

select * from sys.sysprocesses where spid > 50;

查看CPU使用高的 TOP SQL

SELECT TOP 50
[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
qs.execution_count,
[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total I/O] = total_logical_reads + total_logical_writes,
Query = SUBSTRING(   qt.[text],
                         (qs.statement_start_offset / 2) + 1,
                         ((CASE qs.statement_end_offset
                              WHEN -1 THEN
                              DATALENGTH(qt.[text])
                              ELSE
                              qs.statement_end_offset
                              END - qs.statement_start_offset
                             ) / 2
                            ) + 1
                        ),
Batch = qt.[text],
[DB] = DB_NAME(qt.[dbid]),
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.execution_count > 5; --more than 5 occurences ORDER BY [Total MultiCore/CPU time(sec)] DESC;

查看每个数据库的缓存页计数

SELECT COUNT(*) * 8 / 1024 AS 'cache size(MB)',
       CASE database_id
           WHEN 32767 THEN
               'ResourceDb'
           ELSE
               DB_NAME(database_id)
       END AS 'datebase'
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id),
         database_id
ORDER BY 'cache size(MB)' DESC;


SELECT COUNT(*)AS cached_pages_count  
    ,CASE database_id   
        WHEN 32767 THEN 'ResourceDb'   
        ELSE db_name(database_id)   
        END AS database_name  
FROM sys.dm_os_buffer_descriptors  
GROUP BY DB_NAME(database_id) ,database_id  
ORDER BY cached_pages_count DESC;  

查看缓存命中率情况

SELECT counter_name AS CounterName,
       (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
    JOIN
    (
        SELECT cntr_value,
               object_name
        FROM sys.dm_os_performance_counters
        WHERE counter_name = 'Buffer cache hit ratio base'
              AND object_name LIKE '%Buffer Manager%'
    ) b
        ON a.object_name = b.object_name
WHERE a.counter_name = 'Buffer cache hit ratio'
      AND a.object_name LIKE '%Buffer Manager%';

查看数据库IO使用情况

SELECT DB_NAME(fs.database_id) AS [Database Name],
       CAST(fs.io_stall_read_ms / (1.0 + fs.num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms],
       CAST(fs.io_stall_write_ms / (1.0 + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms],
       CAST((fs.io_stall_read_ms + fs.io_stall_write_ms) / (1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms],
       CONVERT(DECIMAL(18, 2), mf.size / 128.0) AS [File Size (MB)],
       mf.physical_name,
       mf.type_desc,
       fs.io_stall_read_ms,
       fs.num_of_reads,
       fs.io_stall_write_ms,
       fs.num_of_writes,
       fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls],
       fs.num_of_reads + fs.num_of_writes AS [total_io],
       io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)],
       io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf WITH (NOLOCK)
        ON fs.database_id = mf.database_id
           AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC
OPTION (RECOMPILE);

结合上述排查以及性能数据分析，判断实例瓶颈，进行优化