SQL Server performance tuning

SQL Server performance tuning.

資料庫效能調教 — DMV 的進階用法

Reference: http://www.qa-knowhow.com/?p=422

只有用過一次的快取計畫 cached plan

如果出現大量的 Cached plan 多半只有使用一次的化，那麼可以考慮設定 optimize for ad hoc workloads

SELECT TOP(20) 
   [text] AS [QueryText], 
   cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
   AND cp.objtype = N'Adhoc'
   AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);

最常被執行的 query

透過了解最常被執行的query，可以進一步針對這些 query 進行效能調教

SELECT 
   qs.execution_count, 
   qs.total_rows, 
   qs.last_rows, 
   qs.min_rows, 
   qs.max_rows,
   qs.last_elapsed_time, 
   qs.min_elapsed_time, 
   qs.max_elapsed_time,
   SUBSTRING(qt.TEXT,qs.statement_start_offset / 2 + 1,
      (
         CASE WHEN qs.statement_end_offset = -1
         THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
         ELSE qs.statement_end_offset END - qs.statement_start_offset
      ) / 2
   ) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

Missing indexes

SELECT 
   user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
   migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
   mid.equality_columns, mid.inequality_columns, mid.included_columns,
   migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost,
   migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
   INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
      ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
      ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance
ORDER BY index_advantage DESC OPTION (RECOMPILE);

last_user_seek: 這個欄位可以告訴我們最近一次使用的 index 的時間。如果時間太久之前，有可能該query 只是臨時一次性的，因此不一定需要建立 index。

user_seeks + avg_user_impact + avg_total_user_cost: 這幾個欄位可以幫助我們綜合判斷建立該 index 之後所帶來的效益與可能的節省的成本。

建立之後效能是否有顯著提升，也需要之後一段時間的觀察。同樣的在用相關的 DMV 看看SQL 效能的改變。

哪些 Index / Table 可以進行壓縮？

這個問題我們要查詢的是哪些 index /Table 經常在記憶體當中佔用最多。針對比較大的資料長期存放於 buffer pool 記憶體中的，我們就可以設定壓縮，壓縮功能適用於 Enterprise 版本，資料壓縮後，由於資料在記憶體也是保持壓縮的狀態，因此可以增加每一次 logical reads 的資料量。相對的，會比較耗用 CPU 的資源。

SELECT 
   OBJECT_NAME(p.[object_id]) AS [ObjectName],
   p.index_id, COUNT(*)/128 AS [Buffer size(MB)], 
   COUNT(*) AS [BufferCount],
   p.data_compression_desc AS [CompressionType]
FROM sys.allocation_units AS a WITH (NOLOCK)
   INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
      ON a.allocation_unit_id = b.allocation_unit_id
   INNER JOIN sys.partitions AS p WITH (NOLOCK)
      ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(int,DB_ID())
  AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id, p.data_compression_desc
ORDER BY [BufferCount] DESC OPTION (RECOMPILE);

SQL Server 在等待什麼?

http://www.qa-knowhow.com/?p=308

SELECT 
   wait_type, 
   waiting_tasks_count, 
   wait_time_ms/1000 AS wait_time_sec, 
   max_wait_time_ms/1000 AS max_wait_time_sec, 
   signal_wait_time_ms/1000 AS signal_wait_time_sec
FROM sys.dm_os_wait_stats 
ORDER BY wait_time_ms desc

特別介紹幾個欄位：

• [wait_time_ms] = CPU等待時間 + 其他資源(Disk/memory)等待時間
• [signal_wait_time_ms] = CPU 等待時間
  換句話說，

其他資源等待時間 = [wait_time_ms] – [signal_wait_time_ms]

SQL Server 定義的WaitType 有哪幾種呢? 這邊介紹幾種較為常見的

CXPACKET

CxPacket 表示因為多個CPU進行平行處理時，各個 Thread 同時執行時，整個工作完成其實需要等待到需要時間最常的 thread 完成，才算整個工作完成。

舉例來說，下面有一個 Table 需要做讀取，有因此 thread 1~4分別讀取一部分，

• Thread 0是負責管理 thread 1~4整體執行狀況。
• Thread 1-3都很快就完成，其中 Thread 4 因為讀取所需的時間比較長，

因此，當 thread 1~3 工作完成時就會造成一些等待時間，必須等待到 Thread4完成為止，對於Thread 1~3所發生的等待就是 CXPACKET。對於這種的 WaitType 通常是正常的，解決方法不完全需要把 Multiple-thread 執行取消，而是需要輔助看其他 WaitType 的情況，例如是否有 Disk I/O or Table Scan 等情況。

CxPacket 與 PageIOLatch 一起發聲的話，表示有 Table Scan，造成Table Scan 的原因可能為：

• 統計資訊的不正確
• 沒有適當的 Index
• 錯誤的 Query plan

