使用動態管理檢視監視 Microsoft Azure SQL Database 效能
適用於：
Azure SQL 資料庫
Microsoft Azure SQL Database 可使用動態管理檢視的子集，來診斷可能因為封鎖或長時間執行查詢、資源瓶頸、不佳的查詢計畫等等所造成的效能問題。
本文提供如何透過 T-SQL 查詢動態管理檢視來偵測常見效能問題的相關資訊。 您可以使用任何查詢工具，例如：
權限
在 Azure SQL 資料庫，根據計算大小和部署選項，查詢 DMV 可能需要 VIEW DATABASE STATE 或 VIEW SERVER STATE 權限。 後者的權限可能會透過 ##MS_ServerStateReader## 伺服器角色的成員資格來授與。
若要將 VIEW DATABASE STATE 權限授與特定資料庫使用者，請執行下列查詢作為範例：
GRANT VIEW DATABASE STATE TO database_user;
若要將 ##MS_ServerStateReader## 伺服器角色的成員資格授與 Azure 中邏輯伺服器的登入，請連線至 master 資料庫，然後執行下列查詢作為範例：
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login];
在 SQL Server 的執行個體和 Azure SQL 受控執行個體中，動態管理檢視會傳回伺服器狀態資訊。 在 Azure SQL 資料庫中，它們僅會傳回您目前邏輯資料庫的相關資訊。
識別 CPU 效能問題
如果 CPU 耗用量長的時間高於 80%，請考慮下列疑難排解步驟，而無論 CPU 問題現在正在發生還是過去已發生。
CPU 問題現在正在發生
如果正在發生問題，有兩個可能的案例：
許多累積耗用大量 CPU 的個別查詢
使用下列查詢識別常見的查詢雜湊：
PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--'; SELECT TOP 10 GETDATE() runtime, * FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text" FROM (SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats GROUP BY query_hash) AS t ORDER BY Total_Request_Cpu_Time_Ms DESC;
耗用 CPU 的長時間執行的查詢仍在執行
使用下列查詢識別下列查詢：
PRINT '--top 10 Active CPU Consuming Queries by sessions--'; SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName', SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ORDER BY cpu_time DESC; GO
發生在過去的 CPU 問題
如果問題發生在過去，而且您想要執行根本原因分析，請使用查詢存放區。 具有資料庫存取權的使用者可以使用 T-SQL 查詢「查詢存放區」資料。 「查詢存放區」預設組態使用 1 小時的細微性。
使用下列查詢查看耗用大量 CPU 查詢的活動。 此查詢會傳回前 15 個耗用 CPU 的查詢。 請務必變更
rsi.start_time >= DATEADD(hour, -2, GETUTCDATE()：-- Top 15 CPU consuming queries by query hash -- Note that a query hash can have many query ids if not parameterized or not parameterized properly WITH AggregatedCPU AS ( SELECT q.query_hash ,SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms ,SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms ,MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms ,MAX(max_logical_io_reads) max_logical_reads ,COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans ,COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids ,SUM(CASE WHEN rs.execution_type_desc = 'Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count ,SUM(CASE WHEN rs.execution_type_desc = 'Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count ,SUM(CASE WHEN rs.execution_type_desc = 'Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count ,SUM(count_executions) AS total_executions ,MIN(qt.query_sql_text) AS sampled_query_text FROM sys.query_store_query_text AS qt INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id WHERE rs.execution_type_desc IN ('Regular','Aborted','Exception') AND rsi.start_time >= DATEADD(HOUR, - 2, GETUTCDATE()) GROUP BY q.query_hash ) ,OrderedCPU AS ( SELECT query_hash ,total_cpu_ms ,avg_cpu_ms ,max_cpu_ms ,max_logical_reads ,number_of_distinct_plans ,number_of_distinct_query_ids ,total_executions ,Aborted_Execution_Count ,Regular_Execution_Count ,Exception_Execution_Count ,sampled_query_text ,ROW_NUMBER() OVER ( ORDER BY total_cpu_ms DESC ,query_hash ASC ) AS query_hash_row_number FROM AggregatedCPU ) SELECT OD.query_hash ,OD.total_cpu_ms ,OD.avg_cpu_ms ,OD.max_cpu_ms ,OD.max_logical_reads ,OD.number_of_distinct_plans ,OD.number_of_distinct_query_ids ,OD.total_executions ,OD.Aborted_Execution_Count ,OD.Regular_Execution_Count ,OD.Exception_Execution_Count ,OD.sampled_query_text ,OD.query_hash_row_number FROM OrderedCPU AS OD WHERE OD.query_hash_row_number <= 15 --get top 15 rows by total_cpu_ms ORDER BY total_cpu_ms DESC;一旦您識別有問題的查詢之後，就可以調整這些查詢，以減少 CPU 使用率。 如果您沒有時間微調查詢，也可以選擇升級資料庫的 SLO，以解決此問題。
如需在 Azure SQL 資料庫中處理 CPU 效能問題的詳細資訊，請參閱對 Azure SQL 資料庫上的高 CPU 進行診斷和疑難排解。
識別 I/O 效能問題
識別儲存體輸入/輸出 (I/O) 效能問題時，與 I/O 問題相關的最高等候類型如下：
PAGEIOLATCH_*資料檔案 I/O 問題 (包括
PAGEIOLATCH_SH、PAGEIOLATCH_EX、PAGEIOLATCH_UP)。 如果等候類型名稱中有 IO，則指向 I/O 問題。 如果在頁面閂鎖等候名稱中沒有任何 IO，就會指向不同類型的問題 (例如，tempdb競爭)。WRITE_LOG交易記錄 I/O 問題。
如果正在發生 I/O 問題
使用 sys.dm_exec_requests 或 sys.dm_os_waiting_tasks 查看 wait_type 和 wait_time。
識別資料並記錄 I/O 使用率
使用下列查詢，識別資料並記錄 I/O 使用率。 如果資料或記錄 I/O 高於 80%，則表示使用者已針對 Azure SQL 資料庫服務層級使用可用的 I/O。
SELECT
database_name = DB_NAME()
, UTC_time = end_time
, 'CPU Utilization In % of Limit' = rs.avg_cpu_percent
, 'Data IO In % of Limit' = rs.avg_data_io_percent
, 'Log Write Utilization In % of Limit' = rs.avg_log_write_percent
, 'Memory Usage In % of Limit' = rs.avg_memory_usage_percent
, 'In-Memory OLTP Storage in % of Limit' = rs.xtp_storage_percent
, 'Concurrent Worker Threads in % of Limit' = rs.max_worker_percent
, 'Concurrent Sessions in % of Limit' = rs.max_session_percent
FROM sys.dm_db_resource_stats AS rs --past hour only
ORDER BY rs.end_time DESC;
如需使用 sys.dm_db_resource_stats 的更多範例，請參閱本文稍後的監視資源使用率一節。
如果已達到 I/O 限制，則會有兩個選項：
- 升級計算大小或服務層級
- 識別並微調耗用最多 I/O 的查詢。
使用「查詢存放區」檢視與緩衝區相關的 I/O
對於選項 2，您可以針對「查詢存放區」，將下列查詢用於與緩衝區相關的 I/O，以檢視過去兩個小時追蹤的活動：
-- Top queries that waited on buffer
-- Note these are finished queries
WITH Aggregated AS (SELECT q.query_hash, SUM(total_query_wait_time_ms) total_wait_time_ms, SUM(total_query_wait_time_ms / avg_query_wait_time_ms) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text, MIN(wait_category_desc) AS wait_category_desc
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
INNER JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
INNER JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=waits.runtime_stats_interval_id
WHERE wait_category_desc='Buffer IO' AND rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
GROUP BY q.query_hash), Ordered AS (SELECT query_hash, total_executions, total_wait_time_ms, sampled_query_text, wait_category_desc, ROW_NUMBER() OVER (ORDER BY total_wait_time_ms DESC, query_hash ASC) AS query_hash_row_number
FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.query_hash_row_number
FROM Ordered AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_wait_time_ms
ORDER BY total_wait_time_ms DESC;
GO
檢視 WRITELOG 等候的總記錄 I/O
如果等候類型是 WRITELOG，使用下列查詢可依陳述式檢視總記錄 I/O：
-- Top transaction log consumers
-- Adjust the time window by changing
-- rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
WITH AggregatedLogUsed
AS (SELECT q.query_hash,
SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms,
SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms,
SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms,
MAX(max_logical_io_reads) max_logical_reads,
COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans,
COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids,
SUM( CASE
WHEN rs.execution_type_desc = 'Aborted' THEN
count_executions
ELSE 0
END
) AS Aborted_Execution_Count,
SUM( CASE
WHEN rs.execution_type_desc = 'Regular' THEN
count_executions
ELSE 0
END
) AS Regular_Execution_Count,
SUM( CASE
WHEN rs.execution_type_desc = 'Exception' THEN
count_executions
ELSE 0
END
) AS Exception_Execution_Count,
SUM(count_executions) AS total_executions,
MIN(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rs.execution_type_desc IN ( 'Regular', 'Aborted', 'Exception' )
AND rsi.start_time >= DATEADD(HOUR, -2, GETUTCDATE())
GROUP BY q.query_hash),
OrderedLogUsed
AS (SELECT query_hash,
total_log_bytes_used,
number_of_distinct_plans,
number_of_distinct_query_ids,
total_executions,
Aborted_Execution_Count,
Regular_Execution_Count,
Exception_Execution_Count,
sampled_query_text,
ROW_NUMBER() OVER (ORDER BY total_log_bytes_used DESC, query_hash ASC) AS query_hash_row_number
FROM AggregatedLogUsed)
SELECT OD.total_log_bytes_used,
OD.number_of_distinct_plans,
OD.number_of_distinct_query_ids,
OD.total_executions,
OD.Aborted_Execution_Count,
OD.Regular_Execution_Count,
OD.Exception_Execution_Count,
OD.sampled_query_text,
OD.query_hash_row_number
FROM OrderedLogUsed AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_log_bytes_used
ORDER BY total_log_bytes_used DESC;
GO
識別 tempdb 效能問題
識別 I/O 效能問題時，與 tempdb 問題相關聯的最高等候類型為 PAGELATCH_* (而非 PAGEIOLATCH_*)。 不過，PAGELATCH_* 等候不一定表示您有 tempdb 爭用。 這個等候也表示您有使用者物件資料頁面爭用，因為針對相同的資料頁面進行並行要求。 若要進一步確認 tempdb 競爭，請使用 sys.dm_exec_requests 確認 wait_resource 值開頭為 2:x:y，其中 2 tempdb 是資料庫識別碼、x 是檔案識別碼，而 y 是分頁識別碼。
對於 tempdb 競爭，常見的方法是減少或重新撰寫依賴 tempdb 的應用程式程式碼。 常見的 tempdb 使用區域包括：
- 暫存資料表
- 資料表變數
- 資料表值參數
- 版本存放區使用量 (與長時間執行的交易有關)
- 具有使用排序、雜湊聯結和多工緩衝處理之查詢計劃的查詢
如需詳細資訊，請參閱 Azure SQL 中的 tempdb。
使用資料表變數和暫存資料表的常見查詢
使用下列查詢識別使用資料表變數和暫存資料表的常見查詢：
SELECT plan_handle, execution_count, query_plan
INTO #tmpPlan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle);
GO
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT plan_handle, stmt.stmt_details.value('@Database', 'varchar(max)') AS 'Database'
, stmt.stmt_details.value('@Schema', 'varchar(max)') AS 'Schema'
, stmt.stmt_details.value('@Table', 'varchar(max)') AS 'table'
INTO #tmp2
FROM
(SELECT CAST(query_plan AS XML) sqlplan, plan_handle FROM #tmpPlan) AS p
CROSS APPLY sqlplan.nodes('//sp:Object') AS stmt(stmt_details);
GO
SELECT t.plan_handle, [Database], [Schema], [table], execution_count
FROM
(SELECT DISTINCT plan_handle, [Database], [Schema], [table]
FROM #tmp2
WHERE [table] LIKE '%@%' OR [table] LIKE '%#%') AS t
INNER JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;
GO
DROP TABLE #tmpPlan
DROP TABLE #tmp2
識別長時間執行的交易
使用下列查詢識別長時間執行的交易。 長時間執行的交易可防止持續版本存放區 (PVS) 清除。 如需詳細資訊，請參閱針對加速資料庫復原進行疑難排解。
SELECT DB_NAME(dtr.database_id) 'database_name',
sess.session_id,
atr.name AS 'tran_name',
atr.transaction_id,
transaction_type,
transaction_begin_time,
database_transaction_begin_time,
transaction_state,
is_user_transaction,
sess.open_transaction_count,
TRIM(REPLACE(
REPLACE(
SUBSTRING(
SUBSTRING(
txt.text,
(req.statement_start_offset / 2) + 1,
((CASE req.statement_end_offset
WHEN -1 THEN
DATALENGTH(txt.text)
ELSE
req.statement_end_offset
END - req.statement_start_offset
) / 2
) + 1
),
1,
1000
),
CHAR(10),
' '
),
CHAR(13),
' '
)
) Running_stmt_text,
recenttxt.text 'MostRecentSQLText'
FROM sys.dm_tran_active_transactions AS atr
INNER JOIN sys.dm_tran_database_transactions AS dtr
ON dtr.transaction_id = atr.transaction_id
LEFT JOIN sys.dm_tran_session_transactions AS sess
ON sess.transaction_id = atr.transaction_id
LEFT JOIN sys.dm_exec_requests AS req
ON req.session_id = sess.session_id
AND req.transaction_id = sess.transaction_id
LEFT JOIN sys.dm_exec_connections AS conn
ON sess.session_id = conn.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt
OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS recenttxt
WHERE atr.transaction_type != 2
AND sess.session_id != @@spid
ORDER BY start_time ASC;
識別記憶體授與等候效能問題
如果您的常見等候類型為 RESOURCE_SEMAPHORE，而且沒有高 CPU 使用量問題，則可能有記憶體授與等候問題。
判斷 RESOURCE_SEMAPHORE 等候是否為最高等候
使用下列查詢判斷 RESOURCE_SEMAPHORE 等候是否為最高等候。 這也表示，在最新歷程記錄中，等待時間的排名上升到 RESOURCE_SEMAPHORE。 如需針對記憶體授與等候問題進行疑難排解的詳細資訊，請參閱疑難排解 SQL Server 中記憶體授與所造成的效能緩慢或記憶體不足問題。
SELECT wait_type,
SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
INNER JOIN sys.dm_exec_sessions AS sess
ON req.session_id = sess.session_id
WHERE is_user_process = 1
GROUP BY wait_type
ORDER BY SUM(wait_time) DESC;
識別高記憶體耗用的陳述式
如果在 Azure SQL 資料庫中發生記憶體不足的錯誤，請檢閱 sys.dm_os_out_of_memory_events。 如需詳細資訊，請參閱針對 Azure SQL 資料庫中的記憶體不足錯誤進行疑難排解。
首先，修改下列腳本以更新 和end_time的相關值start_time。 然後，執行下列查詢，以識別高記憶體取用陳述式：
SELECT IDENTITY(INT, 1, 1) rowId,
CAST(query_plan AS XML) query_plan,
p.query_id
INTO #tmp
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS r
ON p.plan_id = r.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS i
ON r.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE start_time > '2018-10-11 14:00:00.0000000'
AND end_time < '2018-10-17 20:00:00.0000000';
GO
;WITH cte
AS (SELECT query_id,
query_plan,
m.c.value('@SerialDesiredMemory', 'INT') AS SerialDesiredMemory
FROM #tmp AS t
CROSS APPLY t.query_plan.nodes('//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]') AS m(c) )
SELECT TOP 50
cte.query_id,
t.query_sql_text,
cte.query_plan,
CAST(SerialDesiredMemory / 1024. AS DECIMAL(10, 2)) SerialDesiredMemory_MB
FROM cte
INNER JOIN sys.query_store_query AS q
ON cte.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;
識別前 10 個有效記憶體授與
使用下列查詢識別前 10 個有效記憶體授與：
SELECT TOP 10
CONVERT(VARCHAR(30), GETDATE(), 121) AS runtime,
r.session_id,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.writes,
r.logical_reads,
r.row_count,
wait_time,
wait_type,
r.command,
OBJECT_NAME(txt.objectid, txt.dbid) 'Object_Name',
TRIM(REPLACE(REPLACE(SUBSTRING(SUBSTRING(TEXT, (r.statement_start_offset / 2) + 1,
( (
CASE r.statement_end_offset
WHEN - 1
THEN DATALENGTH(TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1), 1, 1000), CHAR(10), ' '), CHAR(13), ' ')) AS stmt_text,
mg.dop, --Degree of parallelism
mg.request_time, --Date and time when this query requested the memory grant.
mg.grant_time, --NULL means memory has not been granted
mg.requested_memory_kb / 1024.0 requested_memory_mb, --Total requested amount of memory in megabytes
mg.granted_memory_kb / 1024.0 AS granted_memory_mb, --Total amount of memory actually granted in megabytes. NULL if not granted
mg.required_memory_kb / 1024.0 AS required_memory_mb, --Minimum memory required to run this query in megabytes.
max_used_memory_kb / 1024.0 AS max_used_memory_mb,
mg.query_cost, --Estimated query cost.
mg.timeout_sec, --Time-out in seconds before this query gives up the memory grant request.
mg.resource_semaphore_id, --Non-unique ID of the resource semaphore on which this query is waiting.
mg.wait_time_ms, --Wait time in milliseconds. NULL if the memory is already granted.
CASE mg.is_next_candidate --Is this process the next candidate for a memory grant
WHEN 1 THEN
'Yes'
WHEN 0 THEN
'No'
ELSE
'Memory has been granted'
END AS 'Next Candidate for Memory Grant',
qp.query_plan
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_query_memory_grants AS mg
ON r.session_id = mg.session_id
AND r.request_id = mg.request_id
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY mg.granted_memory_kb DESC;
監視連線
您可以使用 sys.dm_exec_connections 檢視，以擷取對特定資料庫或彈性集區所建立連線的相關資訊，還有每個連線的詳細資料。 此外，sys.dm_exec_sessions 檢視有助於擷取所有作用中使用者的連接資訊和內部工作。
檢視目前的工作階段
下列查詢可擷取目前的連接資訊。 若要檢視所有工作階段，請移除 WHERE 子句。
在執行 sys.dm_exec_requests 和 sys.dm_exec_sessions 檢視時，只有資料庫擁有 VIEW DATABASE STATE 權限時，才會在資料庫上看到所有執行工作階段。 否則，您僅會看到目前的工作階段。
SELECT
c.session_id, c.net_transport, c.encrypt_option,
c.auth_scheme, s.host_name, s.program_name,
s.client_interface_name, s.login_name, s.nt_domain,
s.nt_user_name, s.original_login_name, c.connect_time,
s.login_time
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE c.session_id = @@SPID; --Remove to view all sessions, if permissions allow
監視資源使用量
您可以在 Azure 入口網站或查詢存放區中使用 SQL Database 查詢效能深入解析，以在查詢層級上監視 Azure SQL 資料庫資源使用狀況。
您也可以使用這些檢視來監視使用狀況：
sys.dm_db_resource_stats
您可以在每一個資料庫中使用 sys.dm_db_resource_stats 檢視表。 sys.dm_db_resource_stats 檢視可顯示相對於服務層級的最新資源使用量資料。 每隔 15 秒鐘就會記錄一次 CPU、資料 I/O、記錄檔寫入和記憶體的平均百分比，並且會維持 1 小時。
因為此檢視會提供更細微的資源使用量資訊，請先使用 sys.dm_db_resource_stats 來進行任何現狀分析或疑難排解。 例如，下列查詢會顯示目前的資料庫在過去一小時的平均和最大資源使用量：
SELECT
Database_Name = DB_NAME(),
tier_limit = COALESCE(rs.dtu_limit, cpu_limit), --DTU or vCore limit
AVG(avg_cpu_percent) AS 'Average CPU use in percent',
MAX(avg_cpu_percent) AS 'Maximum CPU use in percent',
AVG(avg_data_io_percent) AS 'Average data IO in percent',
MAX(avg_data_io_percent) AS 'Maximum data IO in percent',
AVG(avg_log_write_percent) AS 'Average log write use in percent',
MAX(avg_log_write_percent) AS 'Maximum log write use in percent',
AVG(avg_memory_usage_percent) AS 'Average memory use in percent',
MAX(avg_memory_usage_percent) AS 'Maximum memory use in percent'
FROM sys.dm_db_resource_stats AS rs --past hour only
GROUP BY rs.dtu_limit, rs.cpu_limit;
如需其他查詢的資訊，請參閱 sys.dm_db_resource_stats 中的範例。
sys.resource_stats
master 資料庫中的 sys.resource_stats 檢視有其他資訊，可協助您監視資料庫在其特定服務層級和計算大小上的效能。 每隔 5 分鐘就會收集一次資料，並且會維持大約 14 天。 此檢視適合用來對資料庫如何使用資源進行長期歷程分析。
下圖顯示了具有 P2 計算大小的進階版資料庫一週內每小時的 CPU 資源使用情況。 此圖表從週一開始，顯示了五個工作日，然後顯示週末，此時應用程式是使用率要少得多。
根據資料情況，此資料庫目前尖峰 CPU 負載相對於 P2 計算大小的 CPU 使用率略高於 50% (週二中午)。 如果 CPU 是應用程式資源設定檔中的主要因素，則您可決定 P2 是正確的計算大小，以保證工作負載始終符合。 如果您預期應用程式會隨著時間成長，最好有額外的資源緩衝，以便應用程式始終不會達到效能層級限制。 如果您增加計算大小，您可以協助避免當資料庫沒有足夠能力有效處理要求時，可能發生的客戶可見錯誤，特別是在對延遲敏感的環境中。 例如，資料庫可支援根據資料庫呼叫結果繪製網頁的應用程式。
其他應用程式類型可能會以不同的方式解譯相同圖形。 例如，如果應用程式每天嘗試處理薪資資料，而且具有相同圖表，這種「批次工作」模型可能會在 P1 計算大小上正常運作。 P1 計算大小具有 100 個 DTU，而 P2 計算大小則為 200 個 DTU。 P1 計算大小提供的效能僅為 P2 計算大小的一半。 因此，P2 中 50% 的 CPU 使用量等於 P1 中的 100% CPU 使用量。 如果應用程式沒有逾時，那麼是要耗費 2 小時或 2.5 小時完成工作，還是要今天完成，都可能並不重要。 此類別中的應用程式可能會使用 P1 計算大小。 您可以利用這一事實，即一天中的某一時間段資源使用量較低，因此在當天低點之後都可能會出現任何「高點」。 P1 計算大小可能適合這類應用程式 (並節省成本)，只要工作可以每天準時完成。
資料庫引擎會在每個伺服器中 master 資料庫的 sys.resource_stats 檢視中，公開每個使用中的資料庫的已耗用資源資訊。 資料表中的資料會以 5 分鐘為間隔進行彙總。 對於基本、標準和進階版服務層級，可能需要超過 5 分鐘的時間，資料才會出現在資料表中，因此相較於近即時分析，該資料更適合用於歷程記錄分析。 查詢 sys.resource_stats 檢視，以查看資料庫的最新歷程記錄，並驗證您選擇的保留是否在需要時提供了您想要的效能。
注意
在 Azure SQL 資料庫上，您必須連線到 master 資料庫，才能在下列範例中查詢 sys.resource_stats。
此範例示範如何公開此檢視中的資料：
SELECT TOP 10 *
FROM sys.resource_stats
WHERE database_name = 'userdb1'
ORDER BY start_time DESC;
下一個範例顯示以不同方式使用 sys.resource_stats 目錄檢視，以取得資料庫如何使用資源的相關資訊：
若要查看使用者資料庫
userdb1過去一週的資源使用，您可以執行此查詢，並替換您自己的資料庫名稱：SELECT * FROM sys.resource_stats WHERE database_name = 'userdb1' AND start_time > DATEADD(day, -7, GETDATE()) ORDER BY start_time DESC;若要評估您的工作負載與計算大小的符合程度，您必須鑽研資源度量的每個層面：CPU、讀取、寫入、背景工作角色數目和工作階段數目。 以下是使用
sys.resource_stats修訂過的查詢，可報告這些資源計量的平均值和最大值，針對已佈建資料庫的每個服務層級：SELECT rs.database_name , rs.sku , storage_mb = MAX(rs.Storage_in_megabytes) , 'Average CPU Utilization In %' = AVG(rs.avg_cpu_percent) , 'Maximum CPU Utilization In %' = MAX(rs.avg_cpu_percent) , 'Average Data IO In %' = AVG(rs.avg_data_io_percent) , 'Maximum Data IO In %' = MAX(rs.avg_data_io_percent) , 'Average Log Write Utilization In %' = AVG(rs.avg_log_write_percent) , 'Maximum Log Write Utilization In %' = MAX(rs.avg_log_write_percent) , 'Average Requests In %' = AVG(rs.max_worker_percent) , 'Maximum Requests In %' = MAX(rs.max_worker_percent) , 'Average Sessions In %' = AVG(rs.max_session_percent) , 'Maximum Sessions In %' = MAX(rs.max_session_percent) FROM sys.resource_stats AS rs WHERE rs.database_name = 'userdb1' AND rs.start_time > DATEADD(day, -7, GETDATE()) GROUP BY rs.database_name, rs.sku;藉助每項資源計量的平均值和最大值的相關資訊，您可以評估您的工作負載與計算大小的符合程度。 通常，
sys.resource_stats的平均值可提供良好的基準，以針對目標大小來使用。 它應該是您的主要測量搖桿。對於 DTU 購買模型資料庫：
例如，您可能使用具有 S2 計算大小的標準服務層級。 CPU 和 I/O 讀取和寫入的平均使用百分比低於 40%，背景工作角色的平均數目低於 50，而工作階段的平均數目低於 200。 您的工作負載可能符合 S1 計算大小。 您可以輕鬆地查看資料庫是否符合背景工作角色和工作階段限制。 若要查看資料庫是否符合較低的計算大小，請將較低計算大小的 DTU 數目除以目前計算大小的 DTU 數目，然後將結果乘以 100：
S1 DTU / S2 DTU * 100 = 20 / 50 * 100 = 40所得結果即為兩個計算大小之間的相對效能差異，以百分比表示。 如果您的資源使用量未超過此數量，您的工作負載可能符合較低的計算大小。 不過，您需要查看資源使用值的所有範圍，並依百分比判斷資料庫工作負載符合較低計算大小的頻率。 下列查詢會根據我們在本範例中計算的 40% 閾值，輸出每個資源維度的符合百分比：
SELECT database_name, 100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent' FROM sys.resource_stats WHERE start_time > DATEADD(day, -7, GETDATE()) AND database_name = 'sample' --remove to see all databases GROUP BY database_name;根據您的資料庫服務層級，您可以決定您的工作負載是否符合較低的計算大小。 如果您的資料庫工作負載目標為 99.9%，而上述查詢針對所有三個資源維度傳回的值均大於 99.9%，則您的工作負載可能會符合較低的計算大小。
查看符合百分比也可讓您深入了解是否應該移至下一個較高的計算大小，以符合您的目標。 例如，過去一週範例資料庫的 CPU 使用量：
平均 CPU 百分比 最大 CPU 百分比 24.5 100.00 平均 CPU 大約是計算大小限制的四分之一，這很符合資料庫的計算大小。
對於 DTU 購買模型和虛擬核心購買模型資料庫：
最大值顯示，資料庫達到計算大小的限制。 您是否需要移至下一個較高的計算大小？ 查看工作負載達到 100% 的次數，然後將其與資料庫工作負載目標進行比較。
SELECT database_name, 100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent' FROM sys.resource_stats WHERE start_time > DATEADD(day, -7, GETDATE()) AND database_name = 'sample' --remove to see all databases GROUP BY database_name;這些百分比是符合目前計算大小的工作負載範例數目。 如果此查詢針對三個資源維度中任何一個傳回的值小於 99.9%，則取樣的平均工作負載會超過限制。 請考慮移至下一個較高的計算大小或使用應用程式微調技術，來減少資料庫的負載。
注意
針對彈性集區，您可以使用本節所述的技術，來監視集區中的個別資料庫。 您也可以監視整個集區。 如需詳細資訊，請參閱監視和管理彈性集區。
並行要求數上限
若要查看目前的並行要求數目，請在使用者資料庫上執行下列查詢：
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R;
若要分析資料庫的工作負載，請修改此查詢來篩選您要分析的特定資料庫。 首先，將資料庫的名稱從 MyDatabase 更新為所需的資料庫，然後執行下列查詢，以尋找該資料庫中並行要求的計數：
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R
INNER JOIN sys.databases AS D
ON D.database_id = R.database_id
AND D.name = 'MyDatabase';
這只是單一時間點的快照。 若要進一步了解您的工作負載和並行要求需求，您必須收集一段時間內的許多範例。
並行登入事件數目上限
您可以分析您的使用者和應用程式模式，以了解登入事件的頻率。 您也可以在測試環境中執行真實世界的負載，藉此確定您不會達到我們在本文中討論的這項限制或其他限制。 單一查詢或動態管理檢視 (DMV) 無法顯示並行登入計數或歷程記錄。
如果這些用戶端使用相同的連接字串，服務仍會驗證每一個登入。 如果有 10 位使用者同時以相同的使用者名稱和密碼連接到資料庫，將會有 10 個並行登入。 這項限制只適用於登入和驗證期間。 如果相同的 10 位使用者依序連接到資料庫，並行登入數目絕對不會大於 1。
注意
目前，此限制並不適用於彈性集區中的資料庫。
工作階段數上限
若要查看目前作用中的工作階段數目，請在資料庫上執行下列查詢：
SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections;
如果您分析 SQL Server 工作負載，請修改查詢以專注於特定資料庫。 如果您考慮將資料庫移至 Azure，此查詢可協助您判斷該資料庫可能的工作階段需求。 首先，將資料庫的名稱從 MyDatabase 更新為所需的資料庫，然後執行下列查詢：
SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections AS C
INNER JOIN sys.dm_exec_sessions AS S
ON (S.session_id = C.session_id)
INNER JOIN sys.databases AS D
ON (D.database_id = S.database_id)
WHERE D.name = 'MyDatabase';
同樣地，這些查詢傳回的是某一時間點的計數。 如果您在一段時間內收集多個樣本，就能充分了解工作階段使用量。
您可以查詢 sys.resource_stats 目錄檢視，並檢閱 active_session_count 資料行，以取得工作階段的歷史統計資料。
計算資料庫和物件大小
下列查詢會傳回資料庫的大小 (以 MB 為單位)：
-- Calculates the size of the database.
SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';
下列查詢會傳回您資料庫中個別物件的大小 (以 MB 為單位)：
-- Calculates the size of individual database objects.
SELECT o.name, SUM(ps.reserved_page_count) * 8.0 / 1024 AS size_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS o
ON ps.object_id = o.object_id
GROUP BY o.name
ORDER BY size_mb DESC;
監視查詢效能
速度慢或長時間執行的查詢可能會耗用大量系統資源。 本節示範如何使用 sys.dm_exec_query_stats 動態管理檢視，偵測幾個常見的查詢效能問題。 此檢視在快取計畫中的每個查詢陳述式包含一個資料列，而資料列的存留期則會繫結至計畫本身。 從快取中移除計畫時，對應的資料列也會從這個檢視中刪除。
依 CPU 時間尋找熱門查詢
下列範例會傳回以每個執行的平均 CPU 時間排名的前 15 個查詢的相關資訊。 此範例會根據查詢雜湊來彙總查詢，使在邏輯上等同的查詢依其累積資源耗用量進行分組。
SELECT TOP 15 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
監視累計 CPU 時間的查詢計畫
效率不佳的查詢計畫也可能會增加 CPU 耗用量。 下列範例能判斷哪個查詢會使用最新歷程記錄中最多累計的 CPU。
SELECT
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
FROM
(SELECT TOP 15
qs.plan_handle,
qs.total_worker_time
FROM
sys.dm_exec_query_stats AS qs
ORDER BY qs.total_worker_time desc
) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC;
監視封鎖的查詢
速度慢或長時間執行的查詢會造成過度的資源耗用，並且會導致封鎖查詢的後果。 導致封鎖的原因可能是不佳的應用程式設計、不良的查詢計畫、缺乏有用的索引等等。
您可以使用 sys.dm_tran_locks 檢視表來取得資料庫中目前鎖定活動的相關資訊。 如需範例程式碼，請參閱 sys.dm_tran_locks。 如需針對封鎖進行疑難排解的詳細資訊，請參閱了解和解決 Azure SQL Database 封鎖問題。
監視鎖死
在某些情況下，兩個或以上的查詢可能會彼此相互封鎖，導致鎖死。
您可以建立擴充事件來追蹤 Azure SQL 資料庫中的資料庫，以擷取鎖死事件，接著在查詢存放區中尋找相關的查詢及其執行計畫。 若要深入了解，請參閱分析並防止 Azure SQL 資料庫中的鎖死，包括會在 AdventureWorksLT 中造成死結的實驗室。 深入了解可能發生死結的資源的類型。
相關內容
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應