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

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

ID-1008193

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)

 

 

Leave a Reply

Your email address will not be published.