SOS_SCHEDULER_YIELD

這種形式的 wait 主要為等待 CPU 執行，就是之前所說明的 Signal Wait，也間接說明有可能是CPU 的效能瓶頸。

LCK_*

表示有很多 Lock 發生，Lock 會造成其他的 Query 被block 無法執行，或是需要等待到取得該資源的 lock 之後才能繼續執行。因為可以進一步分析正在執行中的 SQL Query!

PAGEIOLATCH_*, IO_COMPLETION, WRITELOG

這些都與 Disk I/O 有關，但是真正的原因有可能是因為 query 的設計不良所導致，例如，Table Scan等。

PAGELATCH_*

這個主要指的是單純記憶體(buffer Cache)內部資源的等待。與 Disk I/O 完全無關。

最常見的一種例子，例如 TempDB 的存取，Query 時因為 join Table 所造成的 Hash Join or Merge Join 也會讓SQL Server 在 memory/TempDB作運算與處理。

(註：Loop Join 的運算僅發生於記憶體中)

LATCH_*

這是短期於記憶體物件間的資料存取的保護與PageLatch 不同的是，這裡的記憶體指的是 internal cache。PageLatch 的記憶體是 Buffer Cache可以配合 sys.dm_os_latch_stats 進一步查詢該 Latch 的原因與類型。

ASYNC_NETWORK_IO

這個指的是 Network IO 瓶頸。但是實務上，當這個 network IO 瓶頸發生的時候，必須要查詢是否為 client 端應用程式的關係，因此必須要讓 SQL Server 等待 client 的回覆，所以看到這類的 Wait 時，可以檢查 client 應用程式端是否有問題。

OLEDB

使用 OLEDB 回傳資料就會出現這樣的 wait type，最常見的維 DBCC 指令可能有背景程式執行 DBCC 的指令所造成。

SQL Server 效能 — File I/O

Reference: http://www.qa-knowhow.com/?p=189

哪一個資料庫存取造成最多 Disk I/O?

SELECT 
   DB_NAME(vfs.database_id) 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_writes+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_mb,
   physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
   JOIN sys.master_files mf 
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY avg_total_latency

記憶體是否被Ad-hoc Query佔滿呢?

Reference: http://www.qa-knowhow.com/?p=676

這邊介紹兩個方法可以查詢資料庫記憶體中，是否被許多 Ad-hoc query 占滿?

1. 查看Memory Consumption 報表，在伺服器連線按右鍵Management Studio >Reports > Standard Reports > Memory Consumption
   ，如果該CacheStore 的比例特別高，就表示 ad-hoc query 很多。
2. 執行select * from sys.dm_exec_cached_plans，看看大部分的 query 是否為 ObjectType = Adhoc

那要如何解決這樣的問題呢?

SQL Server 有一個設定 “optimize for ad hoc workloads ” (預設為 disable)，可以將 Ad-hoc query 的記憶體最佳化，在第一次執行的query 僅存放一點點記憶體，只有在第二次執行的 Query才將整個執行計畫儲存於記憶體。

可以透過下列指令將該設定值啟動：

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGUR

資料庫資料檔與交易檔的讀取效能

Reference: http://www.qa-knowhow.com/?p=934

當一筆資料被更新時，資料庫如何對於 database data Files (ndf/mdf)與交易資料（ldf）檔案進行讀取與寫入的過程：

1. 資料更新
   當資料庫收到 Update 的指令的時候，會開始對於資料從記憶體與磁碟讀取並且更新

2. 從磁碟讀取資料
   SQL 會判斷該讀取的資料是否可以在記憶體中 SQL buffer 取得，如果可以在記憶體中取得，則不需要到磁碟讀取。如果記憶體中沒有所需的資料，則 SQL 就會到磁碟讀取，並且存放到記憶體中 SQL Buffer。資料庫會將更新的資料寫入記憶體，也就是 SQL Buffer，這時候由於資料僅在記憶體中 (SQL Buffer)被更新，尚未寫入磁碟，因此該資料狀態稱為 Dirty Page。

3. Transaction Log (ldf)
   資料庫會立刻紀錄一筆更新交易紀錄，每一個交易紀錄都會有一個循序的 LSN (Log Sequential Number)，當異常資料庫終止時，交易紀錄可以讓資料庫回復到原本的狀態。當 “Commit”時，資料交易紀錄就會被寫入磁碟。由於交易紀錄的寫入較為頻繁，而寫讀取多半為 Sequential I/O，因此建議，Transaction log 可以存放在與 Database Data file 不同的實體磁碟。

