Monitoraggio delle prestazioni dell’istanza gestita di SQL di Microsoft Azure tramite DMV

Articolo
10/16/2024

Si applica a: Istanza gestita di SQL di Azure SQL

Istanza gestita di SQL di Microsoft Azure consente a un sottoinsieme di DMV (Dynamic Management View) di diagnosticare i problemi delle prestazioni che potrebbero essere causati da query bloccate o con esecuzione prolungata, colli di bottiglia delle risorse, piani di query insufficienti e così via. Questo articolo fornisce informazioni su come rilevare problemi comuni relativi alle prestazioni tramite le DMV.

Questo articolo riguarda Istanza gestita di SQL di Azure, vedere anche Monitoraggio delle prestazioni di database SQL di Microsoft Azure tramite le DMV.

Autorizzazioni

In Istanza gestita di SQL di Azure, l'esecuzione di query su una DMV richiede autorizzazioni VIEW SERVER STATE.

GRANT VIEW SERVER STATE TO database_user;

In un'istanza di SQL Server e in Istanza gestita di SQL di Azure, le DMV restituiscono informazioni sullo stato del server.

Identificare i problemi di prestazioni della CPU

Se l'utilizzo della CPU è superiore all'80% per lunghi periodi di tempo, considerare i passaggi di risoluzione dei problemi seguenti:

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 un'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 id if not parameterized or not parameterized properly
-- it grabs a sample query text by min
WITH AggregatedCPU AS (SELECT q.query_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, 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
                            JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                            JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                            JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
                            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_millisec, avg_cpu_millisec, max_cpu_millisec, 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_millisec DESC, query_hash ASC) AS RN
                                                              FROM AggregatedCPU)
SELECT OD.query_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, 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.RN
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_millisec 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 dell'istanza gestita per risolvere il problema.

Identificare i problemi di prestazioni di IO

Quando si identificano i problemi di prestazioni di IO, i tipi di attesa più frequenti associati a problemi di IO sono i seguenti:

PAGEIOLATCH_*

Per i problemi di IO dei file di dati (inclusi PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). Se il nome del tipo di attesa include IO, il tipo di attesa punta a un problema di IO. 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 di tempdb.
WRITE_LOG

Per i problemi di IO del log delle transazioni.

Se il problema di IO si sta verificando in questo momento

Usare sys.dm_exec_requests o sys.dm_os_waiting_tasks per visualizzare wait_type e wait_time.

Per l'opzione 2, è possibile usare la query seguente in Query Store per l'IO correlato ai buffer per visualizzare le ultime due ore di attività tracciate:

-- 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
                         JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                         JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                         JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
                         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 RN
                                                        FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.RN
FROM Ordered AS OD
WHERE OD.RN<=15
ORDER BY total_wait_time_ms DESC;
GO

Visualizzare l'IO totale dei log per le attese WRITELOG

Se il tipo di attesa è WRITELOG, usare la query seguente per visualizzare l'IO totale dei 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_millisec,
           SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_millisec,
           SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
           MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec,
           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
        JOIN sys.query_store_query AS q
            ON qt.query_text_id = q.query_text_id
        JOIN sys.query_store_plan AS p
            ON q.query_id = p.query_id
        JOIN sys.query_store_runtime_stats AS rs
            ON rs.plan_id = p.plan_id
        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 RN
    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.RN
FROM OrderedLogUsed AS OD
WHERE OD.RN <= 15
ORDER BY total_log_bytes_used DESC;
GO

Identificare i problemi di prestazioni di `tempdb`

Quando si identificano i problemi di prestazioni di IO, il tipo di attesa più frequente associato a problemi di tempdb è 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 la contesa di tempdb, usare sys.dm_exec_requests per confermare che il valore wait_resource inizia con 2:x:y dove 2 è tempdb è l'ID 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

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)') 'Database', stmt.stmt_details.value('@Schema', 'varchar(max)') 'Schema', stmt.stmt_details.value('@Table', 'varchar(max)') '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
    JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;

Identificare le transazioni a esecuzione prolungata

