Estou obtendo um comportamento em um plano de consulta que não consigo explicar. A diferença está entre os dois índices filtrados com os quais estou testando. Um usa a where id is not nulle o outro usa where id > 0. Em meus dados reais, obtenho um tempo de execução favorável de 95% usando o > 0índice. Não consigo ver por que eles seriam diferentes ... minhas chaves são inteiros de incremento automático começando em 1 referenciado pela tabela adjacente em uma coluna anulável. Abaixo está um script que irá gerar estruturas e dados análogos aos meus dados de produção.
Observe o seguinte... se você executar esses scripts e, em seguida, comparar as duas instruções de seleção, provavelmente obterá cinquenta e cinquenta desempenhos como eu. No entanto , em meus dados de produção, a consulta que utiliza o > 0índice filtrado escolhe uma varredura de índice em vez de buscar. Essa verificação é executada muito mais rapidamente. Abaixo está uma captura de tela da minha comparação real do plano de consulta. Em segundo lugar, reconstruí esses índices, a fragmentação não é um problema.
Perguntas:
Por que a disparidade? De onde vem a varredura versus busca? Não seria is not nulle > 0seria equivalente em uma junção quando o tipo de dados é identidade int(1,1)?
Esquema + Dados:
if exists (select * from sys.tables where name = 'sometable')
begin drop table sometable end
go
create table sometable (id int not null identity(1,1) primary key
, value nvarchar(50));
go
insert into sometable values ('a test value');
insert into sometable values ('a test value');
insert into sometable values ('a test value');
insert into sometable values ('a test value');
insert into sometable values ('a test value');
go
if exists (select * from sys.tables where name = 'audit')
begin drop table audit end
go
create table audit (id int not null identity(1,1) primary key
, sometable_id int null
, someothertable_id int null
, auditvalue nvarchar(50));
go
declare @count int = 0;
while (@count < 40000)
begin
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'a sometable audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'another sometable audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,1,'irrelevant other table audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'another audit for record one sometable');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'record three audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'another record 3 audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,50,'irrelevant1');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,51,'irrelevant2');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,52,'irrelevant3');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'relevant fourth record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'back to one record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,53,'irrelevant 4');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,54,'irrelevant fifth record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,55,'irrelevant sixth record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (null,56,'irrelevant seventh record');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'a fifth record audit');
insert into audit (sometable_id,someothertable_id,auditvalue) values (floor(rand()*5+1),null,'another fourth record audit');
set @count = (@count + 1);
end
go
--drop index audit.filter_null_audits_for_sometable
create index filter_null_audits_for_sometable on audit(sometable_id,id) include(auditvalue) where sometable_id is not null;
go
--drop index audit.filter_upzero_audits_for_sometable
create index filter_upzero_audits_for_sometable on audit(sometable_id,id) include(auditvalue) where sometable_id > 0;
go
Duas consultas:
select top 50000 a.id sometableid,a.value,b.id auditid,b.auditvalue
from sometable a
join audit b with(index(filter_null_audits_for_sometable)) on a.id = b.sometable_id
select top 50000 a.id sometableid,a.value,b.id auditid,b.auditvalue
from sometable a
join audit b with(index(filter_upzero_audits_for_sometable)) on a.id = b.sometable_id and b.sometable_id > 0
Atualização1
Copiei dados de produção em minhas tabelas de teste. Em vez de cinquenta e cinquenta, os resultados corresponderam ao plano de consulta incluído e reproduziram a disparidade. O cenário de teste é estruturalmente análogo.
Atualização2
select a.id sometableid,a.value,b.id auditid,b.auditvalue
from sometable a
inner merge join audit b with(index(filter_null_audits_for_sometable))
on a.id = b.sometable_id
select a.id sometableid,a.value,b.id auditid,b.auditvalue
from sometable a
join audit b with(index(filter_upzero_audits_for_sometable))
on a.id = b.sometable_id
Esses planos de consulta não serão compilados. Por quê? Eles me forçam a usar > 0como condições de junção de consulta para obter o plano otimizado.
Há um monte de perguntas escondidas aqui.
Vamos começar com, por que o equivalente logicamente
>0produz uma varredura em vez de uma busca.O índice é filtrado em
IS NOT NULL, portanto, o SQL Server sabe que nenhum valor estáNULLnesse índice. No entanto, ele não sabe que todos os valores são>0porque umaIDENTITYpropriedade não é uma restrição. Portanto, para retornar todas as linhas, o>0SQL Server precisa encontrar a primeira linha que realmente existe>0(usando uma operação de busca) e verificar o restante das linhas a partir daí. Se essa varredura estiver cobrindo a maior parte da tabela, gastar as leituras adicionais para a primeira busca pode ser mais caro do que apenas varrer a tabela inteira. Ambas as abordagens levarão ao mesmo resultado em todos os casos. Qual servidor sql escolhe é baseado em estatísticas.Então, por que a verificação é mais rápida?
O SQL Server tem três operadores físicos de junção para escolher: The Nested Loops Join, Hash Join e Merge Join. O Merge Join é de longe o mais rápido, no entanto, requer que ambas as entradas sejam classificadas da mesma maneira. Para obter mais detalhes sobre os operadores de junção, confira minha
JOINsérie aqui: http://sqlity.net/en/1146/a-join-a-day-introduction/ A busca na parte inferior do operador Nested Loop Join é executada para cada linha da entrada superior. São muitas buscas e podem se tornar caras rapidamente. O SQL Server geralmente descobre onde está o chamado "ponto de inflexão", no qual uma varredura completa da tabela é mais barata do que uma busca repetida. No entanto, essa decisão é baseada em estatísticas novamente e geralmente com algumas centenas de linhas.Se o Merge join é mais rápido e os dados já estão classificados, por que o SQL Server não o utiliza para ambas as consultas?
Essa é uma boa pergunta... Geralmente decisões ruins do otimizador são causadas por estatísticas obsoletas (em qualquer tabela envolvida na consulta). No entanto, o otimizador tem limitações e lidar com recursos avançados como índices filtrados pode forçá-lo a sair de sua zona de conforto. Se a atualização das estatísticas não ajudar, precisamos ajudar o otimizador. Existem várias maneiras que podem ser feitas. Você poderia simplesmente fornecer um
MERGE JOINdica. Para esta consulta, isso pode até ser seguro, pois a consulta sempre terá que examinar todas as linhas da tabela filha. No entanto, em geral, as dicas devem ser consideradas um último recurso absoluto, pois evitam que o otimizador se adapte às mudanças nos dados. Você também pode tentar reescrever a consulta de forma que o otimizador inicie em um caminho diferente, levando a um resultado melhor. Isso é comparável à insinuação, apenas oculta - geralmente uma estratégia ainda pior. No entanto, se você pensar em tempos anteriores a 2005, era muito comum utilizar reescritas de consulta apenas para fazer com que o otimizador terminasse com um plano melhor. A vantagem dessa abordagem é que a próxima versão do otimizador pode reconhecer a consulta reescrita como equivalente à original, de modo que essa técnica não tenha efeitos de longo prazo. (Isso também pode ser ruim...)Uma outra abordagem é fornecer informações adicionais ao otimizador. Se você tiver consultas complexas, os detalhes das estatísticas podem não ser refinados o suficiente para que o otimizador faça uma boa escolha. Nesse caso, geralmente ajuda a criar um índice filtrado ou objeto de estatísticas. No seu caso, já estamos olhando para um índice filtrado. O que pode estar faltando é a informação de que todas as linhas são realmente
>0. Portanto, pode ser útil adicionar ambas as condições ao filtro de índice.Embora tudo isso não forneça uma solução real, espero que ajude a entender o que está acontecendo aqui. Para resolver isso, eu começaria adicionando essa segunda condição ao mesmo índice e veria aonde isso o levaria.
Em uma nota diferente: você menciona uma comparação 50-50 de dois planos de execução. Presumo que você esteja se referindo ao "Custo da consulta (relativo ao lote)". Embora o SQL Server exiba essas informações de maneira muito sugestiva, você nunca pode confiar nelas. Esse número é útil para o SQL Server internamente ao comparar diferentes planos de execução para a mesma consulta. No entanto, é absolutamente inútil ao comparar duas consultas não idênticas. É melhor simplesmente ignorar esse valor ao falar sobre desempenho. Use as informações que você pode obter de volta
STATISTICS IOeSTATISTICS TIMEem vez disso.ATUALIZAÇÃO para responder às suas perguntas:
1) Para recuperar as linhas de qualquer consulta, o SQL Server sempre pode executar uma varredura. Nas circunstâncias certas, uma busca pode ser muito mais rápida. Eu estava descrevendo o que o SQL Server teve que fazer para executar uma busca na tabela de auditoria para obter as linhas > 0. Se o SQL Server achar que isso resultaria em mais leituras do que uma varredura (como neste caso), ele está optando pela varredura.
2) Para fazer a dica de junção de mesclagem funcionar, você também precisa informar ao otimizador que realmente não pode haver um valor
<0na tabela. No seu exemplo você poderia fazer assim:Devido à chave estrangeira existente, essa restrição se aplica a ambas as tabelas. Se agora você executar
O SQL Server usará este plano de execução:
Esse é o nosso objetivo. Ele ainda está usando o índice filter_null_audits, mas permitindo a junção de mesclagem agora. Não sei por que essa restrição mudaria se a junção MERGE pudesse ser usada; parece ser uma limitação do otimizador. Novamente, os índices filtrados são uma opção bastante avançada para o otimizador e a implementação pode não estar completa ainda.
3) O conselho sobre não confiar no custo relativo da consulta é de natureza geral e não implica que os números estejam realmente errados para esta consulta específica. Posso construir facilmente duas consultas em que uma mostra um custo relativo de 99%, mas a outra é executada cem vezes mais devagar. Se você tiver um exemplo em mãos com custos relativos corretos, isso significa apenas que você teve sorte. :)
Espero que tenha esclarecido as coisas.