資料庫效能分析的利器 — DMV
SQL Server 本身在每一個 query 執行時，都會做一定的分析與統計，
以便未來做效能分析使用，
這樣的效能分析資訊都會被存放在記憶體的 DMV (Dynamic Management View) 中
DMV 可以回答下列問題 (不限於下列列舉)?
- 那些查詢語句Query 最常被執行?
- 那些索引最常被使用或是從來沒有被用過?
- 那些索引有可能要建立卻沒有建立?
- 查詢花了多少的 IO?
- 前十大最耗時間的查詢為何?
- 資源等待的狀態為何?
- 執行最久的查詢
- 受到 Blocked的查詢query
Missing Index 分析
舉個例來說，
根據歷史以往所執行的query ，可否告訴我們有哪些 Index建議應該要建立而沒有建立的?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS [Total Weighted Cost]
, s.avg_user_impact
, d.statement AS TableName
, d.equality_columns
, d.inequality_columns
, d.included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Weighted Cost] DESC
執行結果如下，這個例子中，根據 SQL Server 的分析，建議兩個索引應該要被建立
- SalesOrderHeader.OrderDate (inequality_column)
- Customer.PersonID (equality_column)
另外，建立索引的時候，SQL 也建議該索引可以包含的資料欄位 (Included Column)
那麼什麼是 equality_column & 什麼是 inequality_column呢?
因為 SQL Server 主要是根據歷史 Where 查詢語句所用到的 query 進行分析，
- inequality_column 表示歷史有許多的 query 語句使用到
WHERE OrderDate >= ‘2003-01-01’ AND OrderDate < ‘2004-01-01’
- equality_column表示有許多query 語句使用到 =
PersonID = ‘1234’
所以什麼是 DMV (Dynamic Management View)?
DMV 就好比是資料庫系統管理者的幕僚分析師，
他會默默地分析所有 Database 的資訊，
像這個例子，DMV 會儲存分析過去以往所有執行過的 Query，並且分析索引的狀況，
供之後我們做效能調教參考的依據。
- DMV 是資料庫內部統計分析的 View 提供之後效能分析使用
- DMV統計分析資訊包含索引、Query 執行、作業系統I/O等效能資訊
- DMV資訊的累積至SQL 上次啟動或是開機時開始計算
- 80%以上的DMV都是很小的儲存或是運算，不會佔據太多的記憶體或是overhead
- 存取DMV 需要 “View Server State” “View Database State Permissions” 的權限
如何知道DMV 開始儲存的時間?
既然DMV 儲存的是累積的分析結果，那麼DMV什麼時候開始收集資訊的呢?
如果DMV 的資訊僅只有兩個星期的分析，那麼相對的就不會那麼有代表性.
如果DMV所收集分析的資訊包含過去一整年，
peak/off hours 等週期，那麼相對就很有代表性
因為DMV的資訊都存放在SQL Server 記憶體，
因此開始收集資訊的開始時間就是SQL Server 從新開機或是服務重新啟動的時間，
(除非系統管理強制手動清除DMV資訊)
我們有兩個方法可以知道DMV 開始收集資訊
1. SQL Server Log
SQL Management Studio > Management > SQL Server Logs > Current > 第一筆
SQL server 每一次重新啟動都會建立一個 Log 記錄檔，
該記錄檔的第一筆時間就是 SQL 最近一次啟動的時間，
例如，這個例子，該SQL Server 啟動的時間 為2014-10-20 09:22
2. TempDB 建立時間
用下列查詢語句可以得知每一個資料庫被建立的時間
select * from [dbo].[sysdatabases]
由於每一次重新啟動 SQL server , TempDb 就會被重新建立，
因此TempDB 建立的時間也就是 SQL Server 重新啟動的時間，
也就是 DMV 開始收集資訊的時間，
這個例子，TempDB 建立的時間為2014-10-20 09:23:25
總結
這篇文章介紹什麼是 DMV，
以及如運用DMV 來對效能的問題進行分析，以Missing index 微例
DMV 可以回答那些問題?
另外，DMV 資訊收集的時間是從上一次SQL Server restart 開始計算，
因此有兩個方法可以得知SQL Server 的啟動時間，Log 與TempDB 建立的時間。
善用DMV 所提供的資訊，可以更有效率的分析資料庫效能的狀況。