當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

SQL Server 性能调优（方法论）

發布時間：2024/4/15 数据库 26 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQL Server 性能调优（方法论）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

SQL Server 性能調優（方法論）

確定思路

wait event的基本troubleshooting

虛擬文件信息（virtual file Statistics）

性能指標

執行計劃緩沖的使用

總結

性能調優很難有一個固定的理論。調優本來就是處理一些特殊的性能問題。

通常一旦拿到一個服務器那么就先做一下性能檢查。查看整個數據庫是運行在什么樣的狀況下的。

分析收集的數據想像這種情況是否合理。

確定思路

一個數據庫操作的時間都是執行時間+等待時間，在無法估計執行時間的時候看要看看等待時間。

那么等待時間分為鎖等待時間和資源等待時間。

那么就先用　sys.dm_os_wait_stats動態性能視圖，查看主要的狀況。如果pageiolatch_sh等待很大，那么就說明，session在等待buffer pool的頁。當一個session要select一些數據，但是剛剛好，這些數據并沒有在buffer pool 中，那么sql server 就會分配一些緩存這些緩存是屬于buffer pool 的，用來存放從磁盤讀取出來的數據，在讀取的時候都會給這些緩存上latch（可以看成是鎖）。當存在io瓶頸的時候，那么磁盤上的數據不能立即讀到buffer pool 中就會出現等待latch的情況。這個可能是io過慢，也有可能是在做一些多余的io造成的。

那么接下來查看sys.dm_io_virtual_file_stats 性能視圖來確定哪個數據庫造成了怎么大的延遲。并且通過physical disk \avg.disk reads/sec和physical disk\avg.disk writes/sec來確定到底數據庫有多少io負載。

接下來通過 sys.dm_exec_query_stats 查看執行計劃，通過查看高物理讀的sql和執行計劃看看有沒有優化的空間。如添加索引，修改sql，優化引擎訪問數據的方法。

有可能，sql 語句已經不能再優化，但是性能還是不行，往往這種sql是報表查詢類的sql，會從磁盤中讀取大量數據，很多數據往往在buffer pool 找不到那么就會發生大量的pageiolatch_sh等待。這時，我們就要看看是否是內存不足照成的，用perfmon 查看 page life expectancy(頁壽命長度)，free list stalls/sec(等待空頁的次數)和Lazy writes/sec。?page life expectancy 波動很厲害，free liststalls/sec 一直大于0，Lazy writes/sec 的量也很大，那么就說明buffer pool 不夠大。但是也有可能是sql 寫的不嚴謹，select了很多沒必要的數據。

在上面的troubleshooting 過程中，很容易走入一個誤區，sys.dm_io_virtual_file_stats和一些性能指標，就會很容易斷定說io有問題，需要額外的預算來擴展io的性能，但是擴展io是比較貴的。io性能不理想很有可能miss index或者buffer pool的壓力造成的。如果單純的添加物理設備，但是沒有找到根本原因，當數據量增長后，依然會出現相同的問題。

waitevent的基本troubleshooting

wait statistics 是SQLOS跟蹤得到的

SQLOS 是一個偽操作系統，是SQL Server 的一部分，有調度線程，內存管理等其他操作。

SQLOS比windows調度器更好的調度sql server 線程。SQLOS的調度器間的交互，會比強占式的系統調度又更好的并發性

當sql server 等待一個sql 執行的時候，等待的時間會被sqlos捕獲，這些時間都會存放在 sys.dm_os_wait_stats性能視圖中。各種等待時間的長度，并且和其他的性能視圖，性能計數器結合，可以很明顯的看出性能問題。

對于未知的性能問題sys.dm_os_wait_stats 用來判斷性能問題是很好用的，但是在服務器重啟或者dbcc 命令清空?sys.dm_os_wait_stats后會很好分析，時間一長就很難分析，因為等待時間是累計的，搞不清楚哪個是你剛剛執行出來的時間。當然可以考慮先捕獲一份，當sql 執行完后，再捕獲一份，進行比較。

查看wait event，得到的信息只是實際性能問題的其中一個癥狀，為了更利用wait event 信息，你需要了解資源等待和非資源等待的區別，還有需要了解其他troubleshooting信息。

