文档

SQL Server性能分析与优化

更新时间:
一键部署

概述

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

详细信息

说明

阿里云提醒您:

  • 如果您对实例或数据有修改、变更等风险操作,务必注意实例的容灾、容错能力,确保数据安全。

  • 如果您对实例(包括但不限于ECS、RDS)等进行配置与数据修改,建议提前创建快照或开启RDS日志备份等功能。

  • 如果您在阿里云平台授权或者提交过登录账号、密码等安全信息,建议您及时修改。

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

前提条件

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

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

  • 性能监控工具正常。

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

排查项目

  • 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使用情况。

image

查看当前进程执行情况

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);

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

  • 本页导读 (0)