Monitorando o desempenho da Instância Gerenciada SQL do Microsoft Azure usando exibições de gerenciamento dinâmico
Aplica-se a:
Instância Gerenciada SQL do Azure
A Instância Gerenciada SQL do Microsoft Azure permite que um subconjunto de DMVs (exibições de gerenciamento dinâmico) diagnostique problemas de desempenho, que podem ser causados por consultas bloqueadas ou de longa execução, gargalos de recursos, planos de consulta insatisfatórios e assim por diante. 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 Microsoft Azure usando exibições de gerenciamento dinâmico.
Permissões
Na Instância Gerenciada 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 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 estiver ocorrendo agora, há dois cenários possíveis:
Muitas consultas individuais que, cumulativamente, consomem alta CPU
Utilize 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;
As consultas de execução prolongada que consomem a 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 padrão do Repositório de Consultas usam uma granularidade de 1 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 arquivo de dados (incluindo
PAGEIOLATCH_SH, ,PAGEIOLATCH_EXPAGEIOLATCH_UP). Se o nome do tipo de espera tiver E/S, isso apontará para um problema de E/ S. Se não houver E /S no nome da espera de trava da página, isso apontará para um tipo diferente de problema (por exemplo,tempdbcontenção).WRITE_LOGPara problemas de E/S do log de transações.
Se o problema de E/S estiver ocorrendo agora
Use o sys.dm_exec_requests ou sys.dm_os_waiting_tasks para ver o wait_type e wait_time.
Exibir E/S relacionadas ao buffer usando o Repositório de Consultas
Para a opção 2, você pode usar a seguinte consulta no Repositório de Consultas para E/S relacionadas ao buffer para exibir as duas últimas 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
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 tempdb problemas são PAGELATCH_* (não PAGEIOLATCH_*). No entanto, PAGELATCH_* esperar nem sempre significa que você tem tempdb contenda. Esta espera também pode significar que tem disputa da página de dados de objeto de utilizador devido a pedidos simultâneos dirigidos à mesma página de dados. Para confirmar ainda mais a contenção, use sys.dm_exec_requests para confirmar tempdb que o valor wait_resource começa com 2:x:y onde 2 é o ID do banco de dados, x é o ID do arquivo e y é tempdb o ID da página.
Para tempdb contenção, um método comum é reduzir ou reescrever o código do aplicativo que depende do tempdb. As áreas de uso comum tempdb incluem:
- Tabelas temporárias
- Variáveis de tabela
- Parâmetros de valor de tabela
- Uso do repositório de versões (associado a transações de longa duração)
- Consultas com planos de consulta que utilizam ordenações, associações 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 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 alto uso da CPU, você pode ter um problema de espera de concessão de memória.
Determinar se uma espera é uma RESOURCE_SEMAHPORE espera superior
Use a consulta a seguir para determinar se uma espera é uma RESOURCE_SEMAHPORE 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 que consomem muita memória
Se encontrar erros de memória esgotada, veja 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 os tamanhos da base de dados e de objetos
A consulta seguinte devolve o tamanho da base 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 seguinte devolve o tamanho de objetos individuais (em megabytes) na base 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
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.
Você também pode monitorar o uso usando sys.dm_db_resource_stats e sys.server_resource_stats.
sys.dm_db_resource_stats
Você pode usar a visualização sys.dm_db_resource_stats em todos os bancos de dados. A sys.dm_db_resource_stats exibição mostra dados de uso de recursos recentes relativos à camada de serviço. As porcentagens médias de CPU, E/S de dados, gravações de log e memória são registradas a cada 15 segundos e mantidas por 1 hora.
Como essa exibição fornece uma visão mais granular do uso de recursos, use sys.dm_db_resource_stats primeiro para qualquer análise de estado atual ou solução de problemas. Por exemplo, esta consulta mostra o uso médio e máximo de recursos para o banco de dados atual na ú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_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.
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; GOO 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';
Este é apenas um instantâneo em um único ponto no tempo. Para entender melhor sua carga de trabalho e os requisitos de solicitação simultânea, você precisará coletar muitas amostras ao longo do tempo.
Máximo de logins simultâneos
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 há uma única consulta ou visualização de gerenciamento dinâmico (Detran) que possa mostrar contagens ou histórico de login simultâneo.
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 início de sessão e da 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 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 do 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 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';
Novamente, essas consultas retornam uma contagem point-in-time. Se você coletar várias amostras ao longo do tempo, terá a melhor compreensão do uso da sessão.
Monitorando 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 o hash de consulta, para que as consultas logicamente equivalentes sejam agrupadas pelo 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;
Monitoramento de 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 impasses
Em alguns casos, duas ou mais consultas podem bloquear mutuamente uma à outra, resultando em um impasse.
Você pode criar um Eventos Estendidos, rastrear um banco de dados para capturar eventos de deadlock e, em seguida, localizar 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.
Monitorando 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
Monitorar com o SQL Insights (visualização)
O Azure Monitor SQL Insights (visualização) é uma ferramenta para monitorar instâncias da Instância Gerenciada SQL do Azure, bancos de dados no Banco de Dados SQL do Azure e SQL Server em VMs SQL do Azure. Este serviço utiliza um agente remoto para capturar dados de vistas de gestão dinâmica (DMVs) e encaminha os dados para o Azure Log Analytics, onde podem ser monitorizados e analisados. Você pode exibir esses dados do Azure Monitor em modos de exibição fornecidos ou acessar os dados de log diretamente para executar consultas e analisar tendências. Para começar a usar o Azure Monitor SQL Insights (visualização), consulte Habilitar o SQL Insights (visualização).
Monitorizar com o Azure Monitor
O Azure Monitor fornece uma variedade de grupos de coleta de dados de diagnóstico, métricas e pontos de extremidade para monitorar a Instância Gerenciada SQL do Azure. Para obter mais informações, consulte Monitorar a instância gerenciada SQL do Azure com o Azure Monitor. O Azure SQL 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.
Consulte também
- Exibições e funções de gerenciamento dinâmico (Transact-SQL)
- Visualizações de gerenciamento dinâmico do sistema
Próximos passos
- Introdução ao Banco de Dados SQL do Azure e à Instância Gerenciada SQL do Azure
- Ajustar aplicativos e bancos de dados para desempenho na Instância Gerenciada SQL do Azure
- Compreender e resolver problemas de bloqueio do SQL Server
- Analise e evite impasses na Instância Gerenciada SQL do Azure
- sys.server_resource_stats (Instância Gerenciada SQL do Azure)