Supervisión del rendimiento de Microsoft Azure SQL Managed Instance mediante vistas de administración dinámicas

Artículo
10/16/2024

Se aplica a: Azure SQL Managed Instance

Microsoft Azure SQL Managed Instance habilita un subconjunto de vistas de administración dinámicas (DMVs) para diagnosticar problemas de rendimiento, que pueden deberse a consultas bloqueadas o de ejecución prolongada, cuellos de botella de recursos, planes de consulta deficientes, etc. En este artículo se ofrece información sobre cómo encontrar problemas comunes de rendimiento con vistas de administración dinámica.

Este artículo trata de Azure SQL Managed Instance; consulte también Supervisión del rendimiento de Microsoft Azure SQL Database mediante vistas de administración dinámicas.

Permisos

En Instancia administrada de Azure SQL, para realizar consultas en una vista de administración dinámica se requieren los permisos VIEW SERVER STATE.

GRANT VIEW SERVER STATE TO database_user;

En una instancia de SQL Server y en Instancia administrada de Azure SQL, las vistas de administración dinámica devuelven la información de estado del servidor.

Identificar problemas de rendimiento de CPU

Si el consumo de CPU es superior al 80 % durante largos períodos de tiempo, tenga en cuenta los siguientes pasos de solución de problemas:

El problema de CPU se produce en este momento

Si el problema se produce ahora mismo, hay dos escenarios posibles:

Muchas consultas individuales que consumen una gran cantidad de CPU de forma acumulativa

Utilice la siguiente consulta para identificar los principales valores hash de la consulta:

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;

Consultas de larga ejecución que consumen CPU siguen en ejecución

Utilice la siguiente consulta para identificar estas consultas:

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

El problema de CPU se produjo en el pasado

Si el problema se ha producido en el pasado y quiere realizar un análisis de la causa principal, utilice el Almacén de consultas. Los usuarios con acceso a la base de datos pueden utilizar T-SQL para consultar los datos del Almacén de consultas. Las configuraciones predeterminadas del Almacén de consultas utilizan una granularidad de una hora. Utilice la siguiente consulta para observar la actividad de las consultas que consumen mucha CPU. Esta consulta devuelve las 15 consultas que más consumen CPU. No se olvide de cambiar 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;

Una vez identificadas las consultas problemáticas, es hora de optimizar las consultas para reducir el uso de CPU. Si no tiene tiempo para optimizar las consultas, también puede actualizar el cierre de sesión único (SLO) de la instancia administrada para solucionar el problema.

Identificar problemas de rendimiento de E/S

Cuando se identifican problemas de rendimiento de E/S, los principales tipos de espera asociados a los problemas de E/S son:

PAGEIOLATCH_*

En el caso de problemas de E/S de archivos de datos (incluidos PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). Si en el nombre de tipo de espera se incluye IO, indica un problema de E/S. Cuando no hay ninguna ES en el nombre de tiempo de espera de bloqueo de la página, se infiere que hay otro tipo de problema (por ejemplo, contención de tempdb).
WRITE_LOG

En el caso de problemas de E/S del registro de transacciones.

Si el problema de E/S se produce en este momento

Utilice la vista sys.dm_exec_requests o sys.dm_os_waiting_tasks para ver wait_type y wait_time.

En la opción 2, puede usar la consulta siguiente en el Almacén de consultas sobre E/S relacionadas con el búfer para ver las dos últimas horas de actividad con seguimiento:

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

Ver el total de E/S de registros para esperas WRITELOG

Si el tipo de espera es WRITELOG, utilice la siguiente consulta para ver el total de E/S de registros por instrucción:

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

Identificar problemas de rendimiento de `tempdb`

Cuando se identifican problemas de rendimiento de E/S, los principales tipos de espera asociados a problemas de tempdb son PAGELATCH_* (no PAGEIOLATCH_*). Pero las esperas PAGELATCH_* no siempre significan que tenga contención tempdb. Esta espera también puede significar que tiene contención de páginas de datos de objeto de usuario debido a solicitudes simultáneas que se destinan a la misma página de datos. Para confirmar aún más el argumento tempdb, utilice sys.dm_exec_requests para confirmar que el valor wait_resource comienza con 2:x:y donde 2 indica que tempdb es el id. de la base de datos, x es el id. del archivo y y es el id. de la página.

