Monitorar o desempenho do Banco de Dados SQL do Microsoft Azure usando exibições de gerenciamento dinâmico
Aplica-se a:
Banco de Dados SQL do Azure
O Banco de Dados SQL do Microsoft Azure permite que um subconjunto de 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 muito mais.
Este artigo fornece informações sobre como detetar problemas comuns de desempenho consultando exibições de gerenciamento dinâmico via T-SQL. Você pode usar qualquer ferramenta de consulta, como:
Permissões
No Banco de Dados SQL do Azure, dependendo do tamanho da computação e da opção de implantação, consultar um DMV pode exigir a permissão VIEW DATABASE STATE ou VIEW SERVER STATE. Esta última permissão pode ser concedida através da associação à ##MS_ServerStateReader## função de servidor.
Para conceder a permissão VIEW DATABASE STATE a um usuário de banco de dados específico, execute a seguinte consulta como exemplo:
GRANT VIEW DATABASE STATE TO database_user;
Para conceder associação à ##MS_ServerStateReader## função de servidor a um logon para o servidor lógico no Azure, conecte-se ao master banco de dados e execute a seguinte consulta como exemplo:
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login];
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. No Banco de Dados SQL do Azure, eles retornam informações somente sobre seu banco de dados lógico atual.
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, independentemente de o problema da CPU estar ocorrendo agora ou tiver ocorrido no passado.
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 a 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 ids if not parameterized or not parameterized properly WITH AggregatedCPU AS ( SELECT q.query_hash ,SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms ,SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms ,MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms ,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 INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id INNER 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_ms ,avg_cpu_ms ,max_cpu_ms ,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_ms DESC ,query_hash ASC ) AS query_hash_row_number FROM AggregatedCPU ) SELECT OD.query_hash ,OD.total_cpu_ms ,OD.avg_cpu_ms ,OD.max_cpu_ms ,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.query_hash_row_number FROM OrderedCPU AS OD WHERE OD.query_hash_row_number <= 15 --get top 15 rows by total_cpu_ms ORDER BY total_cpu_ms 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 do banco de dados para contornar o problema.
Para obter mais informações sobre como lidar com problemas de desempenho da CPU no Banco de Dados SQL do Azure, consulte Diagnosticar e solucionar problemas de alta CPU no Banco de Dados SQL do Azure.
Identificar problemas de desempenho de E/S
Ao identificar problemas de desempenho de entrada/saída (E/S) de armazenamento, os principais tipos de espera associados a problemas de E/S são:
PAGEIOLATCH_*Para problemas de E/S de arquivos de dados (incluindo
PAGEIOLATCH_SH,PAGEIOLATCH_EX,PAGEIOLATCH_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.
Identificar dados e registrar o uso de E/S
Use a consulta a seguir para identificar dados e registrar o uso de E/S. Se a E/S de dados ou de log estiver acima de 80%, isso significa que os usuários usaram a E/S disponível para a camada de serviço do Banco de Dados SQL do Azure.
SELECT
database_name = DB_NAME()
, UTC_time = end_time
, 'CPU Utilization In % of Limit' = rs.avg_cpu_percent
, 'Data IO In % of Limit' = rs.avg_data_io_percent
, 'Log Write Utilization In % of Limit' = rs.avg_log_write_percent
, 'Memory Usage In % of Limit' = rs.avg_memory_usage_percent
, 'In-Memory OLTP Storage in % of Limit' = rs.xtp_storage_percent
, 'Concurrent Worker Threads in % of Limit' = rs.max_worker_percent
, 'Concurrent Sessions in % of Limit' = rs.max_session_percent
FROM sys.dm_db_resource_stats AS rs --past hour only
ORDER BY rs.end_time DESC;
Para obter mais exemplos usando sys.dm_db_resource_statso , consulte a seção Monitorar uso de recursos mais adiante neste artigo.
Se o limite de E/S tiver sido atingido, você terá duas opções:
- Atualizar o tamanho da computação ou o escalão de serviço
- Identifique e ajuste as consultas que consomem mais E/S.
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
INNER JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
INNER JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
INNER JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
INNER 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 query_hash_row_number
FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.query_hash_row_number
FROM Ordered AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_wait_time_ms
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_ms,
SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms,
SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms,
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
INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
INNER 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 query_hash_row_number
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.query_hash_row_number
FROM OrderedLogUsed AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_log_bytes_used
ORDER BY total_log_bytes_used DESC;
GO
Identificar problemas de desempenho do tempdb
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 começa com 2:x:y onde 2 é o ID do banco de dados, x é o ID do arquivo e y é tempdb o wait_resource 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
Para obter mais informações, consulte tempdb no Azure SQL.
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)') AS 'Database'
, stmt.stmt_details.value('@Schema', 'varchar(max)') AS 'Schema'
, stmt.stmt_details.value('@Table', 'varchar(max)') AS '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
INNER JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;
GO
DROP TABLE #tmpPlan
DROP TABLE #tmp2
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ão persistente (PVS). Para obter mais informações, veja Resolver problemas de recuperação acelerada de bases de dados.
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_SEMAPHORE 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 RESOURCE_SEMAPHORE é uma espera superior
Use a consulta a seguir para determinar se uma espera é uma RESOURCE_SEMAPHORE espera superior. Também indicativo seria um aumento do tempo de RESOURCE_SEMAPHORE espera na história recente. Para obter mais informações sobre como solucionar problemas de espera de concessão de memória, consulte Solucionar problemas de desempenho lento ou memória baixa causados por concessões de memória no SQL Server.
SELECT wait_type,
SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
INNER 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 no Banco de Dados SQL do Azure, revise sys.dm_os_out_of_memory_events. Para obter mais informações, consulte Solucionar erros de falta de memória com o Banco de Dados SQL do Azure.
Primeiro, modifique o script abaixo para atualizar os valores relevantes de start_time e end_time. Em seguida, execute a seguinte consulta 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
INNER JOIN sys.query_store_runtime_stats AS r
ON p.plan_id = r.plan_id
INNER 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
INNER JOIN sys.query_store_query AS q
ON cte.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;
Identificar as 10 principais concessões de memória ativa
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), ' ')) AS 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
INNER 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;
Monitorar conexões
Você pode usar a exibição sys.dm_exec_connections para recuperar informações sobre as conexões estabelecidas com um banco de dados específico ou pool elástico 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.
Ver sessões atuais
A consulta a seguir recupera informações sobre a conexão atual. Para visualizar todas as sessões, remova a WHERE cláusula.
Você verá todas as sessões em execução no banco de dados somente se tiver a permissão VIEW DATABASE STATE no banco de dados ao executar as sys.dm_exec_requests exibições e sys.dm_exec_sessions . Caso contrário, você verá apenas a sessã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
INNER JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE c.session_id = @@SPID; --Remove to view all sessions, if permissions allow
Monitorar o uso de recursos
Você pode monitorar o uso de recursos do Banco de Dados SQL do Azure no nível de consulta usando o SQL Database Query Performance Insight no portal do Azure ou no Repositório de Consultas.
Você também pode monitorar o uso usando estas exibições:
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
Database_Name = DB_NAME(),
tier_limit = COALESCE(rs.dtu_limit, cpu_limit), --DTU or vCore limit
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 AS rs --past hour only
GROUP BY rs.dtu_limit, rs.cpu_limit;
Para outras consultas, consulte os exemplos em sys.dm_db_resource_stats.
sys.resource_stats
A visualização sys.resource_stats no master banco de dados tem informações adicionais que podem ajudá-lo a monitorar o desempenho do banco de dados em sua camada de serviço específica e tamanho de computação. Os dados são recolhidos a cada 5 minutos e são mantidos durante aproximadamente 14 dias. Essa exibição é útil para uma análise histórica de longo prazo de como seu banco de dados usa recursos.
O gráfico a seguir mostra o uso de recursos da CPU para um banco de dados Premium com o tamanho de computação P2 para cada hora em uma semana. Este gráfico começa numa segunda-feira, mostra cinco dias úteis e depois mostra um fim de semana, quando muito menos acontece na aplicação.
A partir dos dados, este banco de dados atualmente tem um pico de carga de CPU de pouco mais de 50% de uso da CPU em relação ao tamanho de computação P2 (meio-dia de terça-feira). Se a CPU for o fator dominante no perfil de recursos do aplicativo, você pode decidir que P2 é o tamanho de computação certo para garantir que a carga de trabalho sempre se encaixe. Se você espera que um aplicativo cresça com o tempo, é uma boa ideia ter um buffer de recursos extra para que o aplicativo nunca atinja o limite de nível de desempenho. Se você aumentar o tamanho da computação, poderá ajudar a evitar erros visíveis pelo cliente que podem ocorrer quando um banco de dados não tem energia suficiente para processar solicitações de forma eficaz, especialmente em ambientes sensíveis à latência. Um exemplo é um banco de dados que oferece suporte a um aplicativo que pinta páginas da Web com base nos resultados de chamadas de banco de dados.
Outros tipos de aplicativos podem interpretar o mesmo gráfico de forma diferente. Por exemplo, se um aplicativo tentar processar dados da folha de pagamento todos os dias e tiver o mesmo gráfico, esse tipo de modelo de "trabalho em lote" pode funcionar bem em um tamanho de computação P1. O tamanho de computação P1 tem 100 DTUs em comparação com 200 DTUs no tamanho de computação P2. O tamanho de computação P1 fornece metade do desempenho do tamanho de computação P2. Assim, 50% do uso da CPU em P2 equivale a 100% de uso da CPU em P1. Se o aplicativo não tiver tempos limites, pode não importar se um trabalho leva 2 horas ou 2,5 horas para terminar, se for feito hoje. Um aplicativo nesta categoria provavelmente pode usar um tamanho de computação P1. Você pode aproveitar o fato de que há períodos de tempo durante o dia em que o uso de recursos é menor, de modo que qualquer "grande pico" pode se espalhar para um dos cochos no final do dia. O tamanho de computação P1 pode ser bom para esse tipo de aplicação (e economizar dinheiro), desde que os trabalhos possam terminar a tempo todos os dias.
O mecanismo de banco de dados expõe informações de recursos consumidos para cada banco de dados ativo na sys.resource_stats exibição do master banco de dados em cada servidor. Os dados na tabela são agregados para intervalos de 5 minutos. Com as camadas de serviço Basic, Standard e Premium, os dados podem levar mais de 5 minutos para aparecer na tabela, portanto, esses dados são mais úteis para análise histórica do que para análise quase em tempo real. Consulte a vista para ver o histórico recente de uma base de dados e para validar se a sys.resource_stats reserva que escolheu apresentou o desempenho pretendido quando necessário.
Nota
No Banco de Dados SQL do Azure, você deve estar conectado ao master banco de dados para consultar sys.resource_stats nos exemplos a seguir.
Este exemplo mostra como os dados nessa exibição são expostos:
SELECT TOP 10 *
FROM sys.resource_stats
WHERE database_name = 'userdb1'
ORDER BY start_time DESC;
O próximo exemplo mostra diferentes maneiras de usar a exibição de sys.resource_stats catálogo para obter informações sobre como seu banco de dados usa recursos:
Para examinar o uso de recursos da semana passada para o banco de dados de usuários, você pode executar esta consulta, substituindo seu próprio nome de banco de dados
userdb1:SELECT * FROM sys.resource_stats WHERE database_name = 'userdb1' AND start_time > DATEADD(day, -7, GETDATE()) ORDER BY start_time DESC;Para avaliar a adequação da carga de trabalho ao tamanho da computação, tem de desagregar cada aspeto das métricas de recursos: CPU, leituras, escritas, número de trabalhos e número de sessões. Aqui está uma consulta revisada usando
sys.resource_statspara relatar os valores médios e máximos dessas métricas de recurso, para cada camada de serviço para a qual o banco de dados foi provisionado:SELECT rs.database_name , rs.sku , storage_mb = MAX(rs.Storage_in_megabytes) , 'Average CPU Utilization In %' = AVG(rs.avg_cpu_percent) , 'Maximum CPU Utilization In %' = MAX(rs.avg_cpu_percent) , 'Average Data IO In %' = AVG(rs.avg_data_io_percent) , 'Maximum Data IO In %' = MAX(rs.avg_data_io_percent) , 'Average Log Write Utilization In %' = AVG(rs.avg_log_write_percent) , 'Maximum Log Write Utilization In %' = MAX(rs.avg_log_write_percent) , 'Average Requests In %' = AVG(rs.max_worker_percent) , 'Maximum Requests In %' = MAX(rs.max_worker_percent) , 'Average Sessions In %' = AVG(rs.max_session_percent) , 'Maximum Sessions In %' = MAX(rs.max_session_percent) FROM sys.resource_stats AS rs WHERE rs.database_name = 'userdb1' AND rs.start_time > DATEADD(day, -7, GETDATE()) GROUP BY rs.database_name, rs.sku;Com essas informações sobre os valores médios e máximos de cada métrica de recurso, você pode avaliar o quão bem sua carga de trabalho se encaixa no tamanho de computação escolhido. Normalmente, os valores médios de fornecem uma boa linha de
sys.resource_statsbase para usar em relação ao tamanho do destino. Deve ser a sua principal vara de medição.Para bancos de dados de modelos de compra de DTU:
Por exemplo, você pode estar usando a camada de serviço Standard com tamanho de computação S2. As porcentagens médias de uso para leituras e gravações de CPU e E/S estão abaixo de 40%, o número médio de trabalhadores está abaixo de 50 e o número médio de sessões está abaixo de 200. Sua carga de trabalho pode se encaixar no tamanho de computação S1. É fácil ver se seu banco de dados se encaixa nos limites de trabalho e sessão. Para ver se um banco de dados se encaixa em um tamanho de computação menor, divida o número DTU do tamanho de computação inferior pelo número DTU do tamanho de computação atual e, em seguida, multiplique o resultado por 100:
S1 DTU / S2 DTU * 100 = 20 / 50 * 100 = 40O resultado é a diferença relativa de desempenho entre os dois tamanhos de computação em porcentagem. Se o uso de recursos não exceder esse valor, sua carga de trabalho poderá se encaixar no tamanho de computação mais baixo. No entanto, você precisa examinar todos os intervalos de valores de uso de recursos e determinar, por porcentagem, com que frequência sua carga de trabalho de banco de dados caberia no tamanho de computação mais baixo. A consulta a seguir gera a porcentagem de ajuste por dimensão de recurso, com base no limite de 40% calculado neste exemplo:
SELECT database_name, 100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent' FROM sys.resource_stats WHERE start_time > DATEADD(day, -7, GETDATE()) AND database_name = 'sample' --remove to see all databases GROUP BY database_name;Com base na camada de serviço do banco de dados, você pode decidir se sua carga de trabalho se encaixa no tamanho de computação mais baixo. Se o objetivo da carga de trabalho do banco de dados for 99,9% e a consulta anterior retornar valores superiores a 99,9% para todas as três dimensões de recursos, sua carga de trabalho provavelmente se encaixará no tamanho de computação mais baixo.
Observar a porcentagem de ajuste também fornece informações sobre se você deve passar para o próximo tamanho de computação mais alto para atingir seu objetivo. Por exemplo, o uso da CPU para um banco de dados de exemplo na semana passada:
Percentagem média de CPU Percentagem máxima de CPU 24.5 100.00 A CPU média é de cerca de um quarto do limite do tamanho de computação, o que se encaixaria bem no tamanho de computação do banco de dados.
Para bancos de dados de modelo de compra DTU e modelo de compra vCore:
O valor máximo mostra que o banco de dados atinge o limite do tamanho de computação. Você precisa passar para o próximo tamanho de computação mais alto? Veja quantas vezes sua carga de trabalho atinge 100% e, em seguida, compare-a com seu objetivo de carga de trabalho de banco de dados.
SELECT database_name, 100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent' FROM sys.resource_stats WHERE start_time > DATEADD(day, -7, GETDATE()) AND database_name = 'sample' --remove to see all databases GROUP BY database_name;Essas porcentagens são o número de amostras que sua carga de trabalho ajusta no tamanho de computação atual. Se essa consulta retornar um valor inferior a 99,9% para qualquer uma das três dimensões de recurso, sua carga de trabalho média de amostra excedeu os limites. Considere mudar para o próximo tamanho de computação mais alto ou usar técnicas de ajuste de aplicativo para reduzir a carga no banco de dados.
Nota
Para conjuntos elásticos, pode monitorizar bases de dados individuais no conjunto, com as técnicas descritas nesta secção. Você também pode monitorar a piscina como um todo. Para informações, consulte Monitorizar e gerir um conjunto elástico.
Máximo de solicitações simultâneas
Para ver o número atual de solicitações simultâneas, execute esta consulta no banco de dados de usuários:
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R;
Para analisar a carga de trabalho de um banco de dados, modifique essa consulta para filtrar o banco de dados específico que você deseja analisar. Primeiro, atualize o nome do banco de dados para o banco de dados desejado e, em seguida, execute a seguinte consulta para localizar a contagem de solicitações simultâneas nesse banco de MyDatabase dados:
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R
INNER JOIN sys.databases AS 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 eventos de login simultâneos
Você pode analisar seus padrões de usuário e aplicativo para ter uma ideia da frequência dos eventos de login. 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.
Nota
Atualmente, esse limite não se aplica a bancos de dados em pools elásticos.
Máximo de sessões
Para ver o número de sessões ativas atuais, execute esta consulta 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. Primeiro, atualize o nome do banco de dados para o banco de MyDatabase dados desejado e, em seguida, execute a seguinte consulta:
SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections AS C
INNER JOIN sys.dm_exec_sessions AS S
ON (S.session_id = C.session_id)
INNER JOIN sys.databases AS 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.
Você pode obter estatísticas históricas sobre sessões consultando a exibição de catálogo sys.resource_stats e revisando a active_session_count coluna.
Calcular tamanhos de banco de dados e 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 size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';
A consulta seguinte devolve o tamanho de objetos individuais (em megabytes) na base de dados:
-- Calculates the size of individual database objects.
SELECT o.name, SUM(ps.reserved_page_count) * 8.0 / 1024 AS size_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS o
ON ps.object_id = o.object_id
GROUP BY o.name
ORDER BY size_mb DESC;
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 usando o modo de exibição de gerenciamento dinâmico sys.dm_exec_query_stats . O modo de exibição contém uma linha por instrução de consulta dentro do plano armazenado em cache e o tempo de vida das linhas está vinculado ao próprio plano. Quando um plano é removido do cache, as linhas correspondentes são eliminadas dessa exibição.
Encontre as principais consultas por tempo de CPU
O exemplo a seguir retorna informações sobre as 15 principais consultas classificadas pelo tempo médio de CPU por execução. 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 15 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 planos de consulta para tempo acumulado de CPU
Um plano de consulta ineficiente também pode aumentar o consumo de CPU. O exemplo a seguir determina qual consulta usa a CPU mais cumulativa do histórico recente.
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 15
qs.plan_handle,
qs.total_worker_time
FROM
sys.dm_exec_query_stats AS 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;
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 exibição para obter informações sobre a sys.dm_tran_locks 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.
Monitorar impasses
Em alguns casos, duas ou mais consultas podem bloquear mutuamente uma à outra, resultando em um impasse.
Você pode criar um rastreamento de Eventos Estendidos em um banco de dados no Banco de Dados SQL do Azure para capturar eventos de deadlock e, em seguida, localizar consultas relacionadas e seus planos de execução no Repositório de Consultas. Saiba mais em Analisar e evitar bloqueios no Banco de Dados SQL do Azure, incluindo um laboratório para causar um impasse no AdventureWorksLT. Saiba mais sobre os tipos de recursos que podem bloquear.
Próximos passos
- Introdução ao Banco de Dados SQL do Azure e à Instância Gerenciada SQL do Azure
- Diagnosticar e resolver problemas de CPU elevada na Base de Dados SQL do Azure
- Otimizar aplicações e bases de dados para desempenho na Base de Dados SQL do Azure
- Compreender e resolver problemas de bloqueio da Base de Dados SQL do Azure
- Analisar e evitar impasses no Banco de Dados SQL do Azure