Azure SQL Copilot (#02) – Qual query o Copilot usa para validar a performance do BD? – Fabricio Lima

Azure SQL Copilot (#02) – Qual query o Copilot usa para validar a performance do BD?

31 de maio de 2024Fabrício LimaCopilot, IA, Inteligencia Artificial

Fala Pessoal,

Seguindo nos testes e posts sobre Copilot para SQL no Azure, hoje vou mostrar a query que ele usa para realizar a validação de performance do ambiente.

Ao perguntar ao Copilot como você pode melhorar a performance do ambiente, assim como no post anterior, ele responde como está o consumo de CPU no BD e se tenho algum lock no banco. Não entrei nesse detalhe no post anterior, mas ele também mostra a query que ele usou para fazer essa validação.

Pergunta feita ao Copilot: How can I improve the performance of my SQL database?

Hoje trouxe um print do que vemos quando perguntamos:

Ele avisa que está conectando no meu server chamado de copilotsqlbrazil e na minha base CopilotSQL.

Se não aparecer essa mensagem enquanto ele processa sua resposta é bem provável que ele nem chegou a se conectar na sua base para fazer validações.

Resposta do Copilot:

Based on the CPU usage data from the past hour, it appears that your SQL database is not experiencing a high CPU usage scenario. The average CPU percent was 3.53%, and the maximum CPU percent reached was 95.41%. However, the CPU usage was over 80% for only 1 minute in the past hour. As per the guidelines, a ‘high CPU’ scenario is defined as more than 3 minutes of CPU usage over 80% in the past hour. Therefore, your situation is classified as a ‘low/no CPU’ scenario.

However, the query results indicate that there are blocking sessions in your database. Blocking occurs when one session (the blocker) holds a lock on a specific resource, and a second session attempts to acquire a conflicting lock type on the same resource. This can impact the performance of your SQL database.

Here is a summary of the blocking sessions:

Session 90 is blocked by session 92. The blocker query is “BEGIN tran\r\nUPDATE [TesteCopilot2]\r\nSET Cod = 44”. This query is attempting to update the ‘Cod’ column in the ‘TesteCopilot2’ table.

To resolve these blocking issues, you can use the T-SQL command ‘KILL’ to terminate the lead blocker sessions for immediate relief. However, this should be done with caution as it can impact running transactions.

To avoid blocking sessions in the future, consider the following best practices:

Keep transactions short.
Ensure transactions isolation levels are appropriate (avoid SERIALIZABLE and REPEATABLE READ if possible).
Use proper indexes and tune queries. Ensure long running queries and transactions are optimized.
Look out for implicit transactions, which are common in Java applications.
Continue to use read commit snapshot isolation (RCSI), which is default for Azure SQL Database.

For more information on understanding and resolving blocking, you can refer to the official Microsoft documentation on Azure SQL Database.

Remember, the ‘wait_type’ column in the query results can help you understand different lock types. For more information on this, you can refer to the official Microsoft documentation on sys.dm_os_wait_stats.

By addressing these blocking sessions and following the best practices, you can improve the performance of your SQL database.

Click here to view the query or queries we used for your answer in Azure Portal’s SQL editor.

Ao clicar nesse link que ele responde, chegamos na query que ele usa para realizar essa validação:

WITH cteHead AS (
SELECT
sess.session_id,
req.request_id,
LEFT(ISNULL(req.wait_type, ''), 50) AS 'wait_type', LEFT(ISNULL(req.wait_resource, ''), 40) AS 'wait_resource',
LEFT(req.last_wait_type, 50) AS 'last_wait_type',
req.wait_time,
req.blocking_session_id,
LEFT(req.[status], 15) AS 'request_status',
sess.open_transaction_count AS 'open_tran_count',
req.command,
req.plan_handle,
req.[sql_handle],
conn.most_recent_sql_handle,
LEFT(sess.[status], 15) AS 'session_status',
req.query_hash,
req.query_plan_hash
FROM sys.dm_exec_sessions AS sess
LEFT OUTER JOIN sys.dm_exec_requests AS req
ON sess.session_id = req.session_id
LEFT OUTER JOIN sys.dm_exec_connections AS conn
ON conn.session_id = sess.session_id
),
cteBlockingHierarchy AS (
SELECT
head.session_id AS head_blocker_session_id,
head.session_id AS session_id,
head.blocking_session_id,
head.request_status,
head.open_tran_count,
head.wait_type,
head.wait_time,
head.wait_resource,
head.[sql_handle],
head.most_recent_sql_handle,
0 AS [level]
FROM cteHead AS head
WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
AND head.session_id IN (
SELECT DISTINCT blocking_session_id
FROM cteHead
WHERE blocking_session_id != 0
)
UNION ALL
SELECT
h.head_blocker_session_id,
blocked.session_id,
blocked.blocking_session_id,
blocked.request_status,
blocked.open_tran_count,
blocked.wait_type,
blocked.wait_time,
blocked.wait_resource,
h.[sql_handle],
h.most_recent_sql_handle,
[level] + 1
FROM cteHead AS blocked
INNER JOIN cteBlockingHierarchy AS h
ON h.session_id = blocked.blocking_session_id
AND h.session_id != blocked.session_id
WHERE (h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE','CXPACKET')
OR h.wait_type IS NULL)
)
SELECT
bh.session_id AS blocked_session_id,
bh.blocking_session_id AS blocker_session_id,
bh.request_status,
bh.wait_type,
bh.wait_resource,
txt.[text] AS blocker_query
FROM cteBlockingHierarchy AS bh
OUTER APPLY sys.dm_exec_sql_text(
ISNULL([sql_handle], most_recent_sql_handle)
) AS txt;

SELECT
AVG(avg_cpu_percent) AS 'Average CPU Percent',
MAX(avg_cpu_percent) AS 'Maximum CPU Percent',
AVG(avg_instance_cpu_percent) AS 'Average Instance CPU Percent',
MAX(avg_instance_cpu_percent) AS 'Maximum Instance CPU Percent',
SUM(CASE
WHEN avg_cpu_percent >= 80 THEN 1
ELSE 0
END) / 4. AS 'Minutes with CPU Percent over 80%'
FROM sys.dm_db_resource_stats
WHERE end_time >= DATEADD(MINUTE, -60, GETDATE());

WITH cteHead AS (

SELECT

sess.session_id,

req.request_id,

LEFT(ISNULL(req.wait_type, ''), 50) AS 'wait_type', LEFT(ISNULL(req.wait_resource, ''), 40) AS 'wait_resource',

LEFT(req.last_wait_type, 50) AS 'last_wait_type',

req.wait_time,

req.blocking_session_id,

LEFT(req.[status], 15) AS 'request_status',

sess.open_transaction_count AS 'open_tran_count',

req.command,

req.plan_handle,

req.[sql_handle],

conn.most_recent_sql_handle,

LEFT(sess.[status], 15) AS 'session_status',

req.query_hash,

req.query_plan_hash

FROM sys.dm_exec_sessions AS sess

LEFT OUTER JOIN sys.dm_exec_requests AS req

ON sess.session_id = req.session_id

LEFT OUTER JOIN sys.dm_exec_connections AS conn

ON conn.session_id = sess.session_id

cteBlockingHierarchy AS (

SELECT

head.session_id AS head_blocker_session_id,

head.session_id AS session_id,

head.blocking_session_id,

head.request_status,

head.open_tran_count,

head.wait_type,

head.wait_time,

head.wait_resource,

head.[sql_handle],

head.most_recent_sql_handle,

0 AS [level]

FROM cteHead AS head

WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)

AND head.session_id IN (

SELECT DISTINCT blocking_session_id

FROM cteHead

WHERE blocking_session_id != 0

)

UNION ALL

SELECT

h.head_blocker_session_id,

blocked.session_id,

blocked.blocking_session_id,

blocked.request_status,

blocked.open_tran_count,

blocked.wait_type,

blocked.wait_time,

blocked.wait_resource,

h.[sql_handle],

h.most_recent_sql_handle,

[level] + 1

FROM cteHead AS blocked

INNER JOIN cteBlockingHierarchy AS h

ON h.session_id = blocked.blocking_session_id

AND h.session_id != blocked.session_id

WHERE (h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE','CXPACKET')

OR h.wait_type IS NULL)

)

SELECT

bh.session_id AS blocked_session_id,

bh.blocking_session_id AS blocker_session_id,

bh.request_status,

bh.wait_type,

bh.wait_resource,

txt.[text] AS blocker_query

FROM cteBlockingHierarchy AS bh

OUTER APPLY sys.dm_exec_sql_text(

ISNULL([sql_handle], most_recent_sql_handle)

) AS txt;

SELECT

AVG(avg_cpu_percent) AS 'Average CPU Percent',

MAX(avg_cpu_percent) AS 'Maximum CPU Percent',

AVG(avg_instance_cpu_percent) AS 'Average Instance CPU Percent',

MAX(avg_instance_cpu_percent) AS 'Maximum Instance CPU Percent',

SUM(CASE

WHEN avg_cpu_percent >= 80 THEN 1

ELSE 0

END) / 4. AS 'Minutes with CPU Percent over 80%'

FROM sys.dm_db_resource_stats

WHERE end_time >= DATEADD(MINUTE, -60, GETDATE());

Resultado da query executada na minha base:

Por que eu achei isso legal e resolvi postar aqui para vocês?

Primeiro para terem mais detalhes de como funciona o Copilot e segundo para que quem não tenha o Copilot ainda possa executar a mesma query que ele usa e fazer a mesma análise que ele faria.

Isso ajuda você aí hoje mesmo antes de receber o Copilot.

Curtiram?

Estou debatendo por email com o time do Copilot sobre a análise de uma query que o Copilot deveria apontar uma melhoria. Assim que ficar redondo essa análise, vai virar um post aqui para vocês também.

Até o próximo artigo.

Post Anterior:

Azure SQL Copilot (#01) – Identificando Locks no BD apenas perguntando ao Copilot

Abraços,

Fabrício Lima

CEO na Fabrício Lima Soluções em BD

Microsoft Data Platform MVP

Instagram:@fabriciofrancalima

Linkedin: https://www.linkedin.com/in/fabriciofrancalima/

Consultoria: [email protected]

Compartilhe isso:

Curtir isso:

Relacionado

Deixe uma respostaCancelar resposta