Monitorando o desempenho da Instância Gerenciada SQL do Azure usando exibições de gerenciamento dinâmico

Aplica-se a:Azure SQL Managed Instance

A Instância Gerenciada SQL do Azure permite que um subconjunto de exibições de gerenciamento dinâmico (DMVs) diagnostique problemas de desempenho. Consultas bloqueadas ou de longa duração, gargalos de recursos, planos de consulta insatisfatórios e assim por diante podem causar esses problemas. Este artigo fornece informações sobre como detetar problemas comuns de desempenho usando exibições de gerenciamento dinâmico.

Este artigo é sobre a Instância Gerenciada SQL do Azure. Consulte também Monitorando o desempenho do Banco de Dados SQL do Azure usando exibições de gerenciamento dinâmico.

Permissions

Na Instância Gerida do SQL do Azure, a consulta de uma vista de gestão dinâmica requer as permissões VIEW SERVER STATE.

GRANT VIEW SERVER STATE TO database_user;

No SQL Server e na Instância Gerenciada SQL do Azure, as exibições de gerenciamento dinâmico retornam informações de estado do servidor.

Identificar problemas de desempenho da CPU

Se o consumo de CPU estiver acima de 80% por longos períodos de tempo, considere as seguintes etapas de solução de problemas.

O problema da CPU está ocorrendo agora

Se o problema está ocorrendo agora, há dois cenários possíveis.

Muitas consultas individuais que, cumulativamente, consomem alta CPU

Use a seguinte consulta para identificar os principais hashes de 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 longa duração que consomem CPU ainda estão em execução

Use a seguinte consulta para identificar essas 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

O problema da CPU ocorreu no passado

Se o problema ocorreu no passado e você deseja fazer uma análise de causa raiz, use o Repositório de Consultas. Os usuários com acesso ao banco de dados podem usar o T-SQL para consultar dados do Repositório de Consultas. As configurações predefinidas do Query Store utilizam uma granularidade de uma hora. Use a consulta a seguir para examinar a atividade de consultas de alto consumo de CPU. Essa consulta retorna as 15 principais consultas que consomem CPU. Lembre-se de mudar 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;

Depois de identificar as consultas problemáticas, é hora de ajustar essas consultas para reduzir a utilização da CPU. Se você não tiver tempo para ajustar as consultas, também poderá optar por atualizar o SLO da instância gerenciada para solucionar o problema.

Identificar problemas de desempenho de E/S

Ao identificar problemas de desempenho de E/S, os principais tipos de espera associados a problemas de E/S são:

  • PAGEIOLATCH_*

    Para problemas de E/S do ficheiro de dados (incluindo PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). Se o nome do tipo de espera contiver E/S, isso indica um problema de E/S. Se o nome da espera de latch de página não incluir IO, isso indica um tipo diferente de problema (por exemplo, contenção tempdb).

  • WRITE_LOG

    Para problemas de entrada/saída do log de transações.

Se o problema de E/S estiver ocorrendo agora

Utilize sys.dm_exec_requests ou sys.dm_os_waiting_tasks para ver wait_type e wait_time.

Para a opção 2, pode utilizar a seguinte consulta na Query Store para E/S relacionadas com o buffer, para ver as últimas duas horas de atividade registada:

-- 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 E/S de log total para esperas WRITELOG

Se o tipo de espera for WRITELOG, use a seguinte consulta para exibir o total de E/S de log por instrução:

