Arquivo da categoria ‘T-SQL’

Olá pessoal,

Recentemente passei por uma situação um tanto inusitada no trabalho. Fui acionado para analisar um DELETE com cláusula WHERE em uma subquery que deveria ter dado erro de sintaxe pelo fato do campo utilizado na subquery não fazer parte da tabela da mesma. O SQL Server só não deu erro como quase deletou todos os registros da tabela, e isso só não ocorreu pois o DBA percebeu a demora e cancelou a operação. Ficou confuso? Vamos ao exemplo.

USE tempdb
GO
create table tb01(cod_tb01 int)
create table tb02(cod_tb02 int)
GO
insert into tb01 values(1),(2),(3),(4),(5)
insert into tb02 values(10),(20),(30),(40),(50)
GO
delete from tb01 where cod_tb01 in(select cod_tb01 from tb02)
GO

O DBA que me acionou me falou: O DELETE acima deletou todos os registros da tabela tb01, mas deveria ter dado erro de sintaxe pois a coluna cod_tb01 não faz parte da tabela tb02 e mesmo se fizesse nenhum registro da tb01 existe na tb02. Quando eu executo apenas a subquery o SQL Server dá erro dizendo que a coluna cod_tb01 não faz parte da tabela tb02, mas quando eu executo o statement todo o SQL Server não dá erro de sintaxe e apaga todos os registros da tb01. Por que?

Pois bem, analisando rapidamente e executando apenas a subquery, realmente o campo cod_tb01 não pertence a tabela tb02 e deveria ter dado erro de sintaxe, mas nesse caso acima o SQL Server não reconheceu como erro pois o SELECT da subquery não tem uma relação direta com a query de fora que é o DELETE e o desenvolvedor não usou um alias de tabela para forçar de qual tabela era o campo cod_tb01, ou seja, as queries são independentes para o SQL Server e para o analisador sintático do SQL Server o usuário estava querendo usar propositalmente o campo da tabela tb01 na subquery. Para o SQL Server o DELETE acima teria o mesmo efeito que o DELETE abaixo:

DELETE FROM tb01 WHERE 1 in(SELECT 1 from tb02)

Se o desenvolvedor tivesse escrito o DELETE da forma abaixo teria dado erro de sintaxe, pois da forma abaixo o desenvolvedor está dizendo de qual tabela pertence o campo cod_tb01 e o analisador sintático informaria que não existe o campo cod_tb01 na tabela tb02.

delete from tb01 where cod_tb01 in(select t2.cod_tb01 from tb02 t2)

Analisando o plano de execução do DELETE podemos ver claramente como o SQL Server interpretou a condição WHERE que acabou deletando todos os registros da tabela tb01.

DELETE

Acima podemos ver que o predicado de busca que o SQL Server usou para fazer o WHERE foi:

tb01.cod_01 = tb01.cod_01

Observação:

Esse comportamento não é exclusivo do SQL Server. Juntamente com outro DBA Oracle, executamos o script acima da demonstração e o mesmo comportamento ocorreu no Oracle. Eu acredito que esse comportamento ocorrerá em qualquer banco de dados onde é possível usar subquery, mas não fiz testes em outros SGBDs.

Bom pessoal o que eu quero passar com esse post é:

Tomem cuidado com subqueries. Felizmente nesse caso o DBA cancelou a operação a tempo e os dados não foram apagados. Eu sempre fui contra usar subquery devido a baixa performance que ela tem comparado ao uso de JOIN. A partir de agora sou mais contra ainda o uso de subquery.

Dica: Em muitos casos subqueries são facilmente substituídas pelo uso de JOIN.

Um bom final de ano a todos!

T-SQL Formatter

Publicado: 22 de maio de 2015 em SQL Server, T-SQL, Tools, Virtual PASS BR

Olá pessoal,

Um grande desafio para quem trabalha com desenvolvimento T-SQL, Query Tuning ou até mesmo desenvolvimento de sistema em geral é quando nos deparamos com uma query T-SQL que não está formatada/identada, então gastamos um bom tempo formatando o texto para que ele fique o mais legível possível.

Hoje em dia existem várias ferramentas que podem formatar o texto da query, algumas pagas e outras não.  Hoje eu vou falar de uma que eu uso muito no dia a dia que é a Poor SQL.

Poor SQL é free e open-source e uma grande vantagem dela é que é possível formatar sua query on-line através do próprio site ou instalar um dos diversos plugins offline existente. Atualmente os plugins são para SSMS/Visual Studio, Notepad++ e WinMerge. Além dos plugins também tem uma ferramenta console para formatar vários arquivos simultâneos, aplicação Windows Form e Library para você integrar com sua aplicação, ou seja, tem para todo mundo!