在sql server中有一部分的sql是沒問題的，可以使用一下sql 語句查看說有的 session的wait event

SELECT DISTINCT

wt.wait_type

FROM?sys.dm_os_waiting_tasks?AS?wt

JOIN?sys.dm_exec_sessions?AS?s?ON?wt.session_id?=?s.session_id

WHERE?s.is_user_process?=?0

因為很大一部分是正常的，所以提供了一個sql 來過濾正常查詢操作

SELECT TOP?10

wait_type?,

max_wait_time_mswait_time_ms?,

signal_wait_time_ms?,

wait_time_ms?-?signal_wait_time_ms?AS?resource_wait_time_ms?,

100.0?*?wait_time_ms?/?SUM(wait_time_ms)?OVER?( )

AS?percent_total_waits?,

100.0?*?signal_wait_time_ms?/?SUM(signal_wait_time_ms)?OVER?( )

AS?percent_total_signal_waits?,

100.0?* (?wait_time_ms?-?signal_wait_time_ms?)

/?SUM(wait_time_ms)?OVER?( )?AS?percent_total_resource_waits

FROM?sys.dm_os_wait_stats

WHERE?wait_time_ms?>?0?-- remove zerowait_time

AND?wait_type?NOT IN?-- filter outadditional irrelevant waits

(?'SLEEP_TASK',?'BROKER_TASK_STOP',?'BROKER_TO_FLUSH',

'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT',?'CLR_MANUAL_EVENT',

'LAZYWRITER_SLEEP',?'SLEEP_SYSTEMTASK',?'SLEEP_BPOOL_FLUSH',

'BROKER_EVENTHANDLER',?'XE_DISPATCHER_WAIT',?'FT_IFTSHC_MUTEX',

'CHECKPOINT_QUEUE',?'FT_IFTS_SCHEDULER_IDLE_WAIT',

'BROKER_TRANSMITTER',?'FT_IFTSHC_MUTEX',?'KSOURCE_WAKEUP',

'LAZYWRITER_SLEEP',?'LOGMGR_QUEUE',?'ONDEMAND_TASK_QUEUE',

'REQUEST_FOR_DEADLOCK_SEARCH',?'XE_TIMER_EVENT',?'BAD_PAGE_PROCESS',

'DBMIRROR_EVENTS_QUEUE',?'BROKER_RECEIVE_WAITFOR',

'PREEMPTIVE_OS_GETPROCADDRESS',?'PREEMPTIVE_OS_AUTHENTICATIONOPS',

'WAITFOR',?'DISPATCHER_QUEUE_SEMAPHORE',?'XE_DISPATCHER_JOIN',

'RESOURCE_QUEUE'?)

ORDER BY?wait_time_ms?DESC

檢查wait event一般只關注前幾個等待信息，查看高等待時間的等待類型。

CXPACKET：

? ??表明并發查詢的等待時間，通常不會立刻產生問題，也可能是因為別的性能問題，導致CXPACKET等待過高。

SOS_SCHEDULER_YIELD

? ??任務在執行的時候被調度器中斷，被放入可執行隊列等待被運行。這個時間過長可能是cpu壓力造成的。

THREADPOOL

? ??一個任務必須綁定到一個工作任務才能執行，threadpool 就是task等待被綁定的時間。出現threadpool過高可能是，cpu不夠用，也可能是大量的并發查詢。

LCK_*

? ??這中等待類型過高，說明可能session發生堵塞，可以看sys.dm_db_index_operational_stats 獲得更深入的內容

PAGEIOLATCH_*,IO_COMPLETION,WRITELOG

? ??這些往往和磁盤的io瓶頸關聯，根本原因往往都是效率極差的查詢操作消費了過多的內存。PAGEIOLATCH_*和數據庫文件的讀寫延遲相關。writelog和事務日???????????????志文件的讀寫相關。這些等待最好和sys.dm_io_virtual_file_stats 關聯確定問題是發生在數據庫，數據文件，磁盤還是整個實例。

PAGELATCH_*

? ??在buffer pool 中非io等待latch。PAGELATCH_* 大量的等待通常是分配沖突。當tempdb中大量的對象要被刪除或者創建，那么系統就會對SGAM，GAM和PFS的分配發生沖突。

LATCH_*

