Table Scan em Tabela com Indice

Question

Pessoal

Boa tarde, o que poderia estar acontecendo, onde em um select está dando table scan sendo que nesta tabela (tbl_Transacao_Doc_CR ), tenho tanto indice por (id_Chave_Controle e id_Transacao_CR) ?

Answer 1

Olá neibala,

Para este caso acredito que o melhor seria ter três índices sendo um índice para id_Chave_Controle, um para id_Transacao_CR e por fim um índice com as duas colunas juntas.

espero ter ajudado.

Abraços.

Answer 2

Nei,

O índice faz a referência para os registros da tabela que compõem os campos que são relacionados à ele, mas os demais campos que estão na tabela não estão vinculados à este índice, então ao localizar os registros que correspondem à sua cláusula WHERE ele obtém os campos indexados e também os incluídos no índice.

Os demais campos que não estão vinculados à este índice (ix_id_chave_controle) são obtidos diretamente na tabela, aí surge o "table scan".

Lembrando que o * (asterísco) é um atalho e não é uma boa prática utilizar em aplicações, recomendo que você recrie este índice com os campos necessários para sua indexação e inclua os demais campos necessários para retornar para a aplicação. Veja abaixo um modelo de CREATE INDEX não clusterizado:

CREATE NONCLUSTERED INDEX [IX_CLIENTE_CONTROLE] ON [dbo].[TBL_TRANSACAO_DOC_CR]
(
	[ID_CHAVE_CONTROLE] ASC,
	[ID_TRANSACAO_CR] ASC
)
INCLUDE ( [NM_CLIENTE],
	[NM_PRODUTO],
	[NM_EMPRESA],
	[DT_ENTRADA]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

Se ajudou na sua solução, não esqueça de marcar como resposta !

Abraços,

Durval Ramos

Microsoft Partner | MTA | MCSA - SQL Server 2012

----------------------------------

Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

Answer 3

Pessoal

    Vendo as dicas informadas, por segurança acabei até criando mais 2 indice, para no final decidir qual seria o melhor caminho a seguinte, e mesmo assim, continua dando table scan, o que poderia estar acontecendo ?

CREATE NONCLUSTERED INDEX [IX_id_Token_chave] ON [dbo].[TBL_TRANSACAO_DOC_CR]
(
	[ID_CHAVE_CONTROLE] ASC,
	[ID_TRANSACAO_CR] ASC
)
 ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_id_Token_transacao] ON [dbo].[TBL_TRANSACAO_DOC_CR]
(
	[ID_TRANSACAO_CR] ASC
	,[ID_CHAVE_CONTROLE] ASC
)
 ON [PRIMARY]
GO

Answer 4

José Diz

    Até o momento ainda não criei nenhum indice clustered, e a tabela está com 244 registros, você tem alguma ideia do que poderia estar acontecendo ?

Answer 5

José Diz

    Valeu pelos link, onde recapitulei alguns pontos que já tinha visto no desenrolar do processo, e para reforçar o problema, onde acabei criando o indice clusterizado, pelo campo id_transacao_doc, campo este único na tabela que é unico, e ele acabou apontando para este que nem faz parte do processo de seleção tanto do where como do inner join, sendo que existe no momento indice para os campos que fazem parte do processo de seleção de dados, o que poderia estar acontecendo, você teria alguma outra ideia do que pode estar acontecendo ? 

  E até para poder entender o que poderia estar acontecendo acabei criando até um procedure, e mesmo com a procedure ele está apontando para o indice do campo que não tem nanda há ver com a pesquisa.

select tsc_doc_cr.vl_AReceber 
	from tbl_Transacao_Doc_CR tsc_doc_cr (nolock)
	inner join nC_RECEBER tb_receber (nolock)
	on tsc_doc_cr.id_Chave_Controle = tb_receber.ChaveControle 
	where tsc_doc_cr.id_Transacao_CR = @id_Transacao

Answer 6

José

   Seu exemplo foi bem didatico, mais refazendo o processo do indice, percebi que ele acaba utililzando o i0 indice, não os outros indice que tem campos relacionado com o processo, o que pode ser ?

CREATE clustered index I0 on tbl_Transacao_Doc_CRx(id_Transacao_doc);
CREATE nonclustered index I1 on tbl_Transacao_Doc_CRx(id_Transacao_CR);
CREATE nonclustered index I2 on tbl_Transacao_Doc_CRx(id_Chave_Controle);

Answer 7