En el caso de la contención de tempdb, un método común consiste en reducir o reescribir el código de la aplicación que se basa en tempdb. Las áreas de uso de tempdb comunes incluyen:

Tablas temporales
Variables de tabla
Parámetros con valores de tabla
Uso del almacén de versiones (asociado a transacciones de larga duración)
Consultas que tienen planes de consultas que usan ordenaciones, combinaciones hash y colas de impresión

Principales consultas que utilizan tablas temporales y variables de tabla

Utilice la siguiente consulta para identificar las principales consultas que usan tablas temporales y variables de tabla:

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;

Identificar transacciones de larga ejecución

Utilice la siguiente consulta para identificar transacciones de larga ejecución. Las transacciones de larga ejecución impiden la limpieza del almacén de versiones.

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;

Identificar problemas de rendimiento de espera de concesión de memoria

Si su principal tipo de espera es RESOURCE_SEMAHPORE y no tiene el problema del uso elevado de la CPU, es posible que lo tenga con la espera de la concesión de memoria.

Determinar si una espera `RESOURCE_SEMAHPORE` es una espera principal

Utilice la siguiente consulta para determinar si una espera RESOURCE_SEMAHPORE es una espera principal.

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;

Identificar instrucciones con un alto consumo de memoria

Si encuentra errores de memoria insuficiente, revise sys.dm_os_out_of_memory_events.

Utilice la siguiente consulta para identificar las instrucciones con un alto consumo de 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
    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;

Identificación de las concesiones de memoria

Utilice la siguiente consulta para identificar las 10 concesiones principales de memoria activa:

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;

Calcular los tamaños de base de datos y objetos

La siguiente consulta devuelve el tamaño de la base de datos en megabytes:

-- 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 consulta siguiente devuelve el tamaño de objetos individuales (en megabytes) de la base de datos:

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

Supervisión de conexiones

Puede usar la vista sys.dm_exec_connections para recuperar información sobre las conexiones establecidas con una instancia administrada específica, así como los detalles de cada conexión. Además, la vista sys.dm_exec_sessions resulta útil para recuperar información sobre todas las conexiones de usuario activas y las tareas internas.

La siguiente consulta recupera información sobre la conexión actual:

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;

Supervisión del uso de recursos

Puede supervisar el uso de recursos mediante el Almacén de consultas, igual que lo haría en SQL Server.

También puede supervisar el uso mediante sys.dm_db_resource_stats y sys.server_resource_stats.

sys.dm_db_resource_stats

Puede usar la vista sys.dm_db_resource_stats en todas las bases de datos. La vista sys.dm_db_resource_stats muestra los datos de uso reciente de recursos en relación con el nivel de servicio. Porcentajes medios para CPU, E/S de datos, escritura de registros y memoria, se registran cada 15 segundos y se mantienen durante una hora.

Dado que esta vista proporciona una panorámica más granular del uso de recursos, use primero sys.dm_db_resource_stats para cualquier análisis o solución de problemas de estado actual. Por ejemplo, esta consulta muestra el uso medio y máximo de recursos para la base de datos actual durante la última hora:

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;

Para otras consultas, consulte los ejemplos de sys.dm_db_resource_stats.

sys.server_resource_stats

Puede usar sys.server_resource_stats para devolver datos de uso de CPU, E/S y almacenamiento de datos para una Instancia administrada de Azure SQL. Los datos se recopilan y se agregan en intervalos de cinco minutos. Hay una fila por cada 15 segundos de informes. Los datos devueltos incluyen el uso de CPU, el tamaño de almacenamiento, el uso de E/S y la SKU de instancia administrada. Los datos históricos se conservan durante 14 días aproximadamente.

En los siguientes ejemplos se muestran distintas maneras en que puede usar la vista de catálogo sys.server_resource_stats para obtener información sobre cómo usa la base de datos los recursos.

En el ejemplo siguiente se devuelve el uso medio de CPU durante los últimos siete días:

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

En el ejemplo siguiente se devuelve el espacio de almacenamiento medio utilizado por la instancia por día, para permitir el análisis de tendencias de crecimiento:

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

Número máximo de solicitudes simultáneas

Para ver el número actual de solicitudes simultáneas, ejecute esta consulta de Transact-SQL en la base de datos:

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

