Monitorare le prestazioni di database SQL di Microsoft Azure usando le viste a gestione dinamica
Si applica a:
database SQL di Azure
database SQL di Microsoft Azure consente a un subset di viste a gestione dinamica di diagnosticare i problemi di prestazioni, che potrebbero essere causati da query bloccate o con esecuzione prolungata, colli di bottiglia delle risorse, piani di query scadenti e altro ancora.
Questo articolo fornisce informazioni su come rilevare i problemi di prestazioni comuni eseguendo query sulle viste a gestione dinamica tramite T-SQL. È possibile usare qualsiasi strumento di query, ad esempio:
Autorizzazioni
In database SQL di Azure, a seconda delle dimensioni di calcolo e dell'opzione di distribuzione, l'esecuzione di query su una DMV potrebbe richiedere l'autorizzazione VIEW DATABA edizione Standard STATE o VIEW edizione Standard RVER STATE. Quest'ultima autorizzazione può essere concessa tramite l'appartenenza al ruolo del ##MS_ServerStateReader## server.
Per concedere l'autorizzazione VIEW DATABA edizione Standard STATE a un utente di database specifico, eseguire la query seguente come esempio:
GRANT VIEW DATABASE STATE TO database_user;
Per concedere l'appartenenza al ruolo del ##MS_ServerStateReader## server a un account di accesso per il server logico in Azure, connettersi al master database e quindi eseguire la query seguente come esempio:
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login];
In un'istanza di SQL Server e in Istanza gestita di SQL di Azure, le DMV restituiscono informazioni sullo stato del server. In database SQL di Azure restituiscono informazioni relative solo al database logico corrente.
Identificare i problemi di prestazioni della CPU
Se l'utilizzo della CPU è superiore all'80% per periodi di tempo prolungati, prendere in considerazione i passaggi seguenti per la risoluzione dei problemi se il problema della CPU si verifica ora o si è verificato in passato.
Il problema della CPU si sta verificando in questo momento
Se il problema si sta verificando in questo momento, esistono due possibili scenari:
Numerose singole query hanno un utilizzo cumulativo elevato della CPU
Usare la query seguente per identificare gli hash di query più frequenti:
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;
Query con esecuzione prolungata che usano la CPU sono ancora in esecuzione
Usare la query seguente per identificare le query:
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
Il problema della CPU si è verificato in precedenza
Se il problema si è verificato in precedenza e si vuole eseguire l'analisi della causa radice, usare Query Store. Gli utenti con accesso al database possono usare T-SQL per eseguire query sui dati di Query Store. Le configurazioni predefinite di Query Store usano una granularità di 1 ora.
Usare la query seguente per esaminare l'attività di query con un utilizzo elevato della CPU. Questa query restituisce le 15 query con un maggior utilizzo della CPU. Ricordarsi di modificare
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;Dopo avere identificato le query problematiche, è possibile ottimizzare le query per ridurre l'utilizzo della CPU. Se l'ottimizzazione delle query richiede troppo tempo, è anche possibile eseguire l'aggiornamento dello SLO del database per risolvere il problema.
Per altre informazioni sulla gestione dei problemi di prestazioni della CPU in database SQL di Azure, vedere Diagnosticare e risolvere i problemi di utilizzo elevato della CPU in database SQL di Azure.
Identificare i problemi di prestazioni di IO
Quando si identificano i problemi di prestazioni di input/output di archiviazione (I/O), i principali tipi di attesa associati ai problemi di I/O sono:
PAGEIOLATCH_*Per i problemi di I/O del file di dati (incluso
PAGEIOLATCH_SH,PAGEIOLATCH_EX,PAGEIOLATCH_UP). Se il nome del tipo di attesa contiene I/ O, fa riferimento a un problema di I/O. Se il nome di attesa latch della pagina non include I/O, il tipo di attesa punta a un tipo di problema diverso, ad esempio alla contesa ditempdb.WRITE_LOGPer i problemi di I/O del log delle transazioni.
Se il problema di I/O si verifica al momento
Usare sys.dm_exec_requests o sys.dm_os_waiting_tasks per visualizzare wait_type e wait_time.
Identificare i dati e l'utilizzo di I/O dei log
Usare la query seguente per identificare i dati e registrare l'utilizzo di I/O. Se i dati o l'I/O del log superano l'80%, significa che gli utenti hanno usato l'I/O disponibile per il livello di servizio database SQL di Azure.
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;
Per altri esempi che usano sys.dm_db_resource_stats, vedere la sezione Monitorare l'uso delle risorse più avanti in questo articolo.
Se è stato raggiunto il limite di I/O, sono disponibili due opzioni:
- Aggiornare le dimensioni di calcolo o il livello di servizio
- Identificare e ottimizzare le query che utilizzano il maggior numero di I/O.
Visualizzare l'I/O correlato al buffer tramite Query Store
Per l'opzione 2, è possibile usare la query seguente su Query Store per le operazioni di I/O correlate al buffer per visualizzare le ultime due ore di attività rilevate:
-- 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
Visualizzare l'I/O totale dei log per le attese WRITELOG
Se il tipo di attesa è WRITELOG, usare la query seguente per visualizzare l'I/O totale del log per istruzione:
-- 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
Identificare i problemi di prestazioni di tempdb
Quando si identificano i problemi di prestazioni di I/O, i tipi di attesa principali associati ai tempdb problemi sono PAGELATCH_* (non PAGEIOLATCH_*). Tuttavia, le attese PAGELATCH_* non indicano sempre una contesa di tempdb. Questo tipo di attesa può anche indicare una contesa della pagina di dati utente-oggetto causata da richieste simultanee che puntano alla stessa pagina di dati. Per confermare tempdb ulteriormente la contesa, usare sys.dm_exec_requests per confermare che il wait_resource valore inizia con 2:x:y dove 2 è tempdb l'ID del database, x è l'ID file e y è l'ID pagina.
Un metodo comune per la contesa di tempdb consiste nel ridurre o nel riscrivere il codice dell'applicazione che si basa su tempdb. Le aree di utilizzo di tempdb comuni includono:
- Tabelle temporanee
- Variabili di tabella
- Parametri con valori di tabella
- Utilizzo dell'archivio versioni (associato alle transazioni a esecuzione prolungata)
- Query con piani di query che usano ordinamenti, hash join e spool
Per altre informazioni, vedere tempdb in Azure SQL.
Query con maggior utilizzo di variabili di tabella e tabelle temporanee
Usare la query seguente per identificare le query con maggior utilizzo di variabili di tabella e tabelle temporanee:
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
Identificare le transazioni a esecuzione prolungata
Usare la query seguente per identificare le transazioni a esecuzione prolungata. Le transazioni con esecuzione prolungata impediscono la pulizia dell'archivio versioni permanenti . Per altre informazioni, vedere Risolvere i problemi relativi al ripristino accelerato del database.
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;
Identificare i problemi di prestazioni di attesa della concessione di memoria
Se il tipo di attesa più frequente è RESOURCE_SEMAPHORE e non si riscontra un problema di utilizzo elevato della CPU, è possibile che si stia verificando un problema di attesa della concessione di memoria.
Determinare se un'attesa RESOURCE_edizione Standard MAPHORE è un'attesa superiore
Usare la query seguente per determinare se un'attesa RESOURCE_SEMAPHORE è un'attesa superiore. Anche indicativo sarebbe un crescente rango di tempo di attesa di RESOURCE_SEMAPHORE nella storia recente. Per altre informazioni sulla risoluzione dei problemi di attesa delle concessioni di memoria, vedere Risolvere i problemi di rallentamento delle prestazioni o memoria insufficiente causati dalle concessioni di memoria in 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;
Identificare le istruzioni con utilizzo della memoria elevato
Se si verificano errori di memoria insufficiente in database SQL di Azure, esaminare sys.dm_os_out_of_memory_events. Per altre informazioni, vedere Risolvere gli errori di memoria insufficiente con database SQL di Azure.
Prima di tutto, modificare lo script seguente per aggiornare i valori pertinenti di start_time e end_time. Eseguire quindi la query seguente per identificare istruzioni con utilizzo elevato di memoria:
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;
Identificare le 10 concessioni di memoria attive più frequenti
Usare la query seguente per identificare le 10 concessioni di memoria attive più frequenti:
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;
Monitorare le connessioni
È possibile utilizzare la vista sys.dm_exec_connections per recuperare informazioni sulle connessioni stabilite a un database specifico o a un pool elastico e i dettagli di ogni connessione. Inoltre, la visualizzazione sys.dm_exec_sessions è utile durante il recupero di informazioni su tutte le connessioni utente attive e le attività interne.
Visualizzare le sessioni correnti
La query seguente recupera informazioni sulla connessione corrente. Per visualizzare tutte le sessioni, rimuovere la WHERE clausola .
Vengono visualizzate tutte le sessioni in esecuzione nel database solo se si dispone dell'autorizzazione VIEW DATABA edizione Standard STATE per il database durante l'esecuzione delle sys.dm_exec_requests viste e sys.dm_exec_sessions . In caso contrario, viene visualizzata solo la sessione corrente.
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
Monitorare l'uso delle risorse
È possibile monitorare database SQL di Azure utilizzo delle risorse a livello di query usando database SQL Informazioni dettagliate prestazioni query nel portale di Azure o in Query Store.
È anche possibile monitorare l'utilizzo usando queste visualizzazioni:
sys.dm_db_resource_stats
È possibile usare la vista sys.dm_db_resource_stats in ogni database. La vista sys.dm_db_resource_stats mostra i dati recenti sull'uso delle risorse rispetto al livello di servizio. Informazioni relative a percentuali medie della CPU, dati I/O, scritture nei log e memoria vengono registrate ogni 15 secondi e vengono mantenute per un'ora.
Poiché questa vista fornisce una visione più granulare sull'uso delle risorse, si consiglia di usare prima sys.dm_db_resource_stats per eventuali analisi o risoluzioni di problemi dello stato corrente. Ad esempio, questa query descrive l'uso medio e massimo delle risorse per il database corrente nell'ultima ora:
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;
Per altre query, vedere gli esempi in sys.dm_db_resource_stats.
sys.resource_stats
La vista sys.resource_stats nel master database include informazioni aggiuntive che consentono di monitorare le prestazioni del database in base al livello di servizio e alle dimensioni di calcolo specifiche. I dati vengono raccolti ogni 5 minuti e conservati per circa 14 giorni. Questa vista è utile per un'analisi cronologica a lungo termine del modo in cui il database usa le risorse.
Il grafico seguente illustra l'uso di risorse della CPU per un database Premium con dimensioni di calcolo P2 per ogni ora nell'arco di una settimana. Questo grafico inizia un lunedì, mostra cinque giorni lavorativi e quindi mostra un fine settimana, quando si verifica molto meno nell'applicazione.
In base ai dati, per le dimensioni di calcolo P2 il carico massimo della CPU di questo database attualmente supera di poco il 50% dell'uso della CPU (a mezzogiorno di martedì). Se la CPU è il fattore dominante nel profilo di risorse dell'applicazione, è possibile decidere che P2 è la dimensione di calcolo corretta per garantire che il carico di lavoro sia sempre adatto. Se si prevede che un'applicazione presenti un incremento nel tempo, è consigliabile avere un buffer di risorse aggiuntivo, in modo che l'applicazione non raggiunga mai il limite del livello di prestazioni. Se si aumentano le dimensioni di calcolo, è possibile evitare gli errori visibili ai clienti che si possono verificare se un database non ha risorse sufficienti per elaborare in modo efficiente le richieste, in particolare in ambienti sensibili alla latenza. Un esempio è costituito da un database che supporta un'applicazione per la creazione di pagine Web in base ai risultati delle chiamate al database.
Altri tipi di applicazioni possono interpretare in modo diverso lo stesso grafico. Se ad esempio un'applicazione prova a elaborare i dati del libro paga ogni giorno e usa lo stesso grafico, questo tipo di modello di processo batch potrebbe essere eseguito correttamente con dimensioni di calcolo P1. Il valore di DTU delle dimensioni di calcolo P1 è pari a 100, mentre quello delle dimensioni di calcolo P2 è pari a 200. Il livello di prestazioni fornito dalle dimensioni di calcolo P2 è doppio rispetto a quello fornito dalle dimensioni di calcolo P1. Il 50% dell'uso della CPU nel livello P2 equivale quindi al 100% dell'uso della CPU in P1. Se l'applicazione non presenta timeout, è possibile che non sia rilevante se il completamento di un processo richiede 2 ore o 2,5 ore, se viene completato in giornata. Per un'applicazione che rientra in questa categoria è probabilmente sufficiente usare le dimensioni di calcolo P1. Si può sfruttare la presenza di periodi di tempo durante il giorno in cui l'uso delle risorse è inferiore, in modo da spalmare un picco massimo in altri momenti nel corso della giornata. Le dimensioni di calcolo P1 possono essere ottimali per questo tipo di applicazione e possono consentire di limitare i costi, purché i processi vengano completati in orario ogni giorno.
Il motore di database espone le informazioni sulle risorse utilizzate per ogni database attivo nella sys.resource_stats vista del master database in ogni server. I dati nella tabella vengono aggregati per intervalli di 5 minuti. Con i livelli di servizio Basic, Standard e Premium, è possibile che la visualizzazione dei dati nella tabella richieda più di 5 minuti, quindi i dati risultano più utili per le analisi cronologiche, invece che per le analisi in tempo quasi reale. Eseguire una query sulla sys.resource_stats vista per visualizzare la cronologia recente di un database e verificare se la prenotazione scelta ha fornito le prestazioni desiderate quando necessario.
Nota
In database SQL di Azure è necessario essere connessi al master database per eseguire query sys.resource_stats negli esempi seguenti.
Questo esempio illustra la modalità di esposizione dei dati in questa vista:
SELECT TOP 10 *
FROM sys.resource_stats
WHERE database_name = 'userdb1'
ORDER BY start_time DESC;
L'esempio seguente illustra diversi modi in cui è possibile usare la sys.resource_stats vista del catalogo per ottenere informazioni sul modo in cui il database usa le risorse:
Per esaminare l'uso della risorsa della settimana precedente per il database
userdb1utente, è possibile eseguire questa query sostituendo il proprio nome di database:SELECT * FROM sys.resource_stats WHERE database_name = 'userdb1' AND start_time > DATEADD(day, -7, GETDATE()) ORDER BY start_time DESC;Per verificare l'idoneità del carico di lavoro per le dimensioni di calcolo, è necessario eseguire il drill-down in ogni aspetto delle metriche delle risorse, ovvero CPU, operazioni di lettura e scrittura, numero di thread di lavoro e numero di sessioni. Ecco una query modificata usando
sys.resource_statsper segnalare i valori medi e massimi di queste metriche delle risorse, per ogni livello di servizio per cui è stato effettuato il provisioning del database: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;Con queste informazioni sui valori medi e massimi di ogni metrica delle risorse è possibile valutare l'idoneità delle dimensioni di calcolo scelte in rapporto al carico di lavoro. In genere, i valori medi da
sys.resource_statsoffrono una buona linea di base da usare rispetto alle dimensioni di destinazione. Deve trattarsi dello strumento di misurazione principale.Per i database del modello di acquisto DTU:
È ad esempio possibile che si usi il livello di servizio Standard con le dimensioni di calcolo S2. Le percentuali medie di uso per la CPU e per operazioni di scrittura e lettura I/O sono inferiori al 40%, il numero medio di thread di lavoro è inferiore a 50 e il numero medio di sessioni è inferiore a 200. Il carico di lavoro potrebbe essere idoneo per le dimensioni di calcolo S1. È facile verificare se il database rientra nei limiti dei thread di lavoro e delle sessioni. Per verificare se un database rientra in una dimensione di calcolo inferiore, dividere il numero DTU delle dimensioni di calcolo inferiori in base al numero DTU delle dimensioni di calcolo correnti e quindi moltiplicare il risultato per 100:
S1 DTU / S2 DTU * 100 = 20 / 50 * 100 = 40Il risultato rappresenta la differenza di prestazioni relativa, in percentuale, tra le due dimensioni di calcolo. Se l'uso delle risorse non supera questa quantità, il carico di lavoro potrebbe essere idoneo per le dimensioni di calcolo inferiori. È tuttavia necessario esaminare anche tutti gli intervalli dei valori di uso delle risorse e determinare, a livello di percentuale, la frequenza con cui il carico di lavoro del database può rientrare nelle dimensioni di calcolo inferiori. La query seguente genera questa percentuale per ogni dimensione di risorsa, in base alla soglia del 40% calcolata in questo esempio:
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;In base livello di servizio del database, è possibile stabilire se il carico di lavoro può rientrare nelle dimensioni di calcolo inferiori. Se l'obiettivo del carico di lavoro del database è 99,9% e la query precedente restituisce valori superiori al 99,9% per tutte e tre le dimensioni della risorsa, è probabile che il carico di lavoro possa rientrare nelle dimensioni di calcolo inferiori.
La percentuale calcolata in precedenza consente inoltre di stabilire se è opportuno usare le dimensioni di calcolo superiori a quelle attualmente in uso per soddisfare l'obiettivo. Ad esempio, l'utilizzo della CPU per un database di esempio nell'ultima settimana:
Percentuale CPU media Percentuale CPU massima 24.5 100.00 L'uso medio della CPU corrisponde a circa un quarto del limite delle dimensioni di calcolo, che potrebbe rientrare nelle dimensioni di calcolo del database.
Per il modello di acquisto DTU e i database del modello di acquisto vCore:
Il valore massimo indica che il database raggiunge il limite delle dimensioni di calcolo. È necessario passare alle dimensioni di calcolo superiori a quelle attualmente in uso? Considerare il numero di volte in cui il carico di lavoro raggiunge il 100% e quindi confrontare tale numero con l'obiettivo del carico di lavoro del database.
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;Queste percentuali sono il numero di campioni che il carico di lavoro rientra nelle dimensioni di calcolo correnti. Se questa query restituisce un valore inferiore al 99,9% per una delle tre dimensioni della risorsa, il carico di lavoro medio campionato ha superato i limiti. Prendere in considerazione il passaggio alla successiva dimensione di calcolo superiore o usare tecniche di ottimizzazione delle applicazioni per ridurre il carico nel database.
Nota
Per i pool elastici, è possibile monitorare i singoli database nel pool con le tecniche descritte in questa sezione. È anche possibile monitorare il pool nel suo complesso. Per informazioni, vedere Monitorare e gestire un elastico.
Numero massimo di richieste simultanee
Per visualizzare il numero corrente di richieste simultanee, eseguire questa query nel database utente:
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R;
Per analizzare il carico di lavoro di un database, modificare questa query per filtrare il database specifico da analizzare. Prima di tutto, aggiornare il nome del database da MyDatabase al database desiderato, quindi eseguire la query seguente per trovare il numero di richieste simultanee nel database:
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';
Questo è solo uno snapshot in un singolo punto nel tempo. Per una migliore comprensione del carico di lavoro e dei requisiti relativi alle richieste simultanee, sarà necessario raccogliere molti campioni nel tempo.
Numero massimo di eventi di accesso simultanei
È possibile analizzare i modelli di utente e applicazione per ottenere un'idea della frequenza degli eventi di accesso. È inoltre possibile eseguire carichi reali in un ambiente di test per assicurarsi di non raggiungere questo o gli altri limiti descritti in questo argomento. Non è disponibile alcuna vista di query singola o DMV per la visualizzazione dei numeri o della cronologia degli accessi simultanei.
Se più client usano la stessa stringa di connessione, il servizio autentica ogni account di accesso. Se 10 utenti si connettono contemporaneamente a un database con nome utente e password identici, ci saranno dieci account di accesso simultanei. Questo limite si applica solo alla durata dell'account di accesso e dell'autenticazione. Se gli stessi 10 utenti si connettono in sequenza al database, il numero di account di accesso simultanei non sarà mai superiore a uno.
Nota
Questo limite attualmente non si applica ai database in pool elastici.
Numero massimo di sessioni
Per visualizzare il numero di sessioni attive correnti, eseguire questa query nel database:
SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections;
Se si analizza un carico di lavoro di SQL Server, modificare la query per concentrarsi su un database specifico. Questa query consente di determinare le possibili esigenze di sessione per il database se si sta valutando la possibilità di spostarla in Azure. Prima di tutto, aggiornare il nome del database da MyDatabase al database desiderato, quindi eseguire la query seguente:
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';
Queste query restituiscono un conteggio temporizzato. Se si raccolgono più campioni nel tempo, si otterrà una comprensione ottimale dell'uso della sessione.
È possibile ottenere statistiche cronologiche sulle sessioni eseguendo una query sulla vista del catalogo sys.resource_stats ed esaminando la active_session_count colonna.
Calcolare le dimensioni di database e oggetti
La seguente query restituisce la dimensione del database in megabyte:
-- 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';
La query seguente restituisce le dimensioni dei singoli oggetti (in megabyte) nel database:
-- 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;
Monitorare le prestazioni delle query
L’esecuzione della query rallentata o prolungata può consumare delle risorse di sistema importanti. In questa sezione viene illustrato come usare le viste a gestione dinamica per rilevare alcuni problemi comuni di prestazioni delle query usando la vista a gestione dinamica sys.dm_exec_query_stats . La vista contiene una riga per ogni istruzione di query nel piano memorizzato nella cache e la durata delle righe è legata al piano stesso. Quando un piano viene rimosso dalla cache, le righe corrispondenti vengono eliminate da questa vista.
Trovare le query principali in base al tempo di CPU
Nell'esempio seguente vengono restituite informazioni sulle prime 15 query classificate in base al tempo medio della CPU per esecuzione. Nell'esempio le query vengono aggregate in base al relativo valore hash, in modo da raggruppare le query logicamente equivalenti in base all'utilizzo di risorse cumulativo.
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;
Monitorare i piani di query per il tempo cpu cumulativo
Un piano di query inefficiente può anche aumentare il consumo della CPU. L'esempio seguente determina la query che usa la CPU più cumulativa nella cronologia recente.
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;
Monitorare le query bloccate
Le query lente o con esecuzione prolungata possono contribuire al consumo eccessivo delle risorse ed essere la conseguenza di query bloccate. Le cause del blocco possono essere una progettazione povera dell'applicazione, dei piani di query non validi, la mancanza di indici utili e così via.
È possibile usare la sys.dm_tran_locks vista per ottenere informazioni sull'attività di blocco corrente nel database. Per un esempio di codice, vedere sys.dm_tran_locks. Per altre informazioni sulla risoluzione dei problemi di blocco, vedere Comprendere e risolvere i problemi di blocco di Azure SQL.
Monitorare i deadlock
In alcuni casi, due o più query possono bloccarsi a vicenda causando un deadlock.
È possibile creare una traccia degli eventi estesi in un database in database SQL di Azure per acquisire eventi deadlock, quindi trovare query correlate e i relativi piani di esecuzione in Query Store. Per altre informazioni, vedere Analizzare e impedire deadlock in database SQL di Azure, incluso un lab per causare un deadlock in AdventureWorksLT. Altre informazioni sui tipi di risorse che possono essere deadlock.
Contenuto correlato
- Introduzione a database SQL di Azure e Istanza gestita di SQL di Azure
- Diagnosticare e risolvere i problemi relativi all'utilizzo elevato della CPU in Database SQL di Azure
- Ottimizzare le applicazioni e i database per le prestazioni nel database SQL di Azure
- Comprendere e risolvere i problemi che causano un blocco per il database SQL di Azure
- Analizzare e impedire deadlock nel database SQL di Azure
- Monitorare i carichi di lavoro SQL di Azure con Database Watcher (anteprima)
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per