Perguntas[index-tuning](dba)

Estou pesquisando uma tabela de relatórios DW que crescerá muito. Para simplificar, vou mostrar a tabela da seguinte forma:

BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL

Cada consulta usará o nome da empresa para consultar em uma partição de dados (não em uma partição física).

Como essa tabela pode conter mais de um bilhão de linhas e cada empresa terá uma distribuição de dados bastante uniforme, as consultas por empresa devem ser o mais rápidas possível. Estou na fase de configurar alguns testes, mas antes de fazê-lo pensei em perguntar e ver se seria uma perda de tempo.

Minha ideia era determinar que, se a partição de dados de cada empresa fosse colocada lado a lado no disco por meio de um índice clusterizado, a recuperação de dados seria mais rápida do que apenas usar um índice não clusterizado para cobrir CompanyName.

Exemplo 1: Aqui está a variação em que a coluna IDENTITY é o PK, mas não CLUSTERED. O CompanayName e TableID se combinam para formar o Índice Clusterizado para que os dados sejam ordenados por empresa no disco.

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY NONCLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE UNIQUE CLUSTERED INDEX [CLUSTERED_ByCompanyName_TableID] ON [dbo].[BigTable]
(
    [CompanyName] ASC,
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

E aqui está a maneira tradicional de criar tabelas com índices de cobertura.

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ByCompanyName] ON [dbo].[BigTable]
(
    [CompanyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Alguém sabe imediatamente se haveria alguma melhoria de desempenho a ser obtida usando o primeiro exemplo sobre o segundo exemplo?

EDIT: Estou inclinado a usar um índice clusterizado com a empresa. O TableID é apenas um campo de autoincremento para usar como PK se uma linha precisar de uma referência exclusiva. Eu sinto que as buscas/varreduras de índice agrupadas são mais rápidas do que as buscas/busca(s) de índice.

Eu gostaria que você pudesse particionar facilmente ou fragmentar com base em algo como companyid.

Uma consulta básica seria na forma de

SELECT
    SUM(FieldA) OVER (PARTITION BY ...) a,
    COUNT(1) OVER (PARTITION BY...) b
    ...       
FROM
    BigTable 
WHERE
    CompanyName = 'NABISCO'
GROUP BY
   ....
ORDER BY
   ....

No meu banco de dados tenho uma tabela com dois índices. Há um índice exclusivo nº 1 nas colunas a,b que preciso por motivos de integridade de dados. Eu tenho outro índice #2 com colunas c,a,b que é usado por motivos de desempenho. Percebi que este índice #2 também é único.

A singularidade do índice #2 me parece redundante, pois você não pode ter valores duplicados no índice #2 sem também ter valores duplicados no índice #1. Estou tentado a alterar o índice #2 para que ele não seja mais exclusivo, porque imagino que o mecanismo de banco de dados possa executar uma segunda verificação em c,a,b no índice #2 para garantir a exclusividade dessas colunas toda vez que uma linha for inserida, causando um impacto no desempenho, mesmo que nunca haja valores duplicados. Isso está correto?

Existe alguma maneira de excluir o índice #1 em a,b e manter o índice #2 em c,a,b, mas ainda forçar uma restrição exclusiva apenas nas colunas a,b sem manter dois índices separados? Isso me permitiria ter apenas um índice com todas as três colunas, mas ainda aplicaria minha restrição de integridade de dados em a,b. Não preciso de um índice em a,b para desempenho porque todas as minhas consultas de seleção incluem a coluna c na cláusula where. Este seria um caso de uso para uma restrição exclusiva em vez de um índice? Eu pensei que o mecanismo de banco de dados basicamente trata essas duas construções da mesma forma (veja este post: Quando devo usar uma restrição exclusiva em vez de um índice exclusivo? ).

Tenha em mente que os índices não são redundantes, mas a "singularidade" dos índices é redundante. Parece que é um acéfalo tornar o índice # 2 não exclusivo. Mas isso resulta em algum ganho real de desempenho? O banco de dados verifica a exclusividade de ambos os índices mesmo que as colunas no índice #1 estejam totalmente incluídas no índice #2?

Algumas respostas perguntaram sobre consultas de exemplo usadas para selecionar dados dessa tabela. Aqui estão os mais comuns:

Select [some other columns] from table where c=1 and a=2
Select [some other columns] from table where c=1
Select [some other columns] from table where c=1 and a=2 and b=3

Essas consultas geralmente incluem a seleção de muitas outras colunas que não estão em nenhum índice.

O que normalmente nunca executamos é uma consulta como esta:

Select [stuff] from table where a=2 and b=3

Meu objetivo é projetar uma tabela, que possa ser consultada através de um id externo( uniqueidentifier), um id interno( bigint), sempre em combinação com companyId(bigint), userId(bigint)e dashboardId(bigint)ou em combinação com ( dashboardId IN @0, ..., @n, n=0,10), ambas condições papel de uma verificação de propriedade.

Eu criei as seguintes composições de índice:

CREATE CLUSTERED INDEX Mytable_createdBy_cix ON Mytable(companyId, createdBy, dashboardId)

CREATE UNIQUE NONCLUSTERED INDEX Mytable_extId_nix ON Mytable(extId) INCLUDE (valueD, valueN)

CREATE UNIQUE NONCLUSTERED INDEX Mytable_chartId_nix ON Mytable (chartId) INCLUDE (valueD, valueN)

Não sei a resposta das seguintes perguntas:

• O índice clusterizado é ruim por não ser exclusivo? Devo adicionar a chave segregada e não usar a atribuição automática uniqueifier?
• Colunas de 3 * 8 bytes + exclusivo de 4 bytes (total de 28 bytes) são demais para um índice clusterizado? Eu li que é incluído nas páginas de inclusão de cada índice não clusterizado exclusivo (onde 16 ou 8 bytes adicionais são adicionados de acordo com a chave usada).
• Esse design de índice faz sentido para as consultas abaixo?

Eu planejo executar consultas, semelhantes a estas:

SELECT chartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId

SELECT chartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId

SELECT chartId, valueD, valueN FROM Mytable WHERE dashboardId IN (@0, @1, @2)

SELECT chartId, valueD, valueN FROM Mytable WHERE (companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId) OR dashboardId IN (@0, @1, @2)

UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND chartId = @chartId

UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND extChartId= @extId

UPDATE Mytable SET valueD = @valueD WHERE ((companyId = @companyId AND createdBy = @userId) OR dashboardId IN (@0, @1, @2)) AND extChartId= @extId

Eu sei , é melhor testar, avaliar planos de execução, compartilhá-los ao fazer perguntas no stackexchange, mas esta é a fase de design, então ainda não existem dados ou tabelas reais.

Posso ajustar a estrutura de chaves/índices/tabela para melhor se adequar às consultas. Só espero acertar pelo menos parcialmente desta primeira vez ao criá-los, para que a questão não seja revisitada.

Muito obrigado por qualquer ajuda com antecedência.

Temos um banco de dados sql server que usamos como data lake e datawarehouse. Cada tabela no banco de dados tem alguma definição padronizada, já que temos cerca de 600 tabelas agora, portanto, a manutenção precisa ser um pouco automatizada.

O processo geral para carregar cada tabela é primeiro carregar uma cópia da tabela em uma tabela hash no esquema changeLog (às vezes apenas com os registros alterados, se pudermos determinar quais são os registros alterados) e, em seguida, comparar a tabela changeLog com o destino tabela. A tabela de destino é usada para relatórios, portanto, essa abordagem changeLog nos permite persistir na tabela de destino e aplicar apenas operações UPDATE/INSERT mínimas.

Cada tabela de destino tem uma Chave Exclusiva/Chave Comercial que é identificável por meio de uma tabela de configuração E possui colunas de auditoria padronizadas com o mesmo nome em todas as tabelas. As colunas de auditoria nos dizem

1. quando o registro foi adicionado ao datawarehouse
2. quando o registro foi atualizado pela última vez no datawarehouse
3. se o registro foi excluído ou não na fonte
4. um identificador de registro alterado usando HASHBYTES('SHA2_256', CONCAT())

O identificador de registro alterado costumava ser CHECKSUM(), mas descobrimos que CHECKSUM() tem uma taxa de colisão muito alta para ser confiável. Acabei de adicionar uma coluna HASHBYTES() a cada tabela e preenchê-la.

Eu criei a coluna HASHBYTES() como VARBINARY(MAX). Agora, toda vez que uma tabela está sendo carregada, podemos dizer se um registro precisa ser atualizado comparando o novo valor de HASHBYTES(), calculado na tabela changeLog, com o valor persistido na tabela de destino.

Percebi imediatamente que a mudança de INT CHECKSUM() para VARBINARY(MAX) HASHBYTES() fez com que o processo de verificação de atualização ficasse significativamente lento. Eu tenho índices NONCLUSTERED em cada coluna CHECKSUM, mas não na coluna HASHBYTES que acabei de adicionar. Há também índices clusterizados para a chave exclusiva de cada tabela.

1. Qual é o índice ideal para adicionar para verificar atualizações?
2. Existe um índice padronizado que posso adicionar a cada tabela?
3. VARBINARY(MAX) é o tipo de dados adequado ou posso reduzi-lo com segurança para um tamanho menor?

Espero que isso seja suficiente para que essa pergunta faça sentido. Preciso acelerar esse processo o mais rápido possível.

EDIT: Estou adicionando um script SQL grande como exemplo, ele tem uma definição de tabela de exemplo para a versão changeLog da tabela e a versão de destino, bem como a consulta que é executada para atualizar a versão de destino.

--OBJECT DEFINITIONS
CREATE TABLE [changeLog].[DimSalesOffice](
    [Sales Office Code] [varchar](3) NOT NULL,
    [CCN_Key] [uniqueidentifier] NOT NULL,
    [Sales Office Name (Short)] [nvarchar](14) NOT NULL,
    [Sales Office Name (Long)] [nvarchar](50) NOT NULL,
    [Sales Office City] [nvarchar](50) NOT NULL,
    [Sales Office StateProvince] [nvarchar](50) NOT NULL,
    [Sales Office Postal Code] [nvarchar](20) NOT NULL,
    [Sales Office Country Code] [varchar](3) NOT NULL,
    [Sales Office Address] [nvarchar](65) NOT NULL,
    [Sales Office Address (Line 2)] [nvarchar](65) NOT NULL,
    [Sales Office Name (Short - Native Language)] [nvarchar](14) NOT NULL,
    [Sales Office Name (Long - Native Language)] [nvarchar](50) NOT NULL,
    [Sales Office Address (Native Language)] [nvarchar](65) NOT NULL,
    [Sales Office Address (Line 2 - Native Language)] [nvarchar](65) NOT NULL,
    [Sales Office City (Native Language)] [nvarchar](100) NOT NULL,
    [Sales Office StateProvince (Native Language)] [nvarchar](100) NOT NULL,
    [Sales Office Region] [nvarchar](100) NULL,
    [Native Language Code] [varchar](2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DimSalesOffice](
    [SalesOffice_Key] [uniqueidentifier] NOT NULL,
    [Sales Office Code] [varchar](3) NOT NULL,
    [CCN_Key] [uniqueidentifier] NOT NULL,
    [Sales Office Name (Short)] [nvarchar](14) NOT NULL,
    [Sales Office Name (Long)] [nvarchar](50) NOT NULL,
    [Sales Office City] [nvarchar](50) NOT NULL,
    [Sales Office StateProvince] [nvarchar](50) NOT NULL,
    [Sales Office Postal Code] [nvarchar](20) NOT NULL,
    [Sales Office Country Code] [varchar](3) NOT NULL,
    [Sales Office Address] [nvarchar](65) NOT NULL,
    [Sales Office Address (Line 2)] [nvarchar](65) NOT NULL,
    [Sales Office Name (Short - Native Language)] [nvarchar](14) NOT NULL,
    [Sales Office Name (Long - Native Language)] [nvarchar](50) NOT NULL,
    [Sales Office Address (Native Language)] [nvarchar](65) NOT NULL,
    [Sales Office Address (Line 2 - Native Language)] [nvarchar](65) NOT NULL,
    [Sales Office City (Native Language)] [nvarchar](100) NOT NULL,
    [Sales Office StateProvince (Native Language)] [nvarchar](100) NOT NULL,
    [Sales Office Region] [nvarchar](100) NULL,
    [Native Language Code] [varchar](2) NOT NULL,
    [DW_CreatedOn] [datetime2](7) NULL,
    [DW_ModifiedOn] [datetime2](7) NULL,
    [DW_IsDeleted?] [bit] NULL,
    [DW_Checksum] [int] NULL,
    [Source_ModifiedOn] [datetime2](7) NULL,
    [DW_Hashbytes] [varbinary](max) NULL,
PRIMARY KEY NONCLUSTERED 
(
    [SalesOffice_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[DimSalesOffice] ADD  DEFAULT (newsequentialid()) FOR [SalesOffice_Key]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_UK_DimSalesOffice] ON [dbo].[DimSalesOffice]
(
    [Sales Office Code] ASC
)
GO

--MERGE QUERY
DECLARE @InsertRecordCount INT, @UpdateRecordCount INT;

/*****UPDATE*****/
UPDATE [dbo].[DimSalesOffice] SET
    [CCN_Key] = [Source].[CCN_Key],
    [Sales Office Name (Short)] = [Source].[Sales Office Name (Short)],
    [Sales Office Name (Long)] = [Source].[Sales Office Name (Long)],
    [Sales Office City] = [Source].[Sales Office City],
    [Sales Office StateProvince] = [Source].[Sales Office StateProvince],
    [Sales Office Postal Code] = [Source].[Sales Office Postal Code],
    [Sales Office Country Code] = [Source].[Sales Office Country Code],
    [Sales Office Address] = [Source].[Sales Office Address],
    [Sales Office Address (Line 2)] = [Source].[Sales Office Address (Line 2)],
    [Sales Office Name (Short - Native Language)] = [Source].[Sales Office Name (Short - Native Language)],
    [Sales Office Name (Long - Native Language)] = [Source].[Sales Office Name (Long - Native Language)],
    [Sales Office Address (Native Language)] = [Source].[Sales Office Address (Native Language)],
    [Sales Office Address (Line 2 - Native Language)] = [Source].[Sales Office Address (Line 2 - Native Language)],
    [Sales Office City (Native Language)] = [Source].[Sales Office City (Native Language)],
    [Sales Office StateProvince (Native Language)] = [Source].[Sales Office StateProvince (Native Language)],
    [Sales Office Region] = [Source].[Sales Office Region],
    [Native Language Code] = [Source].[Native Language Code],
    [DW_Checksum] =
        CHECKSUM(
            [Source].[CCN_Key],
            [Source].[Sales Office Name (Short)],
            [Source].[Sales Office Name (Long)],
            [Source].[Sales Office City],
            [Source].[Sales Office StateProvince],
            [Source].[Sales Office Postal Code],
            [Source].[Sales Office Country Code],
            [Source].[Sales Office Address],
            [Source].[Sales Office Address (Line 2)],
            [Source].[Sales Office Name (Short - Native Language)],
            [Source].[Sales Office Name (Long - Native Language)],
            [Source].[Sales Office Address (Native Language)],
            [Source].[Sales Office Address (Line 2 - Native Language)],
            [Source].[Sales Office City (Native Language)],
            [Source].[Sales Office StateProvince (Native Language)],
            [Source].[Sales Office Region],
            [Source].[Native Language Code],
            0
        ),
    [DW_Hashbytes] = 
        HASHBYTES(
            'SHA2_256',
            ISNULL(CAST([Source].[CCN_Key] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Postal Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Country Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2 - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Region] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Native Language Code] AS NVARCHAR(MAX)), '') + '|'
            + '0'
        ),
    [Source_ModifiedOn] = NULL,
    [DW_ModifiedOn] = GETUTCDATE(),
    [DW_IsDeleted?] = 0
FROM [changeLog].[DimSalesOffice] [Source]
JOIN [dbo].[DimSalesOffice]
    ON [Source].[Sales Office Code] = [DimSalesOffice].[Sales Office Code]
    AND ISNULL([DimSalesOffice].[DW_Hashbytes], HASHBYTES('SHA2_256', '')) <> HASHBYTES(
            'SHA2_256',
            ISNULL(CAST([Source].[CCN_Key] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Postal Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Country Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2 - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Region] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Native Language Code] AS NVARCHAR(MAX)), '') + '|'
            + '0'
        )
SET @UpdateRecordCount = @@ROWCOUNT;

/*****Soft Deletes*****/
UPDATE [dbo].[DimSalesOffice] SET
    [DW_Checksum] = 0,
    [DW_Hashbytes] = 
        HASHBYTES(
            'SHA2_256',
            ISNULL(CAST([DimSalesOffice].[CCN_Key] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Name (Short)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Name (Long)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office City] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office StateProvince] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Postal Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Country Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Address] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Address (Line 2)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Name (Short - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Name (Long - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Address (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Address (Line 2 - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office City (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office StateProvince (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Region] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Native Language Code] AS NVARCHAR(MAX)), '') + '|'
            + '1'
        ),
    [Source_ModifiedOn] = NULL,
    [DW_ModifiedOn] = GETUTCDATE(),
    [DW_IsDeleted?] = 1
FROM [dbo].[DimSalesOffice]
WHERE NOT EXISTS
    (
        SELECT 1
        FROM [changeLog].[DimSalesOffice] [Source]
        WHERE [Source].[Sales Office Code] = [DimSalesOffice].[Sales Office Code]
    )
SET @UpdateRecordCount = @UpdateRecordCount + @@ROWCOUNT;

/*****INSERT*****/
INSERT INTO [dbo].[DimSalesOffice]
    (
        [Sales Office Code],
        [CCN_Key],
        [Sales Office Name (Short)],
        [Sales Office Name (Long)],
        [Sales Office City],
        [Sales Office StateProvince],
        [Sales Office Postal Code],
        [Sales Office Country Code],
        [Sales Office Address],
        [Sales Office Address (Line 2)],
        [Sales Office Name (Short - Native Language)],
        [Sales Office Name (Long - Native Language)],
        [Sales Office Address (Native Language)],
        [Sales Office Address (Line 2 - Native Language)],
        [Sales Office City (Native Language)],
        [Sales Office StateProvince (Native Language)],
        [Sales Office Region],
        [Native Language Code],
        [DW_Checksum],
        [DW_Hashbytes],
        [Source_ModifiedOn],
        [DW_ModifiedOn],
        [DW_IsDeleted?],
        [DW_CreatedOn]
    )
SELECT
    [Source].[Sales Office Code],
    [Source].[CCN_Key],
    [Source].[Sales Office Name (Short)],
    [Source].[Sales Office Name (Long)],
    [Source].[Sales Office City],
    [Source].[Sales Office StateProvince],
    [Source].[Sales Office Postal Code],
    [Source].[Sales Office Country Code],
    [Source].[Sales Office Address],
    [Source].[Sales Office Address (Line 2)],
    [Source].[Sales Office Name (Short - Native Language)],
    [Source].[Sales Office Name (Long - Native Language)],
    [Source].[Sales Office Address (Native Language)],
    [Source].[Sales Office Address (Line 2 - Native Language)],
    [Source].[Sales Office City (Native Language)],
    [Source].[Sales Office StateProvince (Native Language)],
    [Source].[Sales Office Region],
    [Source].[Native Language Code],
    [DW_Checksum] = 
        CHECKSUM(
            [Source].[CCN_Key],
            [Source].[Sales Office Name (Short)],
            [Source].[Sales Office Name (Long)],
            [Source].[Sales Office City],
            [Source].[Sales Office StateProvince],
            [Source].[Sales Office Postal Code],
            [Source].[Sales Office Country Code],
            [Source].[Sales Office Address],
            [Source].[Sales Office Address (Line 2)],
            [Source].[Sales Office Name (Short - Native Language)],
            [Source].[Sales Office Name (Long - Native Language)],
            [Source].[Sales Office Address (Native Language)],
            [Source].[Sales Office Address (Line 2 - Native Language)],
            [Source].[Sales Office City (Native Language)],
            [Source].[Sales Office StateProvince (Native Language)],
            [Source].[Sales Office Region],
            [Source].[Native Language Code],
            0
        ),
    [DW_Hashbytes] = 
        HASHBYTES(
            'SHA2_256',
            ISNULL(CAST([Source].[CCN_Key] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Postal Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Country Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2 - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Region] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Native Language Code] AS NVARCHAR(MAX)), '') + '|'
            + '0'
        ),
    [Source_ModifiedOn] = NULL,
    [DW_ModifiedOn] = GETUTCDATE(),
    [DW_IsDeleted?] = 0,
    [DW_CreatedOn] = GETUTCDATE()