Para analizar la carga de trabajo de una base de datos individual, modifique esta consulta para filtrar por la base de datos específica que quiere analizar. Por ejemplo, si tiene una base de datos local denominada MyDatabase, esta consulta de Transact-SQL devuelve el recuento de solicitudes simultáneas en esa base de datos:

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

Se trata simplemente de una instantánea en un solo momento dado. Para comprender mejor la carga de trabajo y los requisitos de solicitudes simultáneas, debe recopilar muchas muestras durante un período de tiempo.

Número máximo de inicios de sesión simultáneos

Puede analizar los patrones de usuario y aplicación para hacerse una idea de la frecuencia de los inicios de sesión. También podría ejecutar cargas reales en un entorno de prueba para asegurarse de que no está alcanzando este u otros límites descritos en este artículo. No hay una ninguna consulta o vista de administración dinámica (DMV) individuales que puedan mostrar el número o el historial de inicios de sesión simultáneos.

Si varios clientes usan la misma cadena de conexión, el servicio autentica cada inicio de sesión. Si 10 usuarios se conectan de forma simultánea a una base de datos con el mismo nombre de usuario y contraseña, habrá 10 conexiones simultáneas. Este límite se aplica solo a la duración del inicio de sesión y la autenticación. Si los mismos 10 usuarios se conectan a la base de datos de forma secuencial, el número de inicios de sesión simultáneos nunca será superior a uno.

Número máximo de sesiones

Para ver el número de sesiones activas actuales, ejecute esta consulta de Transact-SQL en la base de datos:

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

Si va a analizar una carga de trabajo de SQL Server, modifique la consulta para centrarse en una base de datos específica. Esta consulta le ayuda a determinar las posibles necesidades de sesión para esa base de datos si la mueve a 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';

Nuevamente, estas consultas devuelven un número puntual. Si recopila varias muestras a lo largo de un tiempo, entenderá mejor el uso de la sesión.

Supervisión del rendimiento de las consultas

Las consultas de ejecución lenta o prolongada pueden consumir una cantidad significativa de recursos del sistema. En esta sección se muestra cómo usar vistas de administración dinámica para detectar algunos problemas comunes de rendimiento de las consultas.

Búsqueda de las N mejores consultas

El siguiente ejemplo devuelve información acerca de las cinco consultas principales clasificadas en función del tiempo promedio de CPU. Este ejemplo agrega las consultas conforme a sus hash de consulta, por lo que las consultas lógicamente equivalentes se agrupan por sus consumos de recursos acumulativos.

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;

Supervisión de consultas bloqueadas

Las consultas lentas o de larga ejecución pueden contribuir al consumo excesivo de recursos y ser la consecuencia de consultas bloqueadas. La causa del bloqueo puede ser un diseño deficiente de las aplicaciones, los planes de consulta incorrectos, la falta de índices útiles, etc. Puede usar la vista sys.dm_tran_locks para obtener información sobre la actividad de bloqueo actual en su base de datos. Para obtener código de ejemplo, consulte sys.dm_tran_locks. Para obtener más información sobre la solución de problemas de bloqueo, consulte Descripción y resolución de problemas de bloqueo en Azure SQL.

Supervisión de interbloqueos

En algunos casos, dos o más consultas pueden bloquearse mutuamente, lo que da lugar a un interbloqueo.

La creación de un seguimiento de eventos extendidos de una base de datos le permitirá capturar eventos de interbloqueo y, a continuación, buscar consultas relacionadas con estos y sus planes de ejecución en el Almacén de consultas.

Para Azure SQL Managed Instance, consulte las herramientas de interbloqueo en la guía de interbloqueos.

Supervisión de planes de consulta

Un plan de consulta ineficaz también puede aumentar el consumo de CPU. En el ejemplo siguiente, se usa la vista sys.dm_exec_query_stats para determinar qué consulta emplea la mayor cantidad acumulativa de CPU.

