面對資料庫效能DBA 可以檢查的事
當使用者抱怨系統慢的時候，資料庫管理師 DBA可以做哪些事情呢?
資料庫管理師會面臨對於應用系統程式碼無法掌握的挑戰
許多的應用系統可能是外包開發，或是由其他部門開發完成
對於程式碼的掌握或是進一步的修改會有一些限制
因此，要如何在面對未知的應用系統上，針對資料庫做一些效能調教便是一個挑戰
這篇主要列出，當應用程式系統整體的效能有些狀況的時候，DBA 可以做哪些檢查與思考?
伺服器健康狀況
特別是網路卡或是磁碟硬體上的問題。
如果該資料庫是安裝在虛擬機器上，就必須額外看是否有惡鄰居導致資料庫效能低落
例如：磁碟空間不足或是記憶體不夠也是一個問題
Performance Counters
建立一些 Performance Counters 的基準，這樣慢慢才會知道正常與異常的差異.
這些 counters 的收集主要對於資料庫伺服器整體的效能資訊有整體的認知
例如：CPU, Memory, SQL Server workload, Disk, Networking 等效能
筆者曾經遇過一個狀況是：
資料庫與整個應用系統在少數人的連線狀況下都是正常運作，
但是做效能測試模擬 3000人的狀況時，系統就無法反應
檢查應用程式、網站、系統都是正常
但是最後看到 SQL Database log 才發現 Out of Memory
因此效能的問題，初步還是建議先廣泛的看各個系統上的整體效能狀況
而不是直接跳到結論斷定就是哪一段程式有問題、哪一個 Web Server 有問題
索引
好的索引會對於資料庫的效能有整體的影響。怎麼說呢?
索引不只是會幫助到 SQL Query 效能，也能間接幫助到 CPU, Memory, Disk.
不良的索引會
導致搜尋沒有效率 => 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 |
小結
資料庫效能檢查項目全部就這個嗎? No
這篇文章僅介紹四個主要的觀念與方向
- 伺服器狀況
- Performance counter
- Index
- Blocks and deadlocks
每一個方向都是一個學問，筆者將在之後的文章陸續介紹。