FROM [changeLog].[DimSalesOffice] [Source]
WHERE NOT EXISTS
    (
        SELECT 1
        FROM [dbo].[DimSalesOffice]
        WHERE [Source].[Sales Office Code] = [DimSalesOffice].[Sales Office Code]
    )
SET @InsertRecordCount = @@ROWCOUNT;

SELECT [Update Record Count] = @UpdateRecordCount, [Insert Record Count] = @InsertRecordCount;

Qual é o efeito de remover ou excluir um índice enquanto a tabela está sendo usada?

amostra:

a partir de

tabela: order_tbl índice: order_id_index (não btree)

para

precisa alterá-lo para index:order_id_index(btree)

há um impacto ou perda de dados durante/depois de excluir o índice?

Quero melhorar o desempenho da consulta var dbObj = dbSet.Where(x => x.Name == name).FirstOrDefault();feita na tabela colada abaixo. Eu estava pensando em adicionar um índice em [Name]. Que tipo de índice devo usar se não precisar da capacidade de consultar substrings?

CREATE TABLE [dbo].[TitleTypes] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (MAX) NOT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime),
    CONSTRAINT [PK_TitleTypes] PRIMARY KEY CLUSTERED ([Id] ASC)

)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[TitleTypesHistory]));

Estou analisando a pesquisa de texto completo, que requer a criação de um catálogo. Se eu entendi minha pesquisa corretamente, isso beneficiaria muito alguém que precisa pesquisar substrings no texto, o que não é o caso aqui. Em vez disso, eu esperava algo como um índice de um binário de strings para tornar mais rápido determinar se minha string de pesquisa está contida na coluna.