? ??LATCH_*和內部cache的保護，這種等待過高會發生大量的問題。可以通過?sys.dm_os_latch_stats?查看詳細內容。

ASYNC_NETWORK_IO

? ??這個等待不完全表明網絡的瓶頸。事實上多數情況下是客戶端程序一行一行的處理sql server 的結果集導致。發生這種問題那么就修改客戶端代碼。

簡單的解釋了主要的等待，減少在分析wait event 的時候走的彎路。

為了確定是否已經排除問題可以用DBCC?SQLPERF('sys.dm_os_wait_stats',?clear)清除wait event。也可以用2個wait event 信息相減。

虛擬文件信息（virtual file Statistics）

通常，當使用wait event 分析問題的時候，都為認為很想io的性能問題。但是wait event 并不能說明io是怎么發生的，所以很有可能會誤判

這就是為什么要使用sys.dm_os_latch_stats?查看的原因，可以查看累計的io統計信息，每個文件的讀寫信息，日志文件的讀寫，可以計算讀寫的比例，io等待的次數，等待的時間。

SELECT?DB_NAME(vfs.database_id)?AS?database_name?,

vfs.database_id?,

vfs.FILE_ID?,

io_stall_read_ms?/?NULLIF(num_of_reads,?0)?AS?avg_read_latency?,

io_stall_write_ms?/?NULLIF(num_of_writes,?0)

AS?avg_write_latency?,

io_stall?/?NULLIF(num_of_reads?+?num_of_writes,?0)

AS?avg_total_latency?,

num_of_bytes_read?/?NULLIF(num_of_reads,?0)

AS?avg_bytes_per_read?,

num_of_bytes_written?/?NULLIF(num_of_writes,?0)

AS?avg_bytes_per_write?,

vfs.io_stall?,

vfs.num_of_reads?,

vfs.num_of_bytes_read?,

vfs.io_stall_read_ms?,

vfs.num_of_writes?,

vfs.num_of_bytes_written?,

vfs.io_stall_write_ms?,

size_on_disk_bytes?/?1024?/?1024.?AS?size_on_disk_mbytes?,

physical_name

FROM?sys.dm_io_virtual_file_stats(NULL,NULL)?AS?vfs

JOIN?sys.master_files?AS?mf?ON?vfs.database_id?=?mf.database_id

AND?vfs.FILE_ID?=?mf.FILE_ID

ORDER BY?avg_total_latency?DESC

查看是否讀寫過大，平均延時是否過高。通過這個可以知道是否是io的問題。

如果數據文件和日志文件是共享磁盤隊列的，avg_total_latency 比預期的要高，那么就有可能是io的問題了

如果當前的數據庫是用來歸檔數據到比較慢的存儲中，可能會有很高的PAGEIOLATCH_*和io_stall那么我們就需要確定怎么高的等待是否屬于歸檔的線程，因此在troubleshooting的時候要注意你的服務器的類型。

如果你的磁盤讀寫比例是1:10，而且又很高的?avg_total_latency 那么就考慮把磁盤隊列換成 raid5，為io讀提供更多的主軸。

性能指標

在最開始的troubleshooting，性能指標是非常有用的。也可以用來驗證自己的判斷是否正確。

PLA 是一個很好的性能日志分析工具http://pal.codeplex.com.?可惜沒有中文版，當然可以去codeplex 下載源代碼自己修改。這個工具內嵌了性能收集集合，也就是通常要收集的一些性能指標。也內嵌了閥值模板，可以在性能指標收集完之后做分析。

sql server 對自己的性能指標有對應的性能視圖?sys.dm_os_performance_counters。對于性能指標有些是累計值，因此需要做2個快照，相減計算結果。

DECLARE?@CounterPrefix?NVARCHAR(30)

SET?@CounterPrefix?=?CASE WHEN?@@SERVICENAME?=?'MSSQLSERVER'

THEN?'SQLServer:'

ELSE?'MSSQL$'?+?@@SERVICENAME?+?':'

END?;

-- Capture thefirst counter set

SELECT?CAST(1?AS INT)?AS?collection_instance?,

[OBJECT_NAME]?,

counter_name?,

instance_name?,

cntr_value?,

cntr_type?,

CURRENT_TIMESTAMP?AS?collection_time

INTO?#perf_counters_init

