Monitoramento do desempenho da Instância Gerenciada de SQL do Microsoft Azure usando exibições de gerenciamento dinâmico
Aplica-se a: 
Instância Gerenciada de SQL do Azure
A Instância Gerenciada de SQL do Microsoft Azure permite um subconjunto de DMVs (exibições de gerenciamento dinâmico) para diagnosticar problemas de desempenho, que podem ser causados por consultas bloqueadas ou de longa execução, gargalos de recursos, planos de consulta inadequados etc. Este artigo fornece informações sobre como detectar problemas de desempenho comuns usando exibições de gerenciamento dinâmico.
Este artigo trata da Instância Gerenciada de SQL do Azure. Confira também Monitoramento do desempenho do Banco de Dados SQL do Microsoft Azure usando exibições de gerenciamento dinâmico.
Permissões
No Instância Gerenciada de SQL do Azure, consultar uma exibição de gerenciamento dinâmico requer permissões VIEW SERVER STATE.
GRANT VIEW SERVER STATE TO database_user;
Em uma instância do SQL Server e na Instância Gerenciada de 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 de CPU está ocorrendo agora
Se o problema está ocorrendo no momento, há dois cenários possíveis:
Muitas consultas individuais que consomem cumulativamente alta utilização da CPU
Use a consulta a seguir para identificar os hashes de consulta top:
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 execução que consomem CPU ainda estão em execução
Use a consulta a seguir para identificar os hashes de consulta:
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 analisar a causa raiz, use o Repositório de Consultas. Os usuários com acesso de banco de dados podem usar o T-SQL para consultar os dados dos Repositório de Consultas. As configurações padrão dos Repositório de Dados de Consultas usam uma granularidade de 1 hora. Use a consulta a seguir para examinar a atividade para consultas de consumo de CPU alta. Essa consulta retorna as consultas de consumo de CPU 15 principais. Lembre-se de alterar 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 com problemas, é hora de ajustar as consultas para reduzir a utilização da CPU. Se você não tiver tempo para ajustar as consultas, também pode optar por atualizar o SLO da instância gerenciada para resolver o problema.
Identificar problemas de desempenho de e/s
Ao identificar problemas de desempenho de e/s, os tipos de espera superior associados a problemas de e/s são:
PAGEIOLATCH_*Para problemas de e/s de arquivo de dados (incluindo
PAGEIOLATCH_SH,PAGEIOLATCH_EX,PAGEIOLATCH_UP). Se tiver o nome do tipo de espera e/s nele, ele aponta para um problema de e/s. Se não houver nenhuma E/S no nome do tempo de espera de trava da página, ela apontará para um tipo diferente de problema (por exemplo, contenção detempdb).WRITE_LOGPara problemas de e/s de log de transações.
Se o problema de e/s estiver ocorrendo no momento
Use o . DM exec_requests ou DM os_waiting_tasks para ver os wait_type e wait_time.
Visualizar e/s relacionada ao buffer usando o Repositório de Consultas
Para a opção 2, você pode usar a consulta a seguir em relação ao Repositório de Dados de Consultas para e/s relacionada ao buffer para exibir as últimas duas horas de atividade controlada:
-- 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
Exibir o log total e/s para WRITELOG espera
Se o tipo de espera for WRITELOG, use a seguinte consulta para exibir e/s de log total pela 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 de associado tempdb é de problemas PAGELATCH_* (não PAGEIOLATCH_*). No entanto, PAGELATCH_* esperas sempre significam que você tem tempdb contenção. Essa espera também pode significar que você tenha a contenção de página de dados de objeto de usuário devido a solicitações simultâneas, visando a mesma página de dados. Para confirmar ainda mais a contenção detempdb, use sys.dm_exec_requestss para confirmar que o valor de wait_resource começa com 2:x:y onde 2 é tempdb a ID do banco de dados, x é a ID do arquivo e y é a ID de página.
Para a contenção de tempdb, um método comum é reduzir ou reescrever o código do aplicativo que se baseia em tempdb. Áreas de uso comum tempdb incluem:
- Tabelas temporárias
- Variáveis de tabela
- Parâmetros com valor de tabela
- Uso do repositório de versão ( associado com transações de execução prolongada)
- Consultas que têm planos de consulta que usam classificações e junções de hash 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 tabelas temporárias e variáveis de tabela:
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 as transações de longa execução
Use a consulta a seguir para identificar transações de execução longas. Transações de longa execução impedem a limpeza do repositório de versão.
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 de espera de concessão de memória
Se o seu tipo de espera superior for RESOURCE_SEMAHPORE e você não tiver um problema de uso de CPU alto, você pode ter um problema de concessão de memória em espera.
Determinar se um RESOURCE_SEMAHPORE espera for uma espera superior
Use a seguinte consulta para determinar se uma RESOURCE_SEMAHPORE espera é uma espera superior
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 altas que consomem memória
Se você encontrar erros de memória insuficiente, avalie sys.dm_os_out_of_memory_events.
Use a consulta a seguir para identificar instruções altas consumo de 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 consulta a seguir para identificar 10 principais concessão de memória:
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 os tamanhos de banco de dados e objetos
A seguinte consulta retorna o tamanho do seu 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 do dos objetos individuais (em megabytes) no 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
Monitoramento de conexões
Use a exibição sys.dm_exec_connections para recuperar informações sobre as conexões estabelecidas com uma instância gerenciada específica e os detalhes de cada conexão. Além disso, a exibição sys.dm_exec_sessions é útil ao recuperar informações sobre todas as conexões de usuário e tarefas internas ativas.
A consulta a seguir recupera as 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.
Monitore também o uso com sys.dm_db_resource_stats e sys.server_resource_stats.
sys.dm_db_resource_stats
Você pode usar a exibição sys.dm_db_resource_stats em cada banco de dados. A exibição sys.dm_db_resource_stats mostra dados de uso de recursos recentes relativos à camada de serviço. A porcentagem média de CPU, E/S de dados, gravações de log e memória é registrada a cada 15 segundos e armazenada por 1 hora.
Como essa exibição oferece uma visão mais granular do uso de recursos, use sys.dm_db_resource_stats primeiro para qualquer análise de estado atual ou para solução de problemas. Por exemplo, esta consulta mostra o uso médio e máximo dos recursos do banco de dados atual durante a ú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 outras consultas, consulte os exemplos em sys.dm_db_resource_stats.
sys.server_resource_stats
Você pode usar sys.server_resource_statsretornar dados de uso de CPU, e/s e armazenamento para uma Instância Gerenciada de SQL do Azure. Os dados são coletados e agregados em intervalos de cinco minutos. Há uma linha para cada relatório de 15 segundos. Os dados retornados incluem uso de CPU, tamanho de 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 catálogo sys.server_resource_stats para obter informações sobre como a instância usa os recursos.
O seguinte exemplo 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; GOO seguinte exemplo retorna o espaço de armazenamento médio usado pela instância por dia, a fim de 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 no 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 esta consulta para filtrar o banco de dados específico que 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 no 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';
Isso é apenas um instantâneo em um único ponto no tempo. Para entender melhor sua carga de trabalho e seus requisitos de solicitações simultâneas, você precisará coletar muitas amostras durante algum tempo.
Máximo de logons simultâneos
Você pode analisar seus padrões de usuário e de aplicativo para ter uma ideia da frequência de logons. Você também pode executar cargas reais em um ambiente de teste para ter certeza de que não está atingindo esse ou outros limites mencionados neste artigo. Não há uma única consulta ou DMV (exibição de gerenciamento dinâmico) que mostre a contagem de logons simultâneos ou o histórico.
Se vários clientes usarem a mesma cadeia de conexão, o serviço autenticará cada logon. Se 10 usuários se conectassem simultaneamente a um banco de dados usando o mesmo nome de usuário e senha, existiriam 10 logons simultâneos. Esse limite é aplicado apenas durante o logon e a autenticação. Se os mesmos 10 usuários se conectassem ao banco de dados sequencialmente, o número de logons simultâneos nunca seria maior do que 1.
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 SQL Server, modifique a consulta para se concentrar em um banco de dados específico. Essa consulta ajuda a determinar possíveis necessidades de sessão do banco de dados, caso você esteja considerando 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';
Novamente, essas consultas retornam uma contagem pontual. Se você coletar várias amostras ao longo do tempo, compreenderá melhor o uso da sua sessão.
Monitoramento de desempenho da consulta
Consultas de execução lenta ou longa podem consumir recursos significativos do sistema. Esta seção demonstra como usar exibições de gerenciamento dinâmico para detectar alguns problemas comuns de desempenho de consulta.
Localizando as principais consultas N
O exemplo a seguir retorna informações sobre as cinco principais consultas classificadas pelo tempo médio de CPU. Este exemplo agrega as consultas de acordo com sua hash de consulta, para que as consultas logicamente equivalentes sejam agrupadas em função de seu consumo de recursos cumulativos.
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;
Monitoramento de consultas bloqueadas
Consultas lentas ou demoradas podem contribuir para consumo excessivo de recursos e ser a consequência de consultas bloqueadas. A causa do bloqueio pode ser projeto inadequado de aplicativos, planos de consulta incorretos, a falta de índices úteis e assim por diante. Você pode usar o modo de exibição sys.dm_tran_locks para obter informações sobre a atividade de bloqueio atual no banco de dados. Para ver um código de exemplo, confira sys.dm_tran_locks. Para obter mais informações sobre como solucionar problemas de bloqueio, consulte entender e resolver problemas de bloqueio do SQL do Azure.
Monitoramento de deadlocks
Em alguns casos, duas ou mais consultas podem bloquear-se mutuamente, resultando em um deadlock.
Você pode criar um rastreamento de Eventos Estendidos em um banco de dados para capturar eventos de deadlock e encontrar consultas relacionadas e os respectivos planos de execução no Repositório de Consultas.
Para uma Instância Gerenciada de SQL do Azure, confira as ferramentas Deadlock no guia Deadlocks.
Monitoramento de 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 monitoramento
Monitorar com o observador de banco de dados (preview)
O observador de banco de dados coleta dados detalhados de monitoramento de carga de trabalho para fornecer uma exibição detalhada do desempenho, da configuração e da integridade do banco de dados. Os painéis no portal do Azure fornecem uma visão de painel único do seu patrimônio do SQL do Azure e uma visão detalhada de cada recurso monitorado. Os dados são coletados em um armazenamento de dados central na sua assinatura do Azure. É possível consultar, analisar, exportar, visualizar os dados coletados e integrá-los aos sistemas downstream.
Para obter mais informações sobre o observador de banco de dados, consulte os artigos a seguir:
- Monitorar cargas de trabalho de SQL do Azure com o observador de banco de dados (preview)
- Início Rápido: criar um observador de banco de dados para monitorar o SQL do Azure (preview)
- Criar e configurar um observador de banco de dados (preview)
- Conjuntos e coleção de dados do observador de bancos de dados (preview)
- Analisar dados de monitoramento do observador de banco de dados (preview)
- Perguntas frequentes sobre o observador de banco de dados
Monitorar com Azure Monitor
O Azure Monitor fornece uma variedade de grupos de coleção de dados de diagnóstico, métricas e pontos de extremidade para monitorar a Instância Gerenciada de SQL do Azure. Para obter mais informações, confira Monitorar a Instância Gerenciada de SQL do Azure com o Azure Monitor. A Análise de SQL do Azure (versão prévia) é uma integração com o Azure Monitor, em que muitas soluções de monitoramento não estão mais em desenvolvimento ativo. Para ver mais opções de monitoramento, confira Monitoramento e ajuste de desempenho na Instância Gerenciada de SQL do Azure e no Banco de Dados SQL do Azure.
Confira também
- Exibições e funções de gerenciamento dinâmico (Transact-SQL)
- Exibições de gerenciamento dinâmico do sistema
Próximas etapas
- Introdução ao Banco de Dados SQL do Azure e à Instância Gerenciada de SQL do Azure
- Ajustar aplicativos e bancos de dados para desempenho na Instância Gerenciada de SQL do Azure
- Entender e resolver problemas de bloqueio do SQL Server
- Analisar e evitar deadlocks na Instância Gerenciada de SQL do Azure
- sys.server_resource_stats (Instância Gerenciada de SQL do Azure)