Se uma coluna AUTO_INCREMENT sequencial não for também a chave primária de uma tabela MySQL, ela precisa ter seu próprio índice UNIQUE para garantir a integridade dos dados a longo prazo?

Estou trabalhando na reindexação de uma tabela MySQL específica para acelerar certas cláusulas WHERE muito comuns. A tabela é do CMS do WordPress . Sua definição fora da distribuição, simplificada, é essa.

CREATE TABLE wp_postmeta (
    meta_id BIGINT UNSIGNED AUTO_INCREMENT,
    post_id BIGINT UNSIGNED,
    meta_key VARCHAR(255),
    meta_value LONGTEXT,
    PRIMARY KEY (meta_id),
    INDEX post_id (post_id),
    INDEX meta_key (meta_key)
);

MariaDB / MySQL (InnoDB) agora usa índices clusterizados, então alterei o PK para este:

    PRIMARY KEY (post_id, meta_key, meta_id)

Isso significa que filtros WHERE como post_id = 42 AND meta_key = 'value'podem ser satisfeitos usando o índice clusterizado e a busca LONGTEXT. Está comprovado que resolve o problema de desempenho dos meus usuários.

A questão é esta: essa tabela reindexada também precisa de um índice exclusivo na coluna de ID de incremento automático para continuar funcionando corretamente a longo prazo? Ou posso omitir esse índice para economizar um pouco de espaço e desempenho de UPDATE/INSERT?

    UNIQUE INDEX meta_id (meta_id),

