如何調整 Microsoft SQL Server 的性能

已發表: 2022-03-11

為了留住用戶，任何應用程序或網站都必須快速運行。 對於關鍵任務環境，獲取信息的幾毫秒延遲可能會造成大問題。 隨著數據庫規模日益增長，我們需要盡可能快地獲取數據，並儘可能快地將數據寫回數據庫。 為了確保所有操作都能順利執行，我們必須調整數據庫服務器的性能。

在本文中，我將逐步介紹在市場上最頂級的數據庫服務器之一上進行基本性能調優的過程：Microsoft SQL Server（簡稱 SQL Server）。

#1 尋找罪魁禍首

與任何其他軟件一樣，我們需要了解 SQL Server 是一個複雜的計算機程序。 如果我們對它有問題，我們需要發現它為什麼沒有像我們預期的那樣運行。

sql服務器性能

我們需要從 SQL Server 中盡可能快速準確地提取和推送數據。 如果存在問題，有幾個基本原因以及要檢查的前兩件事是：

  • 硬件和安裝設置，可能需要更正，因為 SQL Server 需要是特定的
  • 如果我們為 SQL Server 提供了正確的 T-SQL 代碼來實現

儘管 SQL Server 是專有軟件，但 Microsoft 提供了很多方法來理解它並有效地使用它。

如果硬件沒問題，安裝已經正常，但是SQL Server還是運行緩慢，那麼首先我們需要看看是否有軟件相關的錯誤。 要檢查發生了什麼，我們需要觀察不同線程的執行情況。 這是通過計算不同線程的等待統計來實現的。 SQL Server 對每個用戶請求都使用線程，而線程只是我們複雜程序中的另一個程序，稱為 SQL Server。 需要注意的是，該線程不是安裝 SQL Server 的操作系統線程； 它與 SQLOS 線程有關，SQLOS 線程是 SQL Server 的偽操作系統。

可以使用sys.dm_os_wait_stats動態管理視圖 (DMV) 計算等待統計信息，該視圖提供有關其當前狀態的附加信息。 網上有很多腳本可以查詢這個視圖，但我最喜歡的是 Paul Randal 的腳本，因為它很容易理解，並且有觀察等待統計的所有重要參數：

 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', 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', 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'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 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_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] 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

當我們執行這個腳本時，我們需要專注於結果的最上面幾行，因為它們是最先設置的，代表了最大等待類型。

我們需要了解等待類型，以便做出正確的決定。 要了解不同的等待類型，我們可以訪問優秀的 Microsoft 文檔。

讓我們舉一個例子，我們有太多PAGEIOLATCH_XX 。 這意味著一個線程正在等待將數據頁從磁盤讀取到緩衝區中，緩衝區只不過是一個內存塊。 我們必須確保我們了解正在發生的事情。 這並不一定意味著I/O子系統不好或者內存不夠，增加I/O子系統和內存就可以解決問題，但只是暫時的。 為了找到一個永久的解決方案，我們需要了解為什麼要從磁盤讀取這麼多數據：什麼類型的 SQL 命令導致了這種情況？ 我們是否通過使用過濾器（例如where子句）讀取太多數據而不是讀取更少數據？ 是否因為表掃描或索引掃描而發生了太多的數據讀取？ 我們可以通過實現或修改現有索引將它們轉換為索引搜索嗎？ 我們是否正在編寫被 SQL Optimizer（我們 SQL 服務器程序中的另一個程序）誤解的 SQL 查詢？

我們需要從不同的角度思考，使用不同的測試用例來提出解決方案。 上述每種等待類型都需要不同的解決方案。 數據庫管理員需要在採取任何行動之前徹底研究它們。 但大多數時候，找到有問題的 T-SQL 查詢並對其進行調整將解決 60% 到 70% 的問題。

#2 查找有問題的查詢

如上所述，我們可以做的第一件事是搜索有問題的查詢。 以下 T-SQL 代碼將找到 20 個性能最差的查詢：

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

我們需要小心結果； 即使一個查詢可以有一個最大的平均運行時間，如果它只運行一次，與具有中等平均運行時間並且一天運行很多次的查詢相比，它對服務器的總體影響是低的。

#3 微調查詢

T-SQL 查詢的微調是一個重要的概念。 要了解的基本內容是我們可以如何編寫 T-SQL 查詢和實現索引，以便 SQL 優化器可以找到一個優化的計劃來做我們想要它做的事情。 在 SQL Server 的每個新版本中，我們都會獲得一個更複雜的優化器，它將覆蓋我們在編寫未優化的 SQL 查詢時的錯誤，並且還將修復與以前的優化器相關的任何錯誤。 但是，無論優化器多麼智能，如果我們不能告訴它我們想要什麼（通過編寫適當的 T-SQL 查詢），SQL 優化器將無法完成它的工作。

SQL Server 使用高級搜索和排序算法。 如果我們擅長搜索和排序算法，那麼大多數時候我們可以猜到 SQL Server 採取特定行動的原因。 了解更多和理解此類算法的最佳書籍是 Donald Knuth的《計算機編程藝術》 。

當我們檢查需要微調的查詢時，我們需要使用這些查詢的執行計劃，以便我們可以了解 SQL Server 是如何解釋它們的。

我不能在這裡涵蓋執行計劃的所有方面，但在基本層面上我可以解釋我們需要考慮的事情。

  • 首先，我們需要找出哪些運算符佔用了大部分查詢成本。
  • 如果運營商付出了很多成本，我們需要了解原因。 大多數情況下，掃描會比搜索花費更多的成本。 我們需要檢查為什麼發生特定掃描（表掃描或索引掃描）而不是索引搜索。 我們可以通過在表列上實現適當的索引來解決這個問題，但與任何復雜的程序一樣，沒有固定的解決方案。 例如，如果表很小，則掃描比查找快。
  • 大約有 78 個運算符，它們代表 SQL Server 執行計劃的各種操作和決策。 我們需要通過查閱微軟文檔來深入研究它們，以便更好地理解它們並採取適當的行動。
