Início / dba / Perguntas / 318661
Accepted
lifeisajourney
Asked: 2022-10-25 13:55:07 +0800 CST

Criando um índice não clusterizado na chave de cluster com algumas colunas incluídas

  • 772

Tenho 3 tabelas:
test_productInfo: contém informações sobre o produto
test_productCreator: contém todos os usuários do nosso sistema
test_productOwner: contém informações sobre quem é o proprietário de um(s) produto(s). Uma tabela de referência cruzada entre test_productInfo e test_productCreator.

Meu procedimento armazenado leva um productCreatorId ou vários productCreatorIds e uma chave de classificação e retorna as primeiras 1.000 linhas de test_productInfo(todas as colunas) classificadas pela chave de classificação passada. Infelizmente, tenho que selecionar tudo.

Colei a consulta atual que temos abaixo e aqui está o plano de execução: https://www.brentozar.com/pastetheplan/?id=SyKgEEVNj

Estou recebendo dados de 20 usuários. A consulta atual tem classificação transbordando em tempdb.

Adicionei um novo índice não clusterizado em productId, que é a chave de clustering, e incluí todas as chaves de classificação. Modifiquei a consulta para selecionar apenas productId ao classificar para que esse novo índice possa ser usado e armazenei os resultados em uma tabela temporária. Em uma consulta subsequente estou adicionando todas as colunas necessárias e fazendo uma classificação novamente. Esta consulta funciona bem para usuários que têm mais de 1.000 linhas porque não estou classificando mais de 1.000 linhas quando todas as colunas estão sendo selecionadas. Para usuários com menos de 1000 linhas, estou acessando a tabela productInfo duas vezes, mas não observei muita diferença no tempo entre minha consulta atual e a consulta modificada. Aqui está o plano de execução para a consulta modificada: https://www.brentozar.com/pastetheplan/?id=SyA_SVEVs

Com a nova consulta modificada, estou tentando reduzir os derramamentos de classificação e estou vendo algumas melhorias, especialmente quando há mais de 1000 linhas.

Faz sentido criar um índice não clusterizado em uma chave de cluster com algumas colunas incluídas? existe uma maneira melhor de reescrever minha consulta atual?

--drop table test_productInfo
--drop table test_productOwner
--drop table test_productCreator

Create Table test_productInfo
(
productId uniqueIdentifier Primary Key,
productName varchar(100),
productRegion varchar(100),
productCreatedDt Datetime,
productUpdatedDt  Datetime,
productStatus varchar(100),
isProductsold bit,
productLineNumber Int,
productLineId uniqueIdentifier,
productAddress varchar(100),
productAddress2 varchar(100),
productCity varchar(100),
productState varchar(100),
productCountry varchar(100),
productZip  Varchar(100),
productLatitude varchar(100),
productLongitude varchar(100)
)

Create Table test_productOwner
(
productOwnerId Int Identity(1,1),
productId uniqueIdentifier,
productCreatorId uniqueIdentifier,
Createddate datetime,
modifieddate datetime
)

Create Table test_productCreator 
(
productCreatorId uniqueIdentifier primary key,
productCreatorName varchar(100),
createddate datetime,
modifieddate datetime
)

ALTER TABLE test_productOwner ADD CONSTRAINT PK_test_productOwner PRIMARY KEY NONCLUSTERED(productOwnerId)
create clustered index ix_test_productOwner_productId On dbo.test_productOwner(productId)
create nonclustered index ix_test_productOwner_test_productCreatorId On dbo.test_productOwner(productCreatorId)

/* Insert data */
Declare @i int = 1, @j int = 1
Declare @newIdi uniqueidentifier, @newIdj uniqueidentifier

While @i < 100
Begin
set @newIdi = newid()

Insert Into dbo.test_productCreator values (@newIdi, 'ABC'+convert(varchar(10),@i), getdate(),getdate())

    while @j <= 2300
    Begin
        set @newIdj = newId()
        Insert Into dbo.test_productInfo values (@newIdj, 'ProductName'+convert(varchar(10),@i)+convert(varchar(10),@j), 'Region1',getdate(),getdate(),'completed', 1, @i,newId()
        ,'Address1'+convert(varchar(10),@i)+convert(varchar(10),@j), 'Address2'+convert(varchar(10),@i)+convert(varchar(10),@j), 'los angeles','ca','USA','22231','26.45','33.23')
        
        insert into dbo.test_productOwner values (@newIdj, @newIdi, getdate(), getdate())
        
        set @j = @j + 1
        
    End