(Estou ciente de que pode haver designs de tabela muito melhores do que este. Mas minha atribuição atual não me permite alterar a tabela, apenas os índices.)

Estou executando uma consulta em um conjunto de dados que tem 30 milhões de linhas e só continuará a crescer, a tabela é customer_actions (tamanho da tabela é 2416 MB)

create table customer_actions
(
    id          bigint not null
        constraint customer_actions_pkey
            primary key,
    action      text,
    customer_id bigint,
    product_id  bigint,
    item_type   text,
    create_date timestamp
);

Eu tentei uma grande variedade de índices, mas olhando para o exaplin da consulta, nada está sendo atingido

SELECT customer_id, product_id, count(*) AS count
from customer_actions
WHERE action = 'a2b'
      AND item_type = 'wine'
      AND create_date BETWEEN current_timestamp - INTERVAL '2 Years' AND current_timestamp
GROUP BY customer_id, product_id

SELECT customer_id, product_id, count(*) AS count
from customer_actions
WHERE action = 'view'
      AND item_type = 'wine'
      AND create_date BETWEEN current_timestamp - INTERVAL '2 Years' AND current_timestamp
GROUP BY customer_id, product_id

SELECT customer_id, product_id, count(*) AS count
from customer_actions
WHERE action = 'buy'
      AND item_type = 'wine'
      AND create_date BETWEEN current_timestamp - INTERVAL '2 Years' AND current_timestamp