FROM?sys.dm_os_performance_counters

WHERE?(?OBJECT_NAME?=?@CounterPrefix?+?'Access Methods'

AND?counter_name?=?'Full Scans/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Access Methods'

AND?counter_name?=?'Index Searches/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Buffer Manager'

AND?counter_name?=?'Lazy Writes/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Buffer Manager'

AND?counter_name?=?'Page lifeexpectancy'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'General Statistics'

AND?counter_name?=?'Processes Blocked'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'General Statistics'

AND?counter_name?=?'User Connections'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Locks'

AND?counter_name?=?'Lock Waits/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Locks'

AND?counter_name?=?'Lock Wait Time(ms)'

)OR (?OBJECT_NAME?=?@CounterPrefix?+?'SQL Statistics'

AND?counter_name?=?'SQLRe-Compilations/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Memory Manager'

AND?counter_name?=?'Memory GrantsPending'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'SQL Statistics'

AND?counter_name?=?'Batch Requests/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'SQL Statistics'

AND?counter_name?=?'SQLCompilations/sec'

)

-- Wait on Secondbetween data collection

WAITFOR DELAY?'00:00:01'

-- Capture thesecond counter set

SELECT?CAST(2?AS INT)?AS?collection_instance?,

OBJECT_NAME?,

counter_name?,

instance_name?,

cntr_value?,

cntr_type?,

CURRENT_TIMESTAMP?AS?collection_time

INTO?#perf_counters_second

FROM?sys.dm_os_performance_counters

WHERE?(?OBJECT_NAME?=?@CounterPrefix?+?'Access Methods'

AND?counter_name?=?'Full Scans/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Access Methods'

AND?counter_name?=?'Index Searches/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Buffer Manager'

AND?counter_name?=?'Lazy Writes/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Buffer Manager'

AND?counter_name?=?'Page lifeexpectancy'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'General Statistics'

AND?counter_name?=?'Processes Blocked'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'General Statistics'

AND?counter_name?=?'User Connections'

)OR (?OBJECT_NAME?=?@CounterPrefix?+?'Locks'

AND?counter_name?=?'Lock Waits/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Locks'

AND?counter_name?=?'Lock Wait Time(ms)'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'SQL Statistics'

AND?counter_name?=?'SQLRe-Compilations/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'Memory Manager'

AND?counter_name?=?'Memory GrantsPending'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'SQL Statistics'

AND?counter_name?=?'Batch Requests/sec'

)

OR (?OBJECT_NAME?=?@CounterPrefix?+?'SQL Statistics'

AND?counter_name?=?'SQLCompilations/sec'

)

-- Calculate thecumulative counter values

SELECT?i.OBJECT_NAME?,

i.counter_name?,

i.instance_name?,

CASE WHEN?i.cntr_type?=?272696576

THEN?s.cntr_value?-?i.cntr_value

WHEN?i.cntr_type?=?65792?THEN?s.cntr_value

END AS?cntr_value

FROM?#perf_counters_init?AS?i

JOIN?#perf_counters_second?AS?s

ON?i.collection_instance?+?1?=?s.collection_instance

AND?i.OBJECT_NAME?=?s.OBJECT_NAME

AND?i.counter_name?=?s.counter_name

AND?i.instance_name?=?s.instance_name

ORDER BY?OBJECT_NAME

-- Cleanup tables

DROP TABLE?#perf_counters_init

DROP TABLE?#perf_counters_second

主要收集一下性能指標：

??SQLServer:AccessMethods\Full Scans/sec

??SQLServer:AccessMethods\Index Searches/sec

??SQLServer:BufferManager\Lazy Writes/sec

??SQLServer:BufferManager\Page life expectancy

??SQLServer:BufferManager\Free list stalls/sec

??SQLServer:GeneralStatistics\Processes Blocked

??SQLServer:GeneralStatistics\User Connections

??SQLServer:Locks\LockWaits/sec

??SQLServer:Locks\LockWait Time (ms)

??SQLServer:MemoryManager\Memory Grants Pending

??SQLServer:SQLStatistics\Batch Requests/sec

??SQLServer:SQLStatistics\SQL Compilations/sec

??SQLServer:SQLStatistics\SQL Re-Compilations/sec

