{"id":3134,"date":"2024-06-07T14:13:30","date_gmt":"2024-06-07T17:13:30","guid":{"rendered":"https:\/\/powertuning.com.br\/?p=3134"},"modified":"2024-06-07T14:20:43","modified_gmt":"2024-06-07T17:20:43","slug":"azure-sql-copilot-sera-que-ele-consegue-analisar-uma-query-e-sugerir-melhorias","status":"publish","type":"post","link":"https:\/\/powertuning.com.br\/blog\/azure-sql-copilot-sera-que-ele-consegue-analisar-uma-query-e-sugerir-melhorias\/","title":{"rendered":"Azure SQL Copilot \u2022 Ser\u00e1 que ele consegue analisar uma query e sugerir melhorias?"},"content":{"rendered":"

Fala Pessoal,<\/p>\n

Seguimos aqui nos testes e feedbacks do SQL Copilot para o time de produto.<\/p>\n

Dessa vez vou mostrar se ele consegue analisar uma query, que provavelmente ser\u00e1 uma das coisas que os clientes mais devem usar nele, principalmente quem n\u00e3o tem um DBA interno.<\/p>\n

Criei uma tabela de teste e inseri alguns registros:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n<\/div>\n<\/td>\n
\n
\n
CREATE<\/span> TABLE<\/span> Client<\/span>(<\/span><\/div>\n
Id_Client<\/span> INT<\/span> IDENTITY<\/span>,<\/span><\/div>\n
[<\/span>Name<\/span>]<\/span> VARCHAR<\/span>(<\/span>500<\/span>)<\/span>,<\/span><\/div>\n
BirtDate<\/span> DATE<\/span>,<\/span><\/div>\n
[<\/span>Address<\/span>]<\/span> VARCHAR<\/span>(<\/span>4000<\/span>)<\/span><\/div>\n
)<\/span><\/div>\n
<\/div>\n
INSERT<\/span> INTO<\/span> Client <\/span>(<\/span>[<\/span>Name<\/span>]<\/span>,<\/span> BirtDate<\/span>,<\/span>[<\/span>Address<\/span>]<\/span>)<\/span><\/div>\n
SELECT<\/span> ‘Fabricio Lima good soccer player’<\/span>,<\/span>‘19850611’<\/span>,<\/span>REPLICATE<\/span>(<\/span>‘good’<\/span>,<\/span>1000<\/span>)<\/span><\/div>\n
<\/div>\n
INSERT<\/span> INTO<\/span> Client <\/span>(<\/span>[<\/span>Name<\/span>]<\/span>,<\/span> BirtDate<\/span>,<\/span>[<\/span>Address<\/span>]<\/span>)<\/span><\/div>\n
SELECT<\/span> ‘Fabiano Amorim bad soccer player’<\/span>,<\/span>‘19680711’<\/span>,<\/span>REPLICATE<\/span>(<\/span>‘bad’<\/span>,<\/span>1300<\/span>)<\/span><\/div>\n
<\/div>\n
<\/div>\n
INSERT<\/span> INTO<\/span> Client <\/span>(<\/span>[<\/span>Name<\/span>]<\/span>,<\/span> BirtDate<\/span>,<\/span>[<\/span>Address<\/span>]<\/span>)<\/span><\/div>\n
SELECT<\/span> [<\/span>Name<\/span>]<\/span>,<\/span> BirtDate<\/span>,<\/span>[<\/span>Address<\/span>]<\/span><\/div>\n
FROM<\/span> Client<\/span><\/div>\n
GO<\/span> 15<\/span><\/div>\n
<\/div>\n
<\/div>\n
INSERT<\/span> INTO<\/span> Client <\/span>(<\/span>[<\/span>Name<\/span>]<\/span>,<\/span> BirtDate<\/span>,<\/span>[<\/span>Address<\/span>]<\/span>)<\/span><\/div>\n
SELECT<\/span> ‘Joe Sack good SQL Server player’<\/span>,<\/span>‘19800711’<\/span>,<\/span>REPLICATE<\/span>(<\/span>‘good’<\/span>,<\/span>1000<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Vamos trabalhar com uma query bem simples para facilitar o aprendizado:<\/p>\n

\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/div>\n<\/td>\n
\n
\n
SELECT <\/span>DISTINCT <\/span>Name<\/span><\/div>\n
FROM <\/span>Client<\/span><\/div>\n
WHERE <\/span>Name<\/span> =<\/span> ‘Joe Sack good SQL Server player’<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Ao rodar essa query no SSMS o SQL Server identifica um missed index e j\u00e1 nos sugere criar um \u00edndice na coluna Name.<\/p>\n

Vamos ver se o Copilot tamb\u00e9m nos sugere isso?<\/p>\n

Ele demora uns 40 segundos para responder e mostra essas mensagens enquanto processa:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Mandei ele melhorar uma query que faltava um \u00edndice e ele realmente achou o missed index igual encontramos l\u00e1 no SSMS:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Resposta completa do Copilot:<\/p>\n

\n

\u201cBased on the analysis of your query, I found a missing index recommendation that could potentially improve the performance of your query. Here are the details:<\/em><\/p>\n