GROUP BY customer_id, product_id

Índices que eu tentei, alguns deles eu sei que não funcionariam, mas eu estava me agarrando a canudos, todos aqueles com uma condição no final também foram tentados sem a condição. Não suponha que alguém possa me apontar na direção certa, sou muito novo em PSQL e ainda não tenho uma compreensão profunda de índices.

CREATE  INDEX IF NOT EXISTS idx_14 on customer_actions (customer_id, product_id, 
create_date, action, item_type) where action = 'a2b'
CREATE  INDEX IF NOT EXISTS idx_15 on customer_actions (customer_id, product_id, action, item_type) where action = 'a2b'
CREATE  INDEX IF NOT EXISTS idx_16 on customer_actions (customer_id, product_id) where action = 'a2b'
CREATE INDEX IF NOT EXISTS idx_11 on customer_actions (item_type, action ) where item_type = 'wine' and action = 'a2b';
CREATE INDEX IF NOT EXISTS idx_12 on customer_actions (item_type, action ) where item_type = 'wine' and action = 'view' ;
CREATE INDEX IF NOT EXISTS idx_13 on customer_actions (item_type, action ) where item_type = 'wine' and action = 'buy' ;
CREATE INDEX idx_time on customer_actions using brin (create_date);
create index idx_actions_a2b on customer_actions (action) where action = 'a2b'
CREATE INDEX IF NOT EXISTS idx_customer_actions_action_product_cardinality_order on customer_actions (customer_id, product_id, action);
CREATE INDEX id_time_and_other on customer_actions (action, item_type, create_date DESC)
CREATE INDEX IF NOT EXISTS idx_customer_actions_product_and_customer on customer_actions (customer_id, product_id)
CREATE  INDEX IF NOT EXISTS idx_14 on customer_actions (customer_id, product_id, create_date, action, item_type)
CREATE  INDEX IF NOT EXISTS idx_14 on customer_actions (customer_id, product_id, create_date, action)
CREATE  INDEX IF NOT EXISTS idx_17 on customer_actions (customer_id, product_id)