這里又2個 Access Methods 性能指標，說明了訪問數據庫不同的方式，full scans/sec 表示了發生在數據庫中索引和表掃描的次數。

如果io出現瓶頸，并且伴隨著大量的掃描出現，那么很有可能就是miss index 或者sql 代碼不理想照成的。那么多少次數到多少時可以認為有問題呢？在通常狀況下 index searches/sec 比 full scans/sec 高800-1000，如果 full sacans/sec過高，那么很有可能是miss index 和多余的io操作引起的。

Buffer Manager 和 memory manager 通常用來檢測是否存在內存壓力，lazy writes/sec，page life expectancy ,free list stalls/sec 用來佐證是否處于內存壓力。

很多網上的文章和論壇都說，如果Page Life expectancy 低于300秒的時候，存在內存壓力。但是這只是對于以前只有4g內存的服務器的，現在的服務器一般都是32g以上內存5分鐘的閥值已經不能在說明問題了。300秒雖然已經不再適用，但是我們可以用300來作為基值來計算當前的PLE的閥值 (32/4)*300 = 2400那么如果是32g的服務器設置為2400可能會比較合適。

如果PEL一直低于閥值，并且 lazy writes/sec一直很高，那么有可能是buffer pool壓力造成的。如果這個時候full scans/sec值也很高，那么請先檢查是不是miss index 或者讀取了多余的數據。

generalstatistics\processes blocked,locks\lock waits/sec和locks\lock wait time(ms)如果這3個值都是非0那么數據庫會發生堵塞。

SQL Statistics 計數器說明了sql 的編譯或者重編譯的速度，sql compilations/sec和 batch requests/sec 成正比，那么很有可能大量sql 訪問都是 ad hoc方式無法通過執行計劃緩沖優化它們，如果 SQLRe-compilations/sec 和 batch requests/sec 成正比，那么應用程序中可能又強制重新編譯的選項。

memorymanager\momory grants pending 表示等待授權內存的等待，如果這個值很高那么增加內存可能會有效果。但是也有可能是大的排序，hash操作也可能造成，可以使用調整索引或者查詢來減小這種狀況。

執行計劃緩沖的使用

執行計劃緩沖是sql server 的內部組件，可以使用 sys.dm_exec_query_stats 查詢，下面有個sql查詢物理讀前十的計劃

SELECT TOP?10

execution_count?,

statement_start_offset?AS?stmt_start_offset?,

sql_handle?,

plan_handle?,

total_logical_reads?/?execution_count?AS?avg_logical_reads?,

total_logical_writes?/?execution_count?AS?avg_logical_writes?,

total_physical_reads?/?execution_count?AS?avg_physical_reads?,

t.text

FROM?sys.dm_exec_query_stats?AS?s

CROSS APPLY?sys.dm_exec_sql_text(s.sql_handle)?AS?t

ORDER BY?avg_physical_reads?DESC

在執行計劃里面的這些值可以看出哪些查詢物理io操作很頻繁，也可以和wait event 和虛擬文件結合分析有問題的io操作。

我們也可以使用sys.dm_exec_query_plan()查看存在內存里面的執行計劃。

這里又2本書深入的講述了查詢執行計劃：《SQL Server 2008 Query performance tuningdistilled》，《Inside Microsoft SQL Server 2008:T-SQLQuerying》。

sys.dm_exec_query_stats還用來查詢 cpu時間，最長執行時間，或者最頻繁的sql

在sql server 2008中加入了2個額外的列，query_hash,query_plan_hash用來聚合相似的sql的。對于ad hoc 過大的服務器可以用來分析相似的sql，不同的編譯的總數。

總結

上面各個部分都講了一個思維，一個思路。要想性能調優調的好，那么就先系統體系結構，你要清楚如前面說的miss index 一旦發生，那么不知會影響io，還會影響內存和cpu。接下來要會分析，從一開始的簡單的性能統計信息，往下分析，用其他統計信息排除問題，得到性能問題的真正原因。

文章來源于：Troubleshooting SQL Server: A Guide for theAccidental DBA?如果看不懂的或者想更深入了解的，可以看原文。

總結

以上是生活随笔為你收集整理的SQL Server 性能调优（方法论）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Goldengate介绍
下一篇： SQL Server 性能调优（cpu）