-- Top transaction log consumers
-- Adjust the time window by changing
-- rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
WITH AggregatedLogUsed
AS (SELECT q.query_hash,
           SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_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 tempdb problemas de desempenho

Ao identificar problemas de desempenho de E/S, os principais tipos de espera associados a problemas de tempdb são PAGELATCH_* (não PAGEIOLATCH_*). No entanto, as PAGELATCH_* esperas nem sempre significam tempdb contenção. Esta espera também pode significar que existe contenção na página de dados de objetos de utilizador, devida a pedidos em simultâneo direcionados para a mesma página de dados. Para confirmar melhor a contenção tempdb, use sys.dm_exec_requests para confirmar que o valor wait_resource começa com 2:x:y, em que 2 é o ID da base de dados de tempdb, x é o ID do ficheiro e y é o ID da página.

No caso de contenção de tempdb, um método comum é reduzir ou reescrever o código da aplicação que depende de tempdb. As áreas de uso comum tempdb incluem:

  • Tabelas temporárias
  • Variáveis da tabela
  • Parâmetros com valor de tabela
  • Uso do repositório de versões (associado a transações de longa duração)
  • Consultas com planos de consulta que usam ordenações, junções de hash e spools

Principais consultas que usam variáveis de tabela e tabelas temporárias

Use a consulta a seguir para identificar as principais consultas que usam variáveis de tabela e tabelas temporárias:

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 transações de longa duração

Use a consulta a seguir para identificar transações de longa duração. Transações de longa duração impedem a limpeza do armazenamento de versões.

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 desempenho relacionados com a espera pela atribuição de memória

Se o seu principal tipo de espera for RESOURCE_SEMAHPORE e não tiver um problema de utilização elevada da CPU, poderá ter um problema de espera por concessão de memória.

Determinar se uma espera de RESOURCE_SEMAHPORE é uma das principais esperas

Utilize a seguinte consulta para determinar se uma espera RESOURCE_SEMAHPORE é uma das principais esperas:

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 instruções que consomem muita memória

Se você encontrar erros de falta de memória, revise sys.dm_os_out_of_memory_events.

Use a consulta a seguir para identificar instruções que consomem muita memória:

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;

Identificar as concessões de memória

Use a seguinte consulta para identificar as 10 principais concessões de memória ativa:

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 tamanhos de banco de dados e objetos

A consulta a seguir retorna o tamanho do banco de dados (em 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

A consulta a seguir retorna o tamanho de objetos individuais (em megabytes) em seu banco de dados:

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

Monitorar conexões

Você pode usar a exibição sys.dm_exec_connections para recuperar informações sobre as conexões estabelecidas para uma instância gerenciada específica e os detalhes de cada conexão. Além disso, a visualização sys.dm_exec_sessions é útil ao recuperar informações sobre todas as conexões de usuário ativas e tarefas internas.

A consulta a seguir recupera informações sobre a conexão atual:

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;

Monitorar o uso de recursos

Você pode monitorar o uso de recursos usando o Repositório de Consultas, assim como faria no SQL Server.

Também pode monitorizar a utilização usando sys.server_resource_stats.

sys.server_resource_stats

Você pode usar sys.server_resource_stats para retornar dados de uso da CPU, E/S e armazenamento para uma Instância Gerenciada SQL do Azure. Os dados são recolhidos e agregados em intervalos de cinco minutos. Há uma linha para cada relatório de 15 segundos. Os dados retornados incluem uso da CPU, tamanho do armazenamento, utilização de E/S e SKU de instância gerenciada. Os dados históricos são retidos por aproximadamente 14 dias.

Os exemplos mostram diferentes maneiras de usar a exibição de sys.server_resource_stats catálogo para obter informações sobre como sua instância usa recursos.

  1. O exemplo a seguir retorna o uso médio da CPU nos últimos sete dias:

    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

  2. O exemplo a seguir retorna o espaço de armazenamento médio usado por sua instância por dia, para permitir a análise de tendência de crescimento:

    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

Máximo de solicitações simultâneas

Para ver o número atual de solicitações simultâneas, execute esta consulta Transact-SQL em seu banco de dados:

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

Para analisar a carga de trabalho de um banco de dados individual, modifique essa consulta para filtrar o banco de dados específico que você deseja analisar. Por exemplo, se você tiver um banco de dados chamado MyDatabase, esta consulta Transact-SQL retornará a contagem de solicitações simultâneas nesse banco de dados:

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

Isto é apenas uma captura num único momento. Para entender melhor sua carga de trabalho e os requisitos de solicitação simultânea, você precisará coletar muitas amostras ao longo do tempo.

Número máximo de inícios de sessão em simultâneo

Você pode analisar seus padrões de usuário e aplicativo para ter uma ideia da frequência de logins. Você também pode executar cargas do mundo real em um ambiente de teste para garantir que não esteja atingindo este ou outros limites que discutimos neste artigo. Não existe uma única consulta nem vista de gestão dinâmica (DMV) que lhe possa mostrar o número de inícios de sessão em simultâneo ou o respetivo histórico.

Se vários clientes usarem a mesma cadeia de conexão, o serviço autenticará cada login. Se 10 usuários se conectarem simultaneamente a um banco de dados usando o mesmo nome de usuário e senha, haverá 10 logins simultâneos. Este limite aplica-se apenas à duração do processo de início de sessão e autenticação. Se os mesmos 10 usuários se conectarem ao banco de dados sequencialmente, o número de logins simultâneos nunca será maior que um.

Máximo de sessões

Para ver o número de sessões ativas atuais, execute esta consulta Transact-SQL em seu banco de dados:

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

Se você estiver analisando uma carga de trabalho do SQL Server, modifique a consulta para se concentrar em um banco de dados específico. Esta consulta ajuda a determinar possíveis necessidades de sessão para o banco de dados se você estiver pensando em movê-lo para o 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';

Mais uma vez, estas consultas devolvem uma contagem num determinado momento. Se recolher várias amostras ao longo do tempo, terá uma melhor compreensão da utilização da sessão.

Monitorar o desempenho da consulta

Consultas lentas ou de longa duração podem consumir recursos significativos do sistema. Esta seção demonstra como usar exibições de gerenciamento dinâmico para detetar alguns problemas comuns de desempenho de consulta.

Encontrar as principais consultas N

O exemplo a seguir retorna informações sobre as cinco principais consultas classificadas pelo tempo médio da CPU. Este exemplo agrega as consultas de acordo com seu hash de consulta, de modo que consultas logicamente equivalentes sejam agrupadas por seu consumo cumulativo de recursos.

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;

Monitorar consultas bloqueadas

Consultas lentas ou de longa duração podem contribuir para o consumo excessivo de recursos e ser a consequência de consultas bloqueadas. A causa do bloqueio pode ser um design de aplicativo ruim, planos de consulta incorretos, a falta de índices úteis e assim por diante. Você pode usar a visualização sys.dm_tran_locks para obter informações sobre a atividade de bloqueio atual no banco de dados. Para obter um código de exemplo, consulte sys.dm_tran_locks. Para obter mais informações sobre como solucionar problemas de bloqueio, consulte Compreender e resolver problemas de bloqueio do SQL do Azure.

Monitorização de interbloqueios

Em alguns casos, duas ou mais consultas podem bloquear-se mutuamente, resultando num interbloqueio.

Você pode criar um rastreamento de Eventos Estendidos para um banco de dados para capturar eventos de deadlock. Em seguida, você pode encontrar consultas relacionadas e seus planos de execução no Repositório de Consultas.

Para a Instância Gerenciada SQL do Azure, consulte as ferramentas Deadlock no guia Deadlocks.

Monitorizar planos de consulta

Um plano de consulta ineficiente também pode aumentar o consumo de CPU. O exemplo a seguir usa a exibição sys.dm_exec_query_stats para determinar qual consulta usa a CPU mais cumulativa.

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;

Outras opções de monitorização

Monitorizar com observador de base de dados (pré-visualização)

O inspetor de banco de dados coleta dados detalhados de monitoramento da carga de trabalho para fornecer uma visão detalhada do desempenho, da configuração e da integridade do banco de dados. Os painéis no portal do Azure fornecem uma vista unificada do seu ambiente do SQL do Azure e uma vista detalhada de cada recurso monitorizado. Os dados são coletados em um armazenamento de dados central em sua assinatura do Azure. Você pode consultar, analisar, exportar, visualizar os dados coletados e integrá-los com sistemas downstream.

Para obter mais informações sobre o inspetor de banco de dados, consulte os seguintes artigos:

Monitorar com o Azure Monitor

O Azure Monitor fornece uma variedade de grupos de recolha de dados de diagnóstico, métricas e pontos finais para monitorizar a Instância Gerida de SQL do Azure. Para obter mais informações, consulte Monitorar a instância gerenciada SQL do Azure com o Azure Monitor. O SQL do Azure Analytics (visualização) é uma integração com o Azure Monitor, onde muitas soluções de monitoramento não estão mais em desenvolvimento ativo. Para obter mais opções de monitoramento, consulte Monitoramento e ajuste de desempenho na Instância Gerenciada SQL do Azure e no Banco de Dados SQL do Azure.

Conteúdo relacionado

  • Last updated on