A explicação da consulta é

Finalize GroupAggregate  (cost=745877.49..1182094.60 rows=1527687 width=24)
"  Group Key: customer_id, product_id"
  ->  Gather Merge  (cost=745877.49..1143902.43 rows=3055374 width=24)
        Workers Planned: 2
        ->  Partial GroupAggregate  (cost=744877.46..790236.43 rows=1527687 width=24)
"              Group Key: customer_id, product_id"
              ->  Sort  (cost=744877.46..752397.99 rows=3008210 width=16)
"                    Sort Key: customer_id, product_id"
                    ->  Parallel Seq Scan on customer_actions  (cost=0.00..318363.94 rows=3008210 width=16)
                          Filter: ((action = 'a2b'::text) AND (item_type = 'wine'::text) AND (create_date <= CURRENT_TIMESTAMP) AND (create_date >= (CURRENT_TIMESTAMP - '2 years'::interval)))

Importa qual campo eu especifico primeiro no índice?

gostaria

CREATE SET TABLE my_table
(   
        validity_date DATE
    ,   branch_id SMALLINT 
    ,   account_class_id BYTEINT
    ,   catalog_id INTEGER
    ,   No_events FLOAT
)
PRIMARY INDEX (validity_date, branch_id);

ser tratado pelo servidor da mesma forma que