4. 將更新資料寫入磁碟
   更新的資料會被暫時保存在 SQL Buffer 記憶體中，暫時還不會立刻寫入磁碟，一直等到 “CheckPoint”　的時間點，所有更新的資料會整批被寫入磁碟。這整個過程又稱為 Lazy Write 延遲讀寫，對於這樣的磁碟讀寫的動作通常為 Random I/O。為什麼 SQL Server 不立刻寫入資料到磁碟呢？ 因為到磁碟讀取與寫入所耗費的資源與時間遠遠大於記憶體的存取，因此，為了較好的效能，SQL Server 會盡量在記憶體中 SQL Buffer 處理，一直等到 checkPoint 時間點，才會以批次的方式整批將記憶體中 SQL Buffer 修改的資料，整批寫入磁碟檔案 (Ndf/MDF)。

資料庫的效能建議如下：

1. 建議將 Transaction Log (LDF)與 Database File (Mdf/NDF)實體的磁碟分開存放
2. LDF 可以放在磁碟效能較好的硬碟。因為交易紀錄檔的寫入較為頻繁，執行與每一筆交易有關。
3. 記憶體多多益善。當面臨昂貴的磁碟存儲設備時，增加記憶體是一個可以有效提升資料庫效能的方法。因為這可以讓資料庫許多的資料存取都在記憶體中完成，節省許多 Disk I/O 時間。

面對資料庫效能DBA 可以檢查的事

http://www.qa-knowhow.com/?p=914

伺服器健康狀況

特別是網路卡或是磁碟硬體上的問題。如果該資料庫是安裝在虛擬機器上，就必須額外看是否有惡鄰居導致資料庫效能低落。例如：磁碟空間不足或是記憶體不夠也是一個問題。

Performance Counters

建立一些 Performance Counters 的基準，這樣慢慢才會知道正常與異常的差異。這些 counters 的收集主要對於資料庫伺服器整體的效能資訊有整體的認知，例如：CPU, Memory, SQL Server workload, Disk, Networking 等效能。

不良的索引會

• 導致搜尋沒有效率 => Table Scan
• 導致錯誤的 Execution plan => 造成過多的磁碟存取 => Disk I/O
• 導致過多的資料存取 => 過多不必要的資料在記憶體中 => Memory pressure
• 導致 fragmentation 或是額外的 maintenance
• 導致 Blocking => 間接造成資料存取沒有效率與相關的 Locks

下列SQL 語句可以幫忙找出特定資料庫 (AdventureWorks2012)為例，Index的使用效率。使用效率高與比較佳的的 Index特徵是：

• user_seeks, user_scans ==> 值越高越好。Index Seeks 的效率遠大於 Index Scan
• user_updates ==> 值越低越好。因為對於 index 欄位，我們希望主要以查詢為主而非經常性的更新資料。
• last_user_seek, last_user_scan ==> 離現在的時間越近越好。因為我們不希望該 index 最近一次被用到是去年的事情。

select o.name as table_name, i.name as index_name, user_seeks, user_scans, user_updates, last_user_seek, last_user_scan,
last_user_update
from sys.dm_db_index_usage_stats s
inner join sys.objects o on s.object_id = o.object_id
inner join sys.indexes i on s.index_id = i.index_id and i.object_id = s.object_id
where s.database_id = DB_ID('AdventureWorks2012')

blocks or deadlocks

交易的死結或是blocks都會造成等待與系統變慢的主要原因，這樣的情況發生時，不管系統的配備有多好，CPU、記憶體、磁碟效能多好，資料庫的查詢都會因為 blocks 或是 deadlocks 而進入等待，等待到資源釋放。因此，每一個資料庫交易的回覆時間就會相對變得很長，特別是在多人同時使用、更新、修改資料的時候，比較會有這個現象。這段SQL 語法可以查出，那些 Query blocking 其他的 Query, 那些 Query 被 blocked。

SELECT
   Blocking.session_id as BlockingSessionId, 
   Sess.login_name AS BlockingUser, 
   BlockingSQL.text AS BlockingSQL, 
   Waits.wait_type WhyBlocked, 
   Blocked.session_id AS BlockedSessionId, 
   USER_NAME(Blocked.user_id) AS BlockedUser, 
   BlockedSQL.text AS BlockedSQL, 
   DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
   INNER JOIN sys.dm_exec_requests AS Blocked
      ON Blocking.session_id = Blocked.blocking_session_id
   INNER JOIN sys.dm_os_waiting_tasks AS Waits
      ON Blocked.session_id = Waits.session_id
   RIGHT OUTER JOIN sys.dm_exec_sessions Sess
      ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
ORDER BY BlockingSessionId, BlockedSessionId

Ac-Hoc Query 與記憶體快取

http://www.qa-knowhow.com/?tag=sql-performance&paged=5

資料庫的效能監視器

Reference: http://www.qa-knowhow.com/?p=939
Performance Counter: Performance Monitor - 效能監視器, 使用Performance Monitor監控CPU、Memory、Network與Disks

資料庫效能調教 — 資料庫的設定值

http://www.qa-knowhow.com/?p=404