Enjoy!

Olá pessoal, estive um pouco afastado do blog, mas estou de volta e com uma dica legal. Para quem ainda  não sabe é possível visualizar no SQL Server as sugestões de índices com uma série de informações, como por exemplo quando esse índice foi acessado pela última vez, quantidade de seeks que houve nesse índice que ainda não existe, ou até mesmo o impacto que ele terá caso seja criado. O SQL Server consegue armazena temporariamente esses dados até que a instância do SQL Server seja reiniciada e quem gera essas informações é o QO(Query Optimizer) ou Otimizador de Querys. Eu confesso que por várias vezes eu já executei a consulta abaixo para ver as sugestões, mas nunca criei um índice por não saber qual query o usaria esse índice. Segue a query de sugestões(Não me lembro onde achei):

SELECT migs.avg_user_impact
,migs.last_user_seek
,mid.[statement] AS [Database.Schema.Table]
,mid.equality_columns
,mid.inequality_columns
,mid.included_columns
,migs.unique_compiles
,migs.user_seeks
,migs.avg_total_user_cost
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY avg_user_impact DESC

Para maiores informações sobre o Missing Index Details veja aqui: http://msdn.microsoft.com/en-us/library/ms345434.aspx Cansado de ver as sugestões dos índices e sem saber quais queries seriam afetadas eu dei mais uma pesquisada na internet e achei uma query que busca dentro do XML dos planos de execuções que estão armazenados buscando somente pelos planos com sugestão. Segue a query original que faz a busca nos cached plans:

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan',N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
SELECT ECP.[usecounts]
,ECP.[refcounts]
,ECP.[objtype]
,ECP.[cacheobjtype]
,EST.[dbid]
,EST.[objectid]
,EST.[text]
,EQP.[query_plan]
FROM sys.dm_exec_cached_plans AS ECP
CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
WHERE ECP.[usecounts] > 1 -- Plan should be used more then one time (= no AdHoc queries)
AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY ECP.[usecounts] DESC

Para maiores informações sobre essa query vejam aqui: http://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-9af68abc Para facilitar eu adicionei o campo Impact que mostra o impacto positivo que esse índice terá sob a query e coloquei uma busca para por exemplo filtramos apenas os índices sugeridos que terão mais de 90% de impacto positivo com sua criação. Segue minha versão da query:

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan',N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
SELECT 
ECP.[usecounts]
,ECP.[refcounts]
,ECP.[objtype]
,ECP.[cacheobjtype]
,EST.[dbid]
,EST.[objectid]
,EST.[text]
,EQP.[query_plan]
,EQP.[query_plan].value(N'(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','[decimal]') as Impact
FROM sys.dm_exec_cached_plans AS ECP
CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
WHERE ECP.[usecounts] > 1 -- Plan should be used more then one time (= no AdHoc queries)
AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
AND EQP.[query_plan].value(N'(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','[decimal]') > 90
ORDER BY ECP.[usecounts] DESC

Uma observação importante: Nunca levem essas sugestões como regras, pois nem sempre elas são o melhor caminho para melhorar o desempenho de uma consulta. Eu indico que primeiramente entendam o que a consulta faz de fato, analisem o plano de execução e crie você mesmo os índices e após isso é legal comparar com o que o SQL Server sugere, ou caso queiram criar de cara o índice eu sugiro que criem e vejam se o índice está sendo usado corretamente. Espero que gostem e que isso possa ser útil  no dia a dia.

Bem pessoal depois de um bom tempo sem escrever devido há vários motivos profissionais, pessoais aqui estou eu de volta.

Meu gerente me perguntou:

Marcel, como faço para descobrir determinado texto dentro de todas as views do banco?

Resposta:

Simples chefe! Dê um select na syscomments que lá tem um campo chamado text que contém o conteúdo de todas as procedures, views, function, check, rule, trigger e default desde que não estejam criptografados, porém chefe se o código do objeto tiver mais de 4000 caracteres será retornado mais de uma linha para aquele objeto.

Vejam a imagem abaixo do retorno da syscomments de um banco de teste que eu criei com duas procedures, onde uma tem menos de 4 mil caracteres e a outra contém mais de 4 mil caracteres. Notem que as duas primeiras linhas que eu destaquei se refere ao mesmo objeto.

syscomments

Espero que isso possam ajudar vocês no dia a dia.