CREATE SET TABLE my_table
(   
        validity_date DATE
    ,   branch_id SMALLINT 
    ,   account_class_id BYTEINT
    ,   catalog_id INTEGER
    ,   No_events FLOAT
)
PRIMARY INDEX (branch_id, validity_date);

Eu tenho uma tabela de fatos grande e uma tabela de dimensões muito menor em um esquema em estrela simples:

--1.
CREATE TABLE dbo.Dim
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(2000)
)
--index
CREATE UNIQUE NONCLUSTERED INDEX uniqueindex1 ON Dim(CustomerName);


--2. 
CREATE TABLE dbo.Fact
(
...
PurchaseDate DATE 
CustomerNameId INT CONSTRAINT fk1 FOREIGN KEY (CustomerNameId) REFERENCES dbo.Dim(Id)
...
)
--index
CREATE CLUSTERED COLUMNSTORE INDEX ccs ON dbo.Fact;

Executando a seguinte consulta simples, que filtra na tabela de fatos e une na dimensão:

SELECT sd.CustomerName,f.*
FROM dbo.Fact f
INNER JOIN dbo.Dim sd ON sd.Id = f.CustomerNameId
WHERE f.PurchaseDate IN (
'20000506',
'20000507',
'20000508',
'20000509',
'20000501',
'20000502',
'20000503'
)

Obtemos o seguinte plano de consulta feio:

Curiosamente, a tabela de dimensões tende a varrer TODAS as suas 500.000 linhas em 4 iterações, mas no final apenas alguns milhares são necessários nesse intervalo de datas da tabela de fatos.

Isso é muito ineficiente com tabelas de dimensões maiores, basicamente todas as linhas verificadas o tempo todo, como os índices da tabela de pesquisa nem estão lá.

O esperado seria que o sql server primeiro limitasse a tabela de fatos no intervalo de datas e, em seguida, usando esse intervalo limitado de CustomerKeyId, ele procurasse o CustomerName da tabela de dimensão pequena usando uma busca de índice.

1. Isso é realmente o quão ineficiente é o esquema em estrela ou há algo que sinto falta aqui?
2. Em outras palavras, como eu poderia forçar o sql server a preparar a tabela CustomerKeyId limitada e pesquisar apenas aquelas? (com CTE de alguma forma?)