730
性能測試：自建數據庫與RDS性能對比（SQL Server案例排查分析）
在性能測試：自建數據庫對比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 SQL Server為了保證主備數據的一致性，采用的是High Safety同步模式，相對於High Performance 模式，性能有所犧牲，但是增強了高可用性，保護了數據。
案例分享
一家物流公司，剛開始使用RDS SQL Server數據庫，發現通過ECS訪問RDS實例，執行語句需要60s左右，但是訪問ECS本地自建庫隻需要2-3s。
問題排查：
1. RDS實例的內存配置比本地高；
2. 網絡延遲不大；
3. RDS的MAXDOP值是2，而ECS自建實例是默認值，即並行語句可以申請盡可能管那多的並行線程。
通過執行計劃看出，RDS中查詢語句的並行度是2，而自建庫中查詢語句的並行度是8，RDS中執行速度自然沒有自建庫快。
通過執行計劃，還發現用戶表中有Missing Index。 在RDS添加了Missing Index後，查詢性能有大幅度提升，即使並行度為2，也可以在5s執行完畢。
最後更新：2017-05-08 11:01:17