Hoje trouxe um print do que vemos quando perguntamos:<\/p>\n
![\"\"](\"https:\/\/fabriciolima.net\/blog\/wp-content\/uploads\/2024\/05\/mensagem-validacao-copilot-300x172.png\")
<\/p>\n
Ele avisa que est\u00e1 conectando no meu server chamado de copilotsqlbrazil<\/strong> e na minha base CopilotSQL.<\/strong><\/p>\n Se n\u00e3o aparecer essa mensagem enquanto ele processa sua resposta \u00e9 bem prov\u00e1vel que ele nem chegou a se conectar na sua base para fazer valida\u00e7\u00f5es.<\/p>\n Resposta do Copilot:<\/strong><\/p>\n 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.<\/p>\n 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.<\/p>\n Here is a summary of the blocking sessions:<\/p>\n 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.<\/p>\n To avoid blocking sessions in the future, consider the following best practices:<\/p>\n For more information on understanding and resolving blocking, you can refer to the official Microsoft documentation on Azure SQL Database.<\/p>\n 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.<\/p>\n By addressing these blocking sessions and following the best practices, you can improve the performance of your SQL database.<\/p>\n Click here<\/strong> to view the query or queries we used for your answer in Azure Portal’s SQL editor.<\/p>\n <\/p>\n Ao clicar nesse link que ele responde, chegamos na query que ele usa para realizar essa valida\u00e7\u00e3o:<\/strong><\/p>\n <\/p>\n <\/p>\n Resultado da query executada na minha base:<\/p>\n <\/p>\n Por que eu achei isso legal e resolvi postar aqui para voc\u00eas?<\/strong><\/p>\n Primeiro para terem mais detalhes de como funciona o Copilot e segundo para que quem n\u00e3o tenha o Copilot ainda possa executar a mesma query que ele usa e fazer a mesma an\u00e1lise que ele faria.<\/p>\n Isso ajuda voc\u00ea a\u00ed hoje mesmo antes de receber o Copilot.<\/p>\n Curtiram?<\/p>\n Estou debatendo por email com o time do Copilot sobre a an\u00e1lise de uma query que o Copilot deveria apontar uma melhoria. Assim que ficar redondo essa an\u00e1lise, vai virar um post aqui para voc\u00eas tamb\u00e9m.<\/p>\n At\u00e9 o pr\u00f3ximo artigo.<\/p>\n Post Anterior:<\/p>\n Azure SQL Copilot (#01) \u2013 Identificando Locks no BD apenas perguntando ao Copilot<\/a><\/p>\n <\/p>\n Abra\u00e7os,<\/p>\n Fabr\u00edcio Lima<\/p>\n CEO na Fabr\u00edcio Lima Solu\u00e7\u00f5es em BD<\/p>\n Microsoft Data Platform MVP<\/p>\n Instagram:@fabriciofrancalima<\/a><\/p>\n\n
\n
WITH cteHead AS (\r\nSELECT\r\nsess.session_id,\r\nreq.request_id,\r\nLEFT(ISNULL(req.wait_type, ''), 50) AS 'wait_type', LEFT(ISNULL(req.wait_resource, ''), 40) AS 'wait_resource',\r\nLEFT(req.last_wait_type, 50) AS 'last_wait_type',\r\nreq.wait_time,\r\nreq.blocking_session_id,\r\nLEFT(req.[status], 15) AS 'request_status',\r\nsess.open_transaction_count AS 'open_tran_count',\r\nreq.command,\r\nreq.plan_handle,\r\nreq.[sql_handle],\r\nconn.most_recent_sql_handle,\r\nLEFT(sess.[status], 15) AS 'session_status',\r\nreq.query_hash,\r\nreq.query_plan_hash\r\nFROM sys.dm_exec_sessions AS sess\r\nLEFT OUTER JOIN sys.dm_exec_requests AS req\r\nON sess.session_id = req.session_id\r\nLEFT OUTER JOIN sys.dm_exec_connections AS conn\r\nON conn.session_id = sess.session_id\r\n),\r\ncteBlockingHierarchy AS (\r\nSELECT\r\nhead.session_id AS head_blocker_session_id,\r\nhead.session_id AS session_id,\r\nhead.blocking_session_id,\r\nhead.request_status,\r\nhead.open_tran_count,\r\nhead.wait_type,\r\nhead.wait_time,\r\nhead.wait_resource,\r\nhead.[sql_handle],\r\nhead.most_recent_sql_handle,\r\n0 AS [level]\r\nFROM cteHead AS head\r\nWHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)\r\nAND head.session_id IN (\r\nSELECT DISTINCT blocking_session_id\r\nFROM cteHead\r\nWHERE blocking_session_id != 0\r\n)\r\nUNION ALL\r\nSELECT\r\nh.head_blocker_session_id,\r\nblocked.session_id,\r\nblocked.blocking_session_id,\r\nblocked.request_status,\r\nblocked.open_tran_count,\r\nblocked.wait_type,\r\nblocked.wait_time,\r\nblocked.wait_resource,\r\nh.[sql_handle],\r\nh.most_recent_sql_handle,\r\n[level] + 1\r\nFROM cteHead AS blocked\r\nINNER JOIN cteBlockingHierarchy AS h\r\nON h.session_id = blocked.blocking_session_id\r\nAND h.session_id != blocked.session_id\r\nWHERE (h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE','CXPACKET')\r\nOR h.wait_type IS NULL)\r\n)\r\nSELECT\r\nbh.session_id AS blocked_session_id,\r\nbh.blocking_session_id AS blocker_session_id,\r\nbh.request_status,\r\nbh.wait_type,\r\nbh.wait_resource,\r\ntxt.[text] AS blocker_query\r\nFROM cteBlockingHierarchy AS bh\r\nOUTER APPLY sys.dm_exec_sql_text(\r\nISNULL([sql_handle], most_recent_sql_handle)\r\n) AS txt;\r\n\r\nSELECT\r\nAVG(avg_cpu_percent) AS 'Average CPU Percent',\r\nMAX(avg_cpu_percent) AS 'Maximum CPU Percent',\r\nAVG(avg_instance_cpu_percent) AS 'Average Instance CPU Percent',\r\nMAX(avg_instance_cpu_percent) AS 'Maximum Instance CPU Percent',\r\nSUM(CASE\r\nWHEN avg_cpu_percent >= 80 THEN 1\r\nELSE 0\r\nEND) \/ 4. AS 'Minutes with CPU Percent over 80%'\r\nFROM sys.dm_db_resource_stats\r\nWHERE end_time >= DATEADD(MINUTE, -60, GETDATE());<\/pre>\n![\"\"](\"https:\/\/fabriciolima.net\/blog\/wp-content\/uploads\/2024\/05\/Resultado-query-Copilot-300x56.jpg\")
<\/p>\n