SELECT
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
FROM
    (SELECT TOP 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;

Otras opciones de supervisión

Supervisión con el monitor de base de datos (versión preliminar)

El monitor de base de datos recopila datos de supervisión de cargas de trabajo en profundidad para proporcionarle una vista detallada del rendimiento, la configuración y el estado de la base de datos. Los paneles de Azure Portal proporcionan una vista de un solo panel del patrimonio de Azure SQL y una vista detallada de cada recurso supervisado. Los datos se recopilan en un almacén de datos central en la suscripción de Azure. Puede consultar, analizar, exportar, visualizar datos recopilados e integrarlos con sistemas de bajada.

Para obtener más información sobre el monitor de base de datos, vea los siguientes artículos:

Supervisión con Azure Monitor

Azure Monitor proporciona una variedad de grupos de recopilación de datos de diagnóstico, métricas y puntos de conexión para supervisar Azure SQL Managed Instance. Para obtener más información, consulte Supervisión de Azure SQL Managed Instance con Azure Monitor. Azure SQL Analytics (versión preliminar) es una integración con Azure Monitor, donde muchas soluciones de supervisión han dejado de desarrollarse de forma activa. Para ver más opciones de supervisión, consulte Supervisión y ajuste del rendimiento en Azure SQL Managed Instance y Azure SQL Database.

Compartir a través de

Supervisión del rendimiento de Microsoft Azure SQL Managed Instance mediante vistas de administración dinámicas

Permisos

Identificar problemas de rendimiento de CPU

El problema de CPU se produce en este momento

Muchas consultas individuales que consumen una gran cantidad de CPU de forma acumulativa

Consultas de larga ejecución que consumen CPU siguen en ejecución

El problema de CPU se produjo en el pasado

Identificar problemas de rendimiento de E/S

Si el problema de E/S se produce en este momento

Ver el total de E/S de registros para esperas WRITELOG

Identificar problemas de rendimiento de `tempdb`

Principales consultas que utilizan tablas temporales y variables de tabla

Identificar transacciones de larga ejecución

Identificar problemas de rendimiento de espera de concesión de memoria

Determinar si una espera `RESOURCE_SEMAHPORE` es una espera principal

Identificar instrucciones con un alto consumo de memoria

Identificación de las concesiones de memoria

Calcular los tamaños de base de datos y objetos

Supervisión de conexiones

Supervisión del uso de recursos

sys.dm_db_resource_stats

sys.server_resource_stats

Número máximo de solicitudes simultáneas

Número máximo de inicios de sesión simultáneos

Número máximo de sesiones

Supervisión del rendimiento de las consultas

Búsqueda de las N mejores consultas

Supervisión de consultas bloqueadas

Supervisión de interbloqueos

Supervisión de planes de consulta

Otras opciones de supervisión

Supervisión con el monitor de base de datos (versión preliminar)

Supervisión con Azure Monitor

Vea también

Pasos siguientes

Comentarios

Recursos adicionales

Compartir a través de

Supervisión del rendimiento de Microsoft Azure SQL Managed Instance mediante vistas de administración dinámicas

Permisos

Identificar problemas de rendimiento de CPU

El problema de CPU se produce en este momento

Muchas consultas individuales que consumen una gran cantidad de CPU de forma acumulativa

Consultas de larga ejecución que consumen CPU siguen en ejecución

El problema de CPU se produjo en el pasado

Identificar problemas de rendimiento de E/S

Si el problema de E/S se produce en este momento

Ver E/S relacionadas con el búfer mediante el Almacén de consultas

Ver el total de E/S de registros para esperas WRITELOG

Identificar problemas de rendimiento de tempdb

Principales consultas que utilizan tablas temporales y variables de tabla

Identificar transacciones de larga ejecución

Identificar problemas de rendimiento de espera de concesión de memoria

Determinar si una espera RESOURCE_SEMAHPORE es una espera principal

Identificar instrucciones con un alto consumo de memoria

Identificación de las concesiones de memoria

Calcular los tamaños de base de datos y objetos

Supervisión de conexiones

Supervisión del uso de recursos

sys.dm_db_resource_stats

sys.server_resource_stats

Número máximo de solicitudes simultáneas

Número máximo de inicios de sesión simultáneos

Número máximo de sesiones

Supervisión del rendimiento de las consultas

Búsqueda de las N mejores consultas

Supervisión de consultas bloqueadas

Supervisión de interbloqueos

Supervisión de planes de consulta

Otras opciones de supervisión

Supervisión con el monitor de base de datos (versión preliminar)

Supervisión con Azure Monitor

Vea también

Pasos siguientes

Comentarios

Recursos adicionales

Identificar problemas de rendimiento de `tempdb`

Determinar si una espera `RESOURCE_SEMAHPORE` es una espera principal