José

      Então eu sou obrigado a utilizar o include, pois refiz o indice, incluindo o include, utilizando o seu exemplo, ai ele apontou, para o indice do include, tem que ser assim mesmo ? 

CREATE nonclustered index I1 on tbl_Transacao_Doc_CRx(id_Transacao_CR) include (id_Chave_Controle, vl_AReceber);

Answer 8

José Diz

       Se no caso eu troca-se a ordem dos campos, na questão de performance com base no select eu teria alguma perca, considerando que o campo faz parte do inner join não do where, conforme exemplo abaixo ?

CREATE nonclustered index I1 on tbl_Transacao_Doc_CRx(id_Chave_Controle) include (id_Transacao_CR, vl_AReceber);

Answer 9

Neibala,

Somente alguns detalhes:

1 - Normalmente Table Scan ocorre quando não existe uma chave primária na Table, isso é fato comprovado.

2 - Podemos ter diversos índices não-clusterizados na mesma table mas somente 1 índice clusterizado.

3 - Alterar a ordem de campos no Select e até mesmo na estrutura de índice, também afeta de forma impactante o processamento e trabalho do plano de execução, veja o código abaixo e também no meu blog você poderá obter mais informações:

Create Database Exemplos

--Criando as Tabelas fisicamente--
CREATE TABLE dbo.Tabela1 
(Coluna1 int NOT NULL,
 Coluna2 int NULL,
 Coluna3 int NULL,
 Coluna4 varchar(50) NULL)
GO

CREATE TABLE dbo.Tabela2 
(Coluna1 int NOT NULL,
 Coluna2 int NULL,
 Coluna3 int NULL,
 Coluna4 varchar(50) NULL)
GO

-- Adicionando Primary Key --
ALTER TABLE dbo.Tabela1 
 ADD CONSTRAINT PK_Tabela1 PRIMARY KEY CLUSTERED (Coluna1) 
GO

ALTER TABLE dbo.Tabela2 
 ADD CONSTRAINT PK_Tabela2 PRIMARY KEY CLUSTERED (Coluna1)
GO

-- Populando as tabelas --
DECLARE @valor INT

SET @valor=1

WHILE @valor < 1000
BEGIN  
   INSERT INTO dbo.Tabela1(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,'Valores')
   INSERT INTO dbo.Tabela2(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,'Valores')
   SET @valor=@valor+1
END
GO

-- Criando Índice na Tabela1 com Multiplas Colunaunas --
CREATE NONCLUSTERED INDEX IND_Tabela1_Coluna2_Coluna3 
ON dbo.Tabela1 (Coluna3,Coluna2)
 WITH (STATISTICS_NORECOMPUTE = OFF, 
            IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS = ON, 
            ALLOW_PAGE_LOCKS = ON) 
  ON [PRIMARY]
GO

--Ativando a exibição de informações sobre Estatísticas com retorno em Tela--
SET STATISTICS IO ON

--Removendo todos os buffers limpos do Pool de buffers do SQL Server--
DBCC DROPCLEANBUFFERS
GO

--Consultando dados--
SELECT * FROM dbo.Tabela1 WHERE Coluna3=99
GO

--Removendo todos os buffers limpos do Pool de buffers do SQL Server--
DBCC DROPCLEANBUFFERS
GO

--Consultando dados--
SELECT * FROM dbo.Tabela1 WHERE Coluna2=99
GO

--Removendo todos os buffers limpos do Pool de buffers do SQL Server--
DBCC DROPCLEANBUFFERS
GO

--Consultando dados--
SELECT * FROM dbo.Tabela1 INNER JOIN dbo.Tabela2 
                                                ON dbo.Tabela1.Coluna2 = dbo.Tabela2.Coluna1
 WHERE dbo.Tabela1.Coluna3=255       
GO

--Removendo todos os buffers limpos do Pool de buffers do SQL Server--
DBCC DROPCLEANBUFFERS
GO

--Consultando dados--
SELECT * FROM dbo.Tabela1 INNER JOIN dbo.Tabela2 
                                                ON dbo.Tabela1.Coluna3 = dbo.Tabela2.Coluna1
 WHERE dbo.Tabela1.Coluna2=255       
GO

--Desativando a exibição de informações sobre Estatísticas com retorno em Tela--
SET STATISTICS IO OFF

---

Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

Answer 10

Junior

   Seu exemplo foi muito didatico vou fazer alguns testes aqui e depois dou um retorno do resultado.