Usare la query seguente per identificare le transazioni a esecuzione prolungata. Le transazioni a esecuzione prolungata impediscono la pulizia dell'archivio versioni.

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_SEMAHPORE 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_SEMAHPORE` è una delle attese più frequenti

Usare la query seguente per determinare se un'attesa RESOURCE_SEMAHPORE è una delle attese più frequenti

SELECT wait_type,
       SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
    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, esaminare sys.dm_os_out_of_memory_events.

Usare la query seguente per identificare le istruzioni con utilizzo della memoria elevato:

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
    JOIN sys.query_store_runtime_stats AS r
        ON p.plan_id = r.plan_id
    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
    JOIN sys.query_store_query AS q
        ON cte.query_id = q.query_id
    JOIN sys.query_store_query_text AS t
        ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;

Identificare le concessioni di memoria

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),
                ' '
            )
            ) 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
    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;

Calcolo delle dimensioni del database e degli 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 DatabaseSizeInMB
FROM sys.database_files
WHERE type_desc = 'ROWS';
GO

La query seguente restituisce le dimensioni dei singoli oggetti (in megabyte) nel database:

-- Calculates the size of individual database objects.
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name;
GO

Monitoraggio delle connessioni

È possibile usare la visualizzazione sys.dm_exec_connections per recuperare informazioni sulle connessioni stabilite con una specifica istanza gestita e i dettagli di ogni connessione. Inoltre, la vista sys.dm_exec_sessions è utile durante il recupero di informazioni su tutte le connessioni utente attive e le attività interne.

La query seguente recupera le informazioni sulla connessione 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
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;

Monitorare l'uso delle risorse

È possibile monitorare l'utilizzo delle risorse usando Query Store, esattamente come in SQL Server.

È possibile anche monitorare l'utilizzo usando sys.dm_db_resource_stats e sys.server_resource_stats.

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. Le percentuali medie relative a CPU, I/O dei dati, 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  
    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;

Per altre query, vedere gli esempi in sys.dm_db_resource_stats.

sys.server_resource_stats

È possibile usare sys.server_resource_stats per restituire i dati di utilizzo della CPU, I/O e di archiviazione per Istanza gestita di SQL di Azure. I dati vengono raccolti e aggregati in intervalli di cinque minuti. È presente una riga per ogni 15 secondi di reporting. I dati restituiti includono utilizzo della CPU, dimensioni di archiviazione, utilizzo di I/O e SKU dell'istanza gestita. I dati cronologici vengono mantenuti per circa 14 giorni.

L'esempio mostra i diversi modi in cui è possibile usare la vista del catalogo sys.server_resource_stats per ottenere informazioni sul modo in cui l'istanza usa le risorse.

Il seguente esempio restituisce l'utilizzo medio della CPU negli ultimi sette giorni:

DECLARE @s datetime;  
DECLARE @e datetime;  
SET @s= DateAdd(d,-7,GetUTCDate());  
SET @e= GETUTCDATE();  
SELECT AVG(avg_cpu_percent) AS Average_Compute_Utilization   
FROM sys.server_resource_stats   
WHERE start_time BETWEEN @s AND @e;
GO

L'esempio seguente restituisce lo spazio di archiviazione medio usato dall'istanza al giorno, per consentire l'analisi delle tendenze di crescita:

DECLARE @s datetime;  
DECLARE @e datetime;  
SET @s= DateAdd(d,-7,GetUTCDate());  
SET @e= GETUTCDATE();  
SELECT Day = convert(date, start_time), AVG(storage_space_used_mb) AS Average_Space_Used_mb
FROM sys.server_resource_stats   
WHERE start_time BETWEEN @s AND @e
GROUP BY convert(date, start_time)
ORDER BY convert(date, start_time);
GO

Numero massimo di richieste simultanee

Per visualizzare il numero attuale di richieste simultanee, eseguire questa query Transact-SQL sul database:

SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests R;

Per analizzare il carico di lavoro di un singolo database, modificare questa query in modo che applichi il filtro al database specifico da analizzare. Se ad esempio è presente un database denominato MyDatabase, questa query Transact-SQL restituirà il numero di richieste simultanee in tale database:

SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests R
INNER JOIN sys.databases 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 accessi simultanei

Per avere un'idea della frequenza degli accessi, è possibile analizzare i modelli dell'utente e dell'applicazione. È 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.

Numero massimo di sessioni

Per visualizzare il numero di sessioni attive correnti, eseguire questa query Transact-SQL sul 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 della sessione per tale database se si sta prendendo in considerazione lo spostamento in Azure.

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections C
INNER JOIN sys.dm_exec_sessions S ON (S.session_id = C.session_id)
INNER JOIN sys.databases 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.

Monitoraggio delle prestazioni delle query

L’esecuzione della query rallentata o prolungata può consumare delle risorse di sistema importanti. In questa sezione viene illustrato come utilizzare le visualizzazioni a gestione dinamica per rilevare alcuni problemi di prestazione delle query comuni.

Ricerca delle prime n query

Nell'esempio seguente vengono restituite informazioni sulle prime cinque query classificate in base al tempo medio della CPU. 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 5 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;

Monitoraggio delle 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 utilizzare la vista sys.dm_tran_locks per ottenere informazioni sulle attività di blocco correnti 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 per acquisire eventi deadlock, quindi trovare query correlate e i relativi piani di esecuzione in Query Store.

Per Istanza gestita di SQL di Azure, vedere gli Strumenti deadlock nella Guida ai deadlock.

Monitoraggio dei piani di query

Un piano di query inefficiente può anche aumentare il consumo della CPU. Nell'esempio seguente viene usata la visualizzazione sys.dm_exec_query_stats per determinare la query che usa la CPU cumulativa maggiore.

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 50
        qs.plan_handle,
        qs.total_worker_time
    FROM
        sys.dm_exec_query_stats 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;

Altre opzioni di monitoraggio

Monitoraggio con Database Watcher (anteprima)

Il database watcher raccoglie dati di monitoraggio approfonditi del carico di lavoro per offrire una visualizzazione dettagliata delle prestazioni, della configurazione e del funzionamento del database. Le dashboard nel portale di Azure offrono una visualizzazione in un unico riquadro dell'ambiente Azure SQL e una visualizzazione dettagliata di ogni risorsa monitorata. I dati vengono raccolti in un archivio dati centrale nell’ambito della sottoscrizione di Azure. I clienti possono eseguire query, analizzarle, esportarle, visualizzarle e integrarle con sistemi downstream.

Per altre informazioni su database watcher, consultare i seguenti articoli:

Eseguire il monitoraggio con Monitoraggio di Azure

Monitoraggio di Azure offre un'ampia gamma di gruppi di raccolta di dati di diagnostica, metriche ed endpoint per il monitoraggio di Istanza gestita di SQL di Azure. Per altre informazioni, vedere Monitorare Istanza gestita di SQL di Azure con Monitoraggio di Azure. Analisi SQL di Azure (anteprima) è un'integrazione con Monitoraggio di Azure in cui molte soluzioni di monitoraggio non sono più in fase di sviluppo attivo. Per altre opzioni di monitoraggio, vedere Monitoraggio e ottimizzazione delle prestazioni di Istanza gestita di SQL di Azure e database SQL di Azure.

Condividi tramite

Monitoraggio delle prestazioni dell’istanza gestita di SQL di Microsoft Azure tramite DMV

Autorizzazioni

Identificare i problemi di prestazioni della CPU

Il problema della CPU si sta verificando in questo momento

Numerose singole query hanno un utilizzo cumulativo elevato della CPU

Query con esecuzione prolungata che usano la CPU sono ancora in esecuzione

Il problema della CPU si è verificato in precedenza

Identificare i problemi di prestazioni di IO

Se il problema di IO si sta verificando in questo momento

Visualizzare l'IO totale dei log per le attese WRITELOG

Identificare i problemi di prestazioni di `tempdb`

Query con maggior utilizzo di variabili di tabella e tabelle temporanee

Identificare le transazioni a esecuzione prolungata

Identificare i problemi di prestazioni di attesa della concessione di memoria

Determinare se un'attesa `RESOURCE_SEMAHPORE` è una delle attese più frequenti

Identificare le istruzioni con utilizzo della memoria elevato

Identificare le concessioni di memoria

Calcolo delle dimensioni del database e degli oggetti

Monitoraggio delle connessioni

Monitorare l'uso delle risorse

sys.dm_db_resource_stats

sys.server_resource_stats

Numero massimo di richieste simultanee

Numero massimo di accessi simultanei

Numero massimo di sessioni

Monitoraggio delle prestazioni delle query

Ricerca delle prime n query

Monitoraggio delle query bloccate

Monitorare i deadlock

Monitoraggio dei piani di query

Altre opzioni di monitoraggio

Monitoraggio con Database Watcher (anteprima)

Eseguire il monitoraggio con Monitoraggio di Azure

Vedi anche

Passaggi successivi

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

Monitoraggio delle prestazioni dell’istanza gestita di SQL di Microsoft Azure tramite DMV

Autorizzazioni

Identificare i problemi di prestazioni della CPU

Il problema della CPU si sta verificando in questo momento

Numerose singole query hanno un utilizzo cumulativo elevato della CPU

Query con esecuzione prolungata che usano la CPU sono ancora in esecuzione

Il problema della CPU si è verificato in precedenza

Identificare i problemi di prestazioni di IO

Se il problema di IO si sta verificando in questo momento

Visualizzare l'IO correlato ai buffer usando Query Store

Visualizzare l'IO totale dei log per le attese WRITELOG

Identificare i problemi di prestazioni di tempdb

Query con maggior utilizzo di variabili di tabella e tabelle temporanee

Identificare le transazioni a esecuzione prolungata

Identificare i problemi di prestazioni di attesa della concessione di memoria

Determinare se un'attesa RESOURCE_SEMAHPORE è una delle attese più frequenti

Identificare le istruzioni con utilizzo della memoria elevato

Identificare le concessioni di memoria

Calcolo delle dimensioni del database e degli oggetti

Monitoraggio delle connessioni

Monitorare l'uso delle risorse

sys.dm_db_resource_stats

sys.server_resource_stats

Numero massimo di richieste simultanee

Numero massimo di accessi simultanei

Numero massimo di sessioni

Monitoraggio delle prestazioni delle query

Ricerca delle prime n query

Monitoraggio delle query bloccate

Monitorare i deadlock

Monitoraggio dei piani di query

Altre opzioni di monitoraggio

Monitoraggio con Database Watcher (anteprima)

Eseguire il monitoraggio con Monitoraggio di Azure

Vedi anche

Passaggi successivi

Commenti e suggerimenti

Risorse aggiuntive

Identificare i problemi di prestazioni di `tempdb`

Determinare se un'attesa `RESOURCE_SEMAHPORE` è una delle attese più frequenti