性能测试：自建数据库与RDS性能对比SQL Server案例排查分析

2022-05-30 17:06:49

近期经常遇到用户将自建数据库与RDS进行对比，简单的对比结果是自建库比RDS实例查询快。我们这里来看看一个实例，有一家物流公司，刚开始使用RDS SQL Server数据库，发现通过ECS访问RDS实例，执行语句需要60s左右，但是访问ECS本地自建库只需要2-3s。那么RDS是否是真的不如自建数据库呢？接下来，我们来探讨对比自建库和RDS的正确姿势，如何公平地对比自建库和RDS的性能。

对比自建库和RDS的语句执行性能，下面这些因素必须都考虑到：

1. 可用区和网络链路。

可用区、网络链路的分析参考性能测试：自建数据库对比RDS中应当注意的地方即可，这篇文章做了深刻的分析。如果需要验证网络因素，可以在RDS中开启profiler，并且同时在客户端抓取网络包，对比RDS中执行结束的时间以及网络包中返回结果的时间，时间差异较大，说明网络传输有延迟。

针对RDS SQL Server 2012实例，可以开启SQL Server Profiler，抓取RPC:Completed, SQL:StmtStarting, SQL:StmtCompleted, SQL:BatchStarting和SQL:BatchCompleted这几个事件。

针对RDS SQL Server 2008 R2实例，暂不支持开启SQL Server Profiler，可以通过下面语句查询近期执行的语句，及其start_time和total_elapased_time 。total_elapased_time指请求到达SQL Server后执行该语句总共消耗的时间（单位ms）。

2. 实例参数配置。

MySQL实例需要关注的参数比较多，详细的分析和描述参考性能测试：自建数据库对比RDS中应当注意的地方；
SQL Server实例需要关注的参数主要有fill factor (%)，max degree of parallelism和max server memory (MB)。
Fill Factor(%):
这是一个用于调优数据存储和性能的server_side参数，当创建或者重建索引时，该值可以确定每个叶级页上要填充数据的空间百分比，以保留一些剩余空间作为以后扩展索引的可用空间。
Max Degree of Parallism（MaxDOP）：
限制并行计划执行时所用的处理器数量，即限制语句的并行度。
Max Server Memory（MB）：
设置buffer pool获取的内存的上限。

3. 资源等待或者阻塞情况

两个环境中，语句执行过程中，需要对比，是否有等待和阻塞情况发生。
查看等待情况：

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
       100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
  -- Maybe uncomment these four if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
 
        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
 
        -- Maybe uncomment these six if you have AG issues
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
 
  N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
        N'ONDEMAND_TASK_QUEUE',
        N'PREEMPTIVE_XE_GETTARGETSTATE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
  N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_RECOVERY',
        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
    )
SELECT
   MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]

INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO

查看阻塞情况，参考RDS for SQL Server 阻塞问题处理方法。

4. 两个环境中索引碎片率和统计信息是否一致。

检查索引碎片率语句如下：

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

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

检查统计信息语句如下：

SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated FROM sys.[stats] AS s JOIN sys.[tables] AS t ON [s].[object_id] = [t].[object_id] WHERE t.type = 'u'

如果发现RDS中统计信息比自建库要老旧，可以手动更新下统计信息。防止由于统计信息老旧，造成SQL Server生成不准确的执行计划，降低执行效率。

5. 高可用性架构

RDS作为一个公共的关系数据库服务，首要是保证稳定高可用，高安全，保证用户使用的是既安全又稳定的服务。然后才是高性能。
RDS SQL Server为了保证主备数据的一致性，采用的是High Safety同步模式，相对于High Performance 模式，性能有所牺牲，但是增强了高可用性，保护了数据。
同时， RDS 还提供多可用区主备实例，双节点在不同机房，更进一步保证了高可用性和安全性。

问题排查：

1. RDS实例的内存配置比本地高；
2. 网络延迟不大；
3. RDS的MAXDOP值是2，而ECS自建实例是默认值，即并行语句可以申请尽可能管那多的并行线程。
通过执行计划看出，RDS中查询语句的并行度是2，而自建库中查询语句的并行度是8，RDS中执行速度自然没有自建库快。

解决方案：

1. 上述我们分析到，RDS的配置实际比自建库高，那么可以在RDS 实例控制台的参数设置中，增加max degree of parallelism值来提升并行度。

2. 通过执行计划，还发现用户表中有Missing Index。在RDS添加了Missing Index后，查询性能有大幅度提升，即使并行度为2，也可以在5s执行完毕。

码农公寓

1. 可用区和网络链路。

2. 实例参数配置。

3. 资源等待或者阻塞情况

4. 两个环境中索引碎片率和统计信息是否一致。

5. 高可用性架构

相关文章