相關： SQL 索引解釋，Pt。 1

#4 執行計劃重用

即使我們在表上實現了適當的索引並編寫了良好的 T-SQL 代碼，如果不重用執行計劃，我們也會出現性能問題。 在對查詢進行微調之後，我們需要確保在必要時可以重用執行計劃。 如果我們重用計劃，大部分 CPU 時間將花在計算可以消除的執行計劃上。

我們可以使用下面的查詢來找出執行計劃被重用了多少次，其中usecounts表示該計劃被重用了多少次：

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

重用執行計劃的最佳方法是實現參數化存儲過程。 當我們無法實現存儲過程時，我們可以使用sp_executesql ，當對 SQL 語句的唯一更改是參數值時，可以使用它來執行 T-SQL 語句。 SQL Server 很可能會重用它在第一次執行時生成的執行計劃。

同樣，與任何復雜的計算機程序一樣，沒有固定的解決方案。 有時最好重新編制計劃。

讓我們檢查以下兩個示例查詢：

  • select name from table where name = 'sri';
  • select name from table where name = 'pal';

讓我們假設我們在name列上有一個非聚集索引，並且表的一半具有值sri並且少數行在name列中有pal 。 對於第一個查詢，SQL Server 將使用表掃描，因為一半的表具有相同的值。 但是對於第二個查詢，最好使用索引掃描，因為只有少數行具有pal值。

即使查詢相似，相同的執行計劃也可能不是好的解決方案。 大多數情況下，情況會有所不同，因此我們需要在做出決定之前仔細分析所有內容。 如果我們不想重用執行計劃，我們總是可以在存儲過程中使用“重新編譯”選項。

請記住，即使在使用存儲過程或sp_executesql之後，有時也不會重新使用執行計劃。 他們是：

  • 當查詢使用的索引更改或被刪除時
  • 當查詢使用的表的統計信息、結構或模式發生變化時
  • 當我們使用“重新編譯”選項時
  • 當有大量的插入、更新或刪除時
  • 當我們在單個查詢中混合 DDL 和 DML

#5 刪除不必要的索引

在對查詢進行微調之後，我們需要檢查索引是如何使用的。 索引維護需要大量 CPU 和 I/O。 每次我們向數據庫中插入數據時，SQL Server 也需要更新索引，所以如果不使用，最好將其刪除。

sql服務器性能

SQL 服務器為我們提供了dm_db_index_usage_stats DMV 來查找索引統計信息。 當我們運行下面的 T-SQL 代碼時，我們會得到不同索引的使用統計信息。 如果我們發現根本不使用或很少使用的索引，我們可以刪除它們以提高性能。

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 SQL Server 安裝和數據庫設置

在建立數據庫時，我們需要將數據和日誌文件分開保存。 主要原因是寫入和訪問數據文件不是順序的，而寫入和訪問日誌文件是順序的。 如果我們將它們放在同一個驅動器上，我們就無法以優化的方式使用它們。

當我們購買存儲區域網絡 (SAN) 時，供應商可能會就如何設置它給我們一些建議，但這些信息並不總是有用的。 我們需要與我們的硬件和網絡人員詳細討論如何以優化的方式分別保存數據和日誌文件。

#7 不要重載 SQL Server

任何數據庫管理員的首要任務是確保生產服務器順利運行並儘可能為客戶服務。 為了實現這一點，我們需要為以下環境維護單獨的數據庫（如果可能，在單獨的機器上）：

  • 生產
  • 發展
  • 測試
  • 分析型

對於生產數據庫，我們需要一個具有完全恢復模式的數據庫，而對於其他數據庫，一個簡單的恢復模式就足夠了。

在生產數據庫上進行測試會給事務日誌、索引、CPU 和 I/O 帶來大量負載。 這就是為什麼我們需要使用單獨的數據庫進行生產、開發、測試和分析。 如果可能，請為每個數據庫使用單獨的機器，因為它會減少 CPU 和 I/O 的負載。

#8 事務日誌、臨時數據庫和內存

日誌文件需要有足夠的可用空間來進行正常操作，因為對日誌文件的自動增長操作非常耗時，並且可能會強制其他操作等待完成。 要找出每個數據庫的日誌文件大小以及使用了多少，我們可以使用DBCC SQLPERF(logspace) 。

設置 tempdb 的最佳方法是將其放在單獨的磁盤上。 我們需要保持初始大小盡可能大，因為當它達到自動增長情況時，性能會下降。

如前所述，我們需要確保 SQL Server 在單獨的機器上運行，最好是一台沒有任何其他應用程序的機器。 我們需要為操作系統保留一些內存，如果它是集群的一部分，還需要保留一些內存，所以在大多數情況下，大約 2GB 就可以了。

對於關鍵任務環境，獲取信息的毫秒延遲可能會破壞交易。
鳴叫

結論：

此處討論的過程和建議僅用於基本性能調整。 如果我們遵循這些步驟，平均而言，我們可能會獲得大約 40% 到 50% 的性能改進。 要進行高級 SQL Server 性能調整，我們需要更深入地研究此處介紹的每個步驟。

進一步閱讀 Toptal 工程博客：

  • 使用 SQL 索引和分區解決瓶頸
  • Oracle 到 SQL Server 和 SQL Server 到 Oracle 遷移指南