Início / dba / Perguntas / 39101
Accepted
Michael Fredrickson
Asked: 2013-04-03 15:39:24 +0800 CST

Verificação de tabela inesperada com LIKE parametrizado

  • 772

Estou experimentando uma verificação de tabela inesperada no SQL Server 2005 em uma tabela heap ao parametrizar uma LIKEinstrução... mas quando o mesmo valor da variável é codificado, a busca de índice esperada ocorre.

O problema só acontece neste cenário específico ... então não estou confuso sobre como resolver o problema, estou confuso sobre por que isso está acontecendo.

O seguinte T-SQL deve recriar o problema no SQL Server 2005:

IF (OBJECT_ID('tempdb.dbo.#tblTest') IS NOT NULL)
    DROP TABLE dbo.#tblTest
GO

CREATE TABLE dbo.#tblTest (
    ID INT IDENTITY(1, 1),
    SerialNumber VARCHAR(50)
)
GO

-- Populate the table with 10,000 rows
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO dbo.#tblTest VALUES(CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END
GO

-- To recreate the issue, the table must be a heap.
ALTER TABLE dbo.#tblTest ADD CONSTRAINT PK_tblTest PRIMARY KEY NONCLUSTERED (ID)
GO

-- Create a (non-covering) index on serial number.
CREATE NONCLUSTERED INDEX IX_tblTest_SerialNumber ON dbo.#tblTest (SerialNumber)
GO

DECLARE @Criteria VARCHAR(50)
SET @Criteria = '1234%'

-- This produces a Table Scan.
SELECT * 
FROM dbo.#tblTest
WHERE SerialNumber LIKE @Criteria

-- This produces an Index Seek
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE '1234%'

Fui direcionado para este artigo de Paul White, que parece muito relacionado , mas as conclusões/explicações não correspondem ao meu problema específico.

Qualquer visão é apreciada.

sql-server sql-server-2005

1 respostas

  1. Best Answer

    A alegação de que ocorre apenas para índices não agrupados se deve ao fato de que você só tinha duas colunas - uma sendo a coluna indexada e a outra sendo

    1. a chave de clustering (ou seja, a outra coluna) se houver um índice clusterizado
    2. o RID se não houvesse índice clusterizado

    No segundo caso, para satisfazer a parte SELECT *(todas as colunas) de sua consulta, seria necessário executar uma pesquisa cara, portanto, o plano genérico (robusto) para executar uma varredura de tabela de 10.000* registros é escolhido. No primeiro caso, o índice é tudo o que é necessário para satisfazer a cláusula SELECT.

    * Deve-se observar que o número de registros e a cardinalidade do índice também desempenham um papel na determinação do plano.

    Com mais colunas, o plano alterna previsivelmente para um CLUSTERED INDEX SCAN para a instrução LIKE parametrizada, mesmo com um índice clusterizado, de acordo com o teste revisado abaixo.

    IF (OBJECT_ID('tempdb.dbo.#tblTest') IS NOT NULL)
    DROP TABLE dbo.#tblTest
GO

CREATE TABLE dbo.#tblTest (
    ID INT IDENTITY(1, 1),
    SerialNumber VARCHAR(50),
    Othercolumn1 uniqueidentifier default (newid()),
    RowVer timestamp
)
GO

-- Populate the table with 10,000 rows
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO dbo.#tblTest (serialnumber) VALUES(CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END
GO

-- To recreate the issue, the table must be a heap.
ALTER TABLE dbo.#tblTest ADD CONSTRAINT PK_tblTest PRIMARY KEY CLUSTERED (ID)
GO

-- Create a (non-covering) index on serial number.
CREATE NONCLUSTERED INDEX IX_tblTest_SerialNumber ON dbo.#tblTest (SerialNumber)
GO

DECLARE @Criteria VARCHAR(50)
SET @Criteria = '1234%'

-- This produces a Table Scan.
SELECT * 
FROM dbo.#tblTest
WHERE SerialNumber LIKE @Criteria

-- This produces an Index Seek
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE '1234%'

    Aqui estão os planos gerados com base na minha estrutura de tabela revisada. Para o esquema em questão, a parte superior torna-se uma varredura de tabela e a parte inferior torna-se uma pesquisa RID em vez de uma pesquisa de chave - tudo o mais sendo igual.

    plano de execução

    Uma das operações mais caras na execução de uma consulta é primeiro construir o plano de execução. Para ajudar nisso, o SQL Server possui um Plan Cache que armazena o texto das instruções e as configurações SET associadas. O mesmo texto usando configurações SET diferentes pode resultar em comportamento diferente, de modo que é planejado novamente e armazenado como uma entrada separada.

    A consulta não parametrizada é simples de planejar - contém o texto exato '1234%'. O índice VARCHAR em SerialNumber é facilmente pesquisado pela parte que contém o prefixo "1234". O SQL Server também estima a cardinalidade da consulta e invariavelmente para seus dados escolherá o plano INDEX SEEK. Qualquer apresentação posterior da instrução de consulta exata (texto) para o SQL Server conterá o valor estático '1234%' e é seguro reexecutar o mesmo plano com eficiência.

    Por outro lado, a consulta parametrizada é armazenada no Plan Cache (dicionário) digitado pelo texto da instrução ... WHERE SerialNumber LIKE @Criteria. Mesmo que @Criteria no lote atual contenha o valor '1234%' e possa usar um INDEX SEEK, é bem possível que outro usuário envie exatamente a mesma consulta com @Criteria'%9', o que não funcionaria tão bem usando a pesquisa ÍNDICE SEEK + RID. Isso SELECIONARIA 10% dos dados, que geralmente estão acima do ponto de inflexão no qual a busca de índice não é mais favorável. Para robustez e capacidade de reutilização , o plano armazenado em cache para esta consulta (e depois executado) é a versão Table Scan que atenderá a maior variedade de @Criteriavalores com eficiência média entre os valores possíveis.

    • 5

relate perguntas