Surveillance des performances de Microsoft Azure SQL Managed Instance à l’aide de vues de gestion dynamique

Article
10/16/2024

S’applique à : Azure SQL Managed Instance

Microsoft Azure SQL Managed Instance active un sous-ensemble de vues de gestion dynamique (DMV) permettant de diagnostiquer des problèmes de performances qui peuvent être causés par des requêtes bloquées ou longues, des goulots d’étranglement des ressources, des plans de requête médiocres, et ainsi de suite. Cet article fournit des informations sur la façon de détecter des problèmes de performances courants à l’aide d’affichages de gestion dynamique.

Cet article a trait à Azure SQL Managed Instance. Consultez également Surveillance des performances Microsoft Azure SQL Database à l’aide de vues de gestion dynamique.

Autorisations

Dans Azure SQL Managed Instance, l’interrogation d’une vue de gestion dynamique nécessite des autorisations VIEW SERVER STATE.

GRANT VIEW SERVER STATE TO database_user;

Dans une instance de SQL Server et dans Azure SQL Managed Instance, des vues de gestion dynamique retournent des informations sur l’état du serveur.

Identifier les problèmes de performances d’UC

Si la consommation de l’UC est supérieure à 80 % pendant de longues périodes, envisagez les étapes de dépannage suivantes :

Le problème de l’UC se produit maintenant

Si le problème se produit maintenant, il y a deux scénarios possibles :

De nombreuses requêtes individuelles consomment une quantité cumulée d’UC élevée

Utilisez la requête suivante pour identifier les principaux hachages de requête :

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;

Des requêtes longues consommant l’UC sont toujours en cours d’exécution

Utilisez la requête suivante pour identifier ces requêtes :

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

Le problème de l’UC s’est produit dans le passé

Si le problème s’est produit dans le passé et que vous souhaitez effectuer une analyse de la cause racine, utilisez Magasin de requêtes. Les utilisateurs ayant accès à la base de données peuvent utiliser T-SQL pour interroger les données du Magasin de requêtes. Les configurations par défaut du Magasin de requêtes utilisent un niveau de granularité d’1 heure. Utilisez la requête suivante pour examiner l’activité des requêtes ayant une consommation d’UC élevée. Cette requête retourne les 15 premières requêtes consommant l’UC. N’oubliez pas de changer 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;

Une fois que vous avez identifié les requêtes problématiques, vous devez les paramétrer pour réduire leur consommation d’UC. Si vous n’avez pas le temps de paramétrer les requêtes, vous pouvez également choisir de mettre à niveau le SLO de la l’instance gérée pour contourner le problème.

Identifier les problèmes de performances d’E/S

Lors de l’identification des problèmes de performances d’E/S, les principaux types d’attente associés à des problèmes d’E/S sont :

PAGEIOLATCH_*

Pour les problèmes d’E/S de fichier de données (y compris PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). Si le nom du type d’attente contient IO, cela indique un problème d’E/S. Si le nom d’attente de verrou de la page ne comporte aucun IO, cela indique un problème différent (par exemple, contention de tempdb).
WRITE_LOG

Pour les problèmes d’E/S de journal des transactions.

Si le problème d’E/S se produit maintenant

Utilisez sys.dm_exec_requests ou sys.dm_os_waiting_tasks pour voir les valeurs wait_type et wait_time.

Pour l’option 2, vous pouvez utiliser la requête suivante sur le Magasin de requêtes pour voir les E/S de la mémoire tampon et afficher les deux dernières heures d’activité suivie :

-- 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

Afficher le nombre total d’E/S du journal pour les attentes WRITELOG

Si le type d’attente est WRITELOG, utilisez la requête suivante pour afficher le nombre total d’E/S du journal par instruction :

-- 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

Identifier les problèmes de performances `tempdb`

Lors de l’identification des problèmes de performances d’E/S, le principal type d’attente associé à des problèmes tempdb est PAGELATCH_* (pas PAGEIOLATCH_*). Toutefois, les attentes PAGELATCH_* ne signifient pas toujours qu’il y a une contention tempdb. Cette attente peut aussi s’expliquer par une contention de page de données utilisateur-objet due à des requêtes concurrentes ciblant la même page de données. Pour confirmer la contention tempdb, utilisez sys.dm_exec_requests et vérifiez que la valeur wait_resource commence par 2:x:y où 2 est tempdb et l’ID de la base de données, x est l’ID du fichier et y l’ID de la page.

