如何得知特定時間內資料庫在忙什麼?

如何得知特定時間內資料庫在忙什麼?

這篇文章主要討論如何知道過去的特定期間內，到底資料庫在忙麼?

我們經常遇到資料庫效能的問題，這類的問題發現通常是事後使用者回報才知道。

例如：昨天下午的時段，資料庫很慢，出現很多timeout問題?

有什麼方式可以查詢昨天下午資料庫到底在忙什麼嗎? 這篇文章提供一個小技巧

資料庫在忙什麼?

可以直接執行下列 script，執行結果就會告訴這個答案.

這個例子以過去四個小時為例子，所以可以依據狀況調整 last_execution_time 的條件

[pastacode lang=”sql” message=”What was the SQL Server running?” highlight=”15″ provider=”manual”]

/* Top 50 slowest query execution in last 4 hours */

SELECT top 50 getdate() as [CurrentDateTime], DB_NAME(st.dbid) DBName
        , qs.last_execution_time
      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
      ,OBJECT_NAME(objectid,st.dbid) StoredProcedure
      ,max(cp.usecounts) execution_count
      ,sum(qs.total_elapsed_time) total_elapsed_time_in_ms
      ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time_in_ms
FROM 
        sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
        JOIN sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
WHERE 


        DB_NAME(st.dbid) is not null and qs.last_execution_time >= DATEADD(hour, -4, GETDATE())
        and st.dbid > 4


GROUP BY 
        DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
        , qs.last_execution_time, qs.total_elapsed_time, qs.execution_count
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC --sum(qs.total_elapsed_time) / max(cp.usecounts) DESC;

[/pastacode]

為什麼可以這樣查詢?

MS SQL 資料庫在執行任何查詢或是語句，都會將執行的統計資訊記錄下來。

這些統計資訊包含、執行多久、耗費多少 CPU、是否有使用Index、I/O存取、執行的次數等。這些統計資訊有助於事後資料庫效能分析使用。

這樣的統計資訊在微軟SQL資料庫稱為 DMV, “Dynamic Management View”

DMV, “Dynamic Management View”

這是一個永久儲存的 Table 嗎? 不是的!

DMV 僅是記憶體中的 Table View

因此，DMV 所有的資訊內容會在每一次資料庫重新啟動的時候被清除 reset。

因此，確認該資料庫最近一次被請動的時間就很重要。

因為，我們要確認我們所收集的DVM 統計資訊的期間，是否有涵蓋到我們遇到效能問題的時間。如果資料庫被重新啟動，那麼也會表示該DMV統計資訊全部都被清空了.

如何知道資料庫最近什麼時候被重新啟動?

資料庫重新被啟動的時候， tempDB 也會被重新created.

因此，其中一個比較簡單的方式就是看 tempDB什麼時候被建立

執行結果，查看 TempDB 的建立時間，就是該資料庫被重新啟動的時間，也就是DMV統計資訊所涵蓋的起始時間。