set @i = @i + 1
set @j = 1
End

**CURRENT Query:**


set statistics io on
Declare @sort varchar(100) = 'PRODMODIFIED'
Declare @PageNumber int = 1, @PageSize   int = 1000
Declare @prodCreator Table(productCreatorId Uniqueidentifier)

insert into @prodCreator 
select top 10 productCreatorId
from dbo.test_productCreator

Select p_pi.*
from test_productInfo p_pi
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
              when @sort = 'PRODCREATED' then p_pi.productCreatedDt
              when @sort = 'PRODNAME' then p_pi.productName
        end
      OFFSET @PageSize * (@PageNumber - 1) ROWS
      FETCH NEXT @PageSize ROWS ONLY

**MODIFIED Query:**
create nonclustered index idx_non_productInfo on dbo.productInfo(productId) include(productUpdatedDt, productCreatedDt, productName)

set statistics io on
Declare @sort varchar(100) = 'PRODMODIFIED'
Declare @PageNumber int = 1, @PageSize   int = 1000
Declare @prodCreator Table(productCreatorId Uniqueidentifier)
Create Table #tmpProduct (productId uniqueidentifier)

insert into @prodCreator 
select top 10 productCreatorId
from dbo.test_productCreator

Insert Into #tmpProduct
Select p_pi.productId
from test_productInfo p_pi
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
              when @sort = 'PRODCREATED' then p_pi.productCreatedDt
              when @sort = 'PRODNAME' then p_pi.productName
        end
      OFFSET @PageSize * (@PageNumber - 1) ROWS
      FETCH NEXT @PageSize ROWS ONLY


Select p_pi.*
from #tmpProduct tmp_pi
join test_productInfo p_pi
on tmp_pi.productId = p_pi.productId
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
              when @sort = 'PRODCREATED' then p_pi.productCreatedDt
              when @sort = 'PRODNAME' then p_pi.productName
        end

If object_id('tempdb..#tmpProduct') is not null
begin
drop table #tmpProduct
end
sql-server-2012

1 respostas

  1. Best Answer

    A classificação se espalha principalmente porque as variáveis ​​de tabela não oferecem suporte a estatísticas e o padrão é uma estimativa de cardinalidade de uma linha. Você pode contornar o problema de cardinalidade no SQL Server 2012 com uma dica de recompilação ou sinalizador de rastreamento 2453 , mas ainda não obterá estatísticas, que são importantes para qualquer operação que dependa da distribuição de dados (como junções e agrupamento).

    Existem várias maneiras de melhorar sua consulta, incluindo o uso de SQL dinâmico, mas para as tabelas relativamente pequenas envolvidas, eu provavelmente não me incomodaria em escrever tanto código. A solução abaixo copia linhas de sua variável de tabela para uma tabela temporária, que suporta estatísticas:

    DECLARE @ProdCreator table (productCreatorId uniqueidentifier PRIMARY KEY);

INSERT @ProdCreator
    (productCreatorId)
SELECT TOP (10) 
    TPC.productCreatorId
FROM dbo.test_productCreator AS TPC;

CREATE TABLE #ProdCreator 
(
    productCreatorId uniqueidentifier PRIMARY KEY
);

INSERT #ProdCreator 
    (productCreatorId)
SELECT 
    PC.productCreatorId 
FROM @ProdCreator AS PC;

    DECLARE 
    @Sort varchar(100) = 'PRODMODIFIED',
    @PageNumber integer = 1,
    @PageSize integer = 1000;