Pour une contention tempdb, il est courant de réduire ou réécrire le code d’application qui repose sur tempdb. Les zones courantes d’utilisation de tempdb sont les suivantes :

Tables temporaires
Variables de table
Paramètres table
Utilisation du magasin de version (associé aux transactions longues)
Requêtes comportant des plans de requête qui utilisent des tris, des jointures hachées et des files d’attente

Principales requêtes qui utilisent des variables de table et des tables temporaires

Utilisez la requête suivante pour identifier les principales requêtes qui utilisent des variables de table et des tables temporaires :

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;

Identifier les transactions longues

Utilisez la requête suivante pour identifier les transactions longues. Les transactions longues empêchent le nettoyage du magasin de version.

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;

Identifier les problèmes de performances d’attente d’allocation de mémoire

Si votre principal type d’attente est RESOURCE_SEMAHPORE et que vous n’avez pas de problème d’utilisation élevée de l’UC, vous avez peut-être un problème d’attente d’allocation de mémoire.

Déterminer si une attente `RESOURCE_SEMAHPORE` est une attente principale

Utilisez la requête suivante pour déterminer si une attente RESOURCE_SEMAHPORE est une attente principale

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;

Identifier les instructions de consommation élevée de la mémoire

Si vous rencontrez des erreurs de mémoire insuffisante, consultez sys.dm_os_out_of_memory_events.

Utilisez la requête suivante pour identifier les instructions de consommation élevée de la mémoire :

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;

Identifier les allocations de mémoire

Utilisez la requête suivante pour identifier les 10 principales allocations de mémoire actives :

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;

Calcul des tailles de base de données et d’objets

La requête suivante renvoie la taille de votre base de données en mégaoctets :

-- 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 requête suivante retourne la taille d’objets individuels (en mégaoctets) dans votre base de données :

-- 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

Analyse des connexions

La vue sys.dm_exec_connections permet de récupérer des informations sur les connexions établies avec une instance gérée spécifique, et les détails de chaque connexion. En outre, la vue sys.dm_exec_sessions permet de récupérer des informations sur toutes les connexions utilisateur et tâches internes actives.

La requête suivante récupère des informations sur la connexion en cours :

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;

Surveiller l’utilisation des ressources

Vous pouvez surveiller l’utilisation des ressources à l’aide du Magasin des requêtes, comme vous le feriez dans SQL Server.

Vous pouvez également surveiller l’utilisation à l’aide des bases de données sys.dm_db_resource_stats et sys.server_resource_stats.

sys.dm_db_resource_stats

Vous pouvez utiliser la vue sys.dm_db_resource_stats dans chaque base de données. La vue sys.dm_db_resource_stats représente les données d’utilisation récente des ressources par rapport au niveau de service. Les pourcentages moyens d’UC, d’E/S des données, d’écritures du journal et de mémoire sont enregistrés toutes les 15 secondes et conservés pendant une heure.

Étant donné que cette vue fournit un aperçu plus granulaire de l’utilisation des ressources, utilisez d’abord sys.dm_db_resource_stats pour n’importe quelle analyse d’état actuel ou pour la résolution des problèmes. Par exemple, cette requête affiche l’utilisation moyenne et maximale des ressources pour la base de données actuelle sur la dernière heure :

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;

Pour les autres requêtes, consultez les exemples dans sys.dm_db_resource_stats.

sys.server_resource_stats

Vous pouvez utiliser sys.server_resource_stats pour retourner l’utilisation du processeur, les E/S et les données de stockage d’une instance managée Azure SQL. Les données sont collectées et agrégées dans des intervalles de cinq minutes. Une ligne est créée pour chaque rapport de 15 secondes. Les données retournées incluent l’utilisation du processeur, la taille de stockage, l’utilisation des E/S et la référence SKU de l’instance managée. Les données historiques sont conservées pendant environ 14 jours.

L’exemple présente différentes manières d’utiliser la vue du catalogue sys.server_resource_stats pour obtenir des informations sur la façon dont votre instance utilise les ressources.

L’exemple suivant retourne l’utilisation moyenne du processeur au cours des sept derniers jours :

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’exemple suivant retourne l’espace de stockage moyen utilisé quotidiennement par votre instance pour permettre l’analyse des tendances de croissance :

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

Nombre maximal de requêtes simultanées

