資料庫效能調教 — DMV 的進階用法
SQL server 提供許多 DMV (Dynamic Management View) ，
可以供資料庫系統管理者知道目前資料庫系統的資訊，
Query 執行的統計狀況、Index 使用狀況、效能的狀況、Disk I/O 讀取的狀況、 Memory 的使用狀況等。這篇文章主要就會探討幾個利用 DMV 來查詢我們希望的效能資訊。
記憶體狀況 Memory Clerk
這個 Query 主要可以查詢十大佔用記憶體的資源類型
主要要注意的是不是有很多的 Ad-hoc query 占用記憶體?
如果有很多 ad-hoc query，因為 資料與執行計畫快取的關係儲存在記憶體，
這個值就會比較高 “CACHESTORE_SQLCP”
SELECT TOP(10) [type] AS [Memory Clerk Type],
SUM(pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);
只有用過一次的快取計畫 cached plan
如果出現大量的 Cached plan 多半只有使用一次的化，
那麼可以考慮設定 “optimize for ad hoc workloads”
SELECT TOP(20) 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);
Logical writes最高的 query
有些 SELECT 語句可能因為需要暫存的關係，也會寫入到 temp Table
Logical write 高的可能原因為磁碟或是記憶體的效能瓶頸
因此，個別檢視這些 query ，有可能採取的行動為
1. 適當的調整磁碟設定與配置
2增加記憶體都會有幫助。
3. 建立或是移除相關的 Index
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes
AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count
AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
AS [Calls/Second],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);
Missing indexes
SQL 會在 query 執行時自動分析判斷該語句是否缺少 index
並且將該資訊儲存於 “sys.dm_db_missing_index_group_stats”
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);
線上有多少 login?
分別在平時、最忙碌等不同時期，執行這個 query，可以讓我們知道資料庫連線的狀況
例如，平時的連線大約為 500。突然如果這個值為 1000，
我們可以知道線上人數增加，當然資料庫的工作量也隨著會提升。
SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);
這篇文章介紹幾種DMV 所可以提供的 Query 查詢，
讓我們知道記憶體的狀態、最常被執行的 Query 、線上使用者連線數、索引的效率等
SQL Server 的DMV 超過 150+，之後再陸陸續續介紹其他實用的查詢，幫助資料庫管理。
(註：部分query 語句參考 MSDN and SQL Internals books)