-- This query now benefits from automatic statistics
-- on the #ProdCreator table
SELECT TPI.*
FROM
(
    SELECT
        TPI.productId, 
        rn = ROW_NUMBER() OVER (
            ORDER BY
                CASE @Sort
                    WHEN 'PRODMODIFIED' 
                        THEN CONVERT(sql_variant, TPI.productUpdatedDt)
                    WHEN 'PRODCREATED' 
                        THEN CONVERT(sql_variant, TPI.productCreatedDt)
                    WHEN 'PRODNAME' 
                        THEN CONVERT(sql_variant, TPI.productName)
                END)
    FROM dbo.test_productInfo AS TPI
    WHERE EXISTS 
    (
        SELECT * 
        FROM #ProdCreator AS PC
        JOIN dbo.test_productOwner AS TPO 
            ON TPO.productCreatorId = PC.productCreatorId
        WHERE 
            TPO.productId = TPI.productId
    )
    ORDER BY rn
        OFFSET (@PageNumber - 1) * @PageSize ROWS
        FETCH FIRST @PageSize ROWS ONLY
) AS ThePage
JOIN dbo.test_productInfo AS TPI
    ON TPI.productId = ThePage.productId
ORDER BY
    ThePage.rn
OPTION (RECOMPILE, MAXDOP 4);

    Exemplo de plano:

    Plano paralelo

    Evita o erro de conversão que impedia que seus originais funcionassem com uma ordem de classificação PRODNAME convertendo para sql_variant . A dica de recompilação permite a otimização de incorporação de parâmetros, que minimiza o impacto da expressão case avaliando-a uma vez antes do início da execução.

    A dica MAXDOP não é necessária, mas é algo que você pode querer considerar se você naturalmente obtiver um plano paralelo e não tiver seu grau máximo de paralelismo definido. O paralelismo tem um benefício extra neste plano, pois permite redução de semijunção antecipada por meio de um bitmap de compilação de hash .

    A junção extra à tabela de informações é buscar apenas todas as colunas para uma única página de linhas. Evita classificar as colunas extras para linhas que não são necessárias. Pode ou não agregar uma quantidade enorme de valor; omita se preferir. O índice proposto na pergunta tornaria a junção inicial um pouco mais eficiente, mas para uma tabela desse tamanho pode não valer a pena. Isso é realmente uma decisão para você tomar.

    Esta consulta tem uma concessão de memória 'excessiva' devido à conversão sql_variant . Se isso for importante para você no aplicativo real, você terá que usar SQL dinâmico. A concessão não é tão grande neste exemplo que eu particularmente me preocuparia com isso.

    Exemplo de SQL dinâmico para comparação (ainda usa a tabela temporária):

    DECLARE 
    @Sort varchar(100) = 'PRODMODIFIED',
    @PageNumber integer = 1,
    @PageSize integer = 1000,
    @SQL nvarchar(max);

SET @SQL = 
    REPLACE
    (
        N'
        SELECT TPI.*
        FROM
        (
            SELECT
                TPI.productId, 
                rn = ROW_NUMBER() OVER (ORDER BY ##OrderBy##)
            FROM dbo.test_productInfo AS TPI
            WHERE EXISTS 
            (
                SELECT * 
                FROM #ProdCreator AS PC
                JOIN dbo.test_productOwner AS TPO 
                    ON TPO.productCreatorId = PC.productCreatorId
                WHERE 
                    TPO.productId = TPI.productId
            )
            ORDER BY rn
                OFFSET (@PageNumber - 1) * @PageSize ROWS
                FETCH FIRST @PageSize ROWS ONLY
        ) AS ThePage
        JOIN dbo.test_productInfo AS TPI
            ON TPI.productId = ThePage.productId
        ORDER BY
            ThePage.rn
        OPTION (MAXDOP 4);
        ',
        N'##OrderBy##',
        CASE @Sort
            WHEN 'PRODMODIFIED' THEN N'TPI.productUpdatedDt'
            WHEN 'PRODCREATED' THEN N'TPI.productCreatedDt'
            WHEN 'PRODNAME' THEN N'TPI.productName'
        END
    );

EXECUTE sys.sp_executesql
    @SQL,
    N'@PageNumber integer, @PageSize integer',
    @PageNumber = @PageNumber,
    @PageSize = @PageSize;

    Planejar para SQL dinâmico

    A versão dinâmica termina em cerca de 80ms na minha máquina. A versão não dinâmica é executada em cerca de 40ms, mas usa mais memória e paralelismo.

    • 4

relate perguntas