Pour afficher le nombre actuel de requêtes simultanées, exécutez cette requête Transact-SQL sur votre base de données :

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

Pour analyser la charge de travail d’une base de données individuelle, modifiez cette requête pour la filtrer selon la base de données spécifique que vous analysez. Par exemple, si vous utilisez une base de données nommée MyDatabase, cette requête Transact-SQL retourne le nombre de requêtes simultanées dans cette base de données :

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

Il s’agit simplement d’un instantané à un point unique dans le temps. Pour obtenir une meilleure compréhension de votre charge de travail et des exigences liées aux demandes simultanées, il vous faut collecter plusieurs échantillons au fil du temps.

Nombre maximal de connexions simultanées

Vous pouvez analyser vos modèles d'utilisateur et d'application pour avoir une idée de la fréquence des connexions. Vous pouvez également exécuter des charges réelles dans un environnement de test pour vous assurer que vous n’atteignez pas cette limite ou d’autres limites décrites dans cet article. Il n’existe aucune requête ou vue de gestion dynamique qui peut vous indiquer le nombre de connexions simultanées ou un historique dédié.

Si plusieurs clients utilisent la même chaîne de connexion, le service authentifie chaque connexion. Si 10 utilisateurs se connectent simultanément à une base de données avec les mêmes nom d’utilisateur et mot de passe, dix connexions simultanées seront établies. Cette limite s’applique uniquement à la durée de la connexion et de l’authentification. Si ces mêmes 10 utilisateurs se connectent séquentiellement à la base de données, le nombre de connexions simultanées ne sera jamais supérieur à 1.

Nombre maximal de sessions

Pour afficher le nombre de sessions simultanément actives, exécutez cette requête Transact-SQL sur votre base de données :

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections;

Si vous analysez une charge de travail SQL Server, modifiez la requête pour vous concentrer sur une base de données spécifique. Cette requête vous aide à déterminer les éventuels besoins de votre session pour la base de données si vous envisagez de la déplacer vers 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';

Là encore, ces requêtes renvoient un nombre à un point dans le temps. Si vous collectez plusieurs échantillons au fur et à mesure, vous bénéficierez d’une meilleure compréhension de l’utilisation de votre session.

Analyse des performances des requêtes

Des requêtes lentes ou longues peuvent consommer des ressources système significatives. Cette section montre comment utiliser des vues de gestion dynamique pour détecter quelques problèmes courants liés aux performances de requête.

Recherche des N premières requêtes

L’exemple suivant renvoie des informations relatives aux cinq premières requêtes, classées sur la base du temps processeur moyen. Cet exemple agrège les requêtes selon leur hachage de requête et, logiquement, les requêtes équivalentes sont regroupées sur la base de leur consommation cumulée de ressources.

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;

Surveillance des requêtes bloquées

Des requêtes lentes ou longues peuvent contribuer à une consommation excessive de ressources et être la conséquence de requêtes bloquées. La cause du blocage peut être une conception médiocre d’application, de mauvais plans de requête, le manque d’index utiles, et ainsi de suite. La vue sys.dm_tran_locks permet d’obtenir des informations sur l’activité de verrouillage actuelle dans votre base de données. Pour obtenir un exemple de code, consultez sys.dm_tran_locks. Pour plus d’informations la résolution des problèmes bloquants, consultez Comprendre et résoudre les problèmes bloquants Azure SQL.

Surveillance des interblocages

Dans certains cas, plusieurs requêtes peuvent se bloquer mutuellement, ce qui entraîne un interblocage.

Vous pouvez créer une trace d’Événements étendus pour capturer les événements d’interblocage, puis rechercher les requêtes associées et leurs plans d’exécution dans le magasin des requêtes.

Pour Azure SQL Managed Instance, reportez-vous à la section Outils d’information sur les blocages dans le Guide des interblocages.

Analyse des plans de requête

Un plan de requête inefficace peut également augmenter la consommation du processeur. L’exemple suivant utilise la vue sys.dm_exec_query_stats pour déterminer quelle requête utilise le plus de temps processeur total.

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;

Autres options de surveillance

Surveiller avec l’observateur de base de données (préversion)

L’observateur de base de données collecte des données de surveillance de charge de travail détaillées pour vous donner une vue détaillée des performances, de la configuration et de l’intégrité de la base de données. Les tableaux de bord dans le Portail Azure fournissent une vue à volet unique de votre patrimoine Azure SQL et une vue détaillée de chaque ressource surveillée. Les données sont collectées dans un magasin de données central au sein de votre abonnement Azure. Vous pouvez interroger, analyser, exporter et visualiser les données collectées, et les intégrer aux systèmes en aval.

Si vous souhaitez en savoir plus sur l’observateur de base de données, consultez les articles suivants :

Surveiller avec Azure Monitor

Azure Monitor fournit une série de groupes de collecte de données de diagnostic, métriques et points de terminaison pour la surveillance d’Azure SQL Managed Instance. Pour plus d’informations, consultez Surveiller Azure SQL Managed Instance avec Azure Monitor. Azure SQL Analytics (préversion) est une intégration avec Azure Monitor, où de nombreuses solutions de monitoring ne sont plus en développement. Pour découvrir d’autres d’options de surveillance, consultez Surveillance et réglage des performances dans Azure SQL Managed Instance et Azure SQL Database.

Partager via

Surveillance des performances de Microsoft Azure SQL Managed Instance à l’aide de vues de gestion dynamique

Autorisations

Identifier les problèmes de performances d’UC

Le problème de l’UC se produit maintenant

De nombreuses requêtes individuelles consomment une quantité cumulée d’UC élevée

Des requêtes longues consommant l’UC sont toujours en cours d’exécution

Le problème de l’UC s’est produit dans le passé

Identifier les problèmes de performances d’E/S

Si le problème d’E/S se produit maintenant

Afficher le nombre total d’E/S du journal pour les attentes WRITELOG

Identifier les problèmes de performances `tempdb`

Principales requêtes qui utilisent des variables de table et des tables temporaires

Identifier les transactions longues

Identifier les problèmes de performances d’attente d’allocation de mémoire

Déterminer si une attente `RESOURCE_SEMAHPORE` est une attente principale

Identifier les instructions de consommation élevée de la mémoire

Identifier les allocations de mémoire

Calcul des tailles de base de données et d’objets

Analyse des connexions

Surveiller l’utilisation des ressources

sys.dm_db_resource_stats

sys.server_resource_stats

Nombre maximal de requêtes simultanées

Nombre maximal de connexions simultanées

Nombre maximal de sessions

Analyse des performances des requêtes

Recherche des N premières requêtes

Surveillance des requêtes bloquées

Surveillance des interblocages

Analyse des plans de requête

Autres options de surveillance

Surveiller avec l’observateur de base de données (préversion)

Surveiller avec Azure Monitor

Voir aussi

Étapes suivantes

Commentaires

Ressources supplémentaires

Partager via

Surveillance des performances de Microsoft Azure SQL Managed Instance à l’aide de vues de gestion dynamique

Autorisations

Identifier les problèmes de performances d’UC

Le problème de l’UC se produit maintenant

De nombreuses requêtes individuelles consomment une quantité cumulée d’UC élevée

Des requêtes longues consommant l’UC sont toujours en cours d’exécution

Le problème de l’UC s’est produit dans le passé

Identifier les problèmes de performances d’E/S

Si le problème d’E/S se produit maintenant

Afficher les E/S de la mémoire tampon à l’aide du Magasin de données

Afficher le nombre total d’E/S du journal pour les attentes WRITELOG

Identifier les problèmes de performances tempdb

Principales requêtes qui utilisent des variables de table et des tables temporaires

Identifier les transactions longues

Identifier les problèmes de performances d’attente d’allocation de mémoire

Déterminer si une attente RESOURCE_SEMAHPORE est une attente principale

Identifier les instructions de consommation élevée de la mémoire

Identifier les allocations de mémoire

Calcul des tailles de base de données et d’objets

Analyse des connexions

Surveiller l’utilisation des ressources

sys.dm_db_resource_stats

sys.server_resource_stats

Nombre maximal de requêtes simultanées

Nombre maximal de connexions simultanées

Nombre maximal de sessions

Analyse des performances des requêtes

Recherche des N premières requêtes

Surveillance des requêtes bloquées

Surveillance des interblocages

Analyse des plans de requête

Autres options de surveillance

Surveiller avec l’observateur de base de données (préversion)

Surveiller avec Azure Monitor

Voir aussi

Étapes suivantes

Commentaires

Ressources supplémentaires

Identifier les problèmes de performances `tempdb`

Déterminer si une attente `RESOURCE_SEMAHPORE` est une attente principale