Início / dba / Perguntas / 171961
Accepted
Wes
Asked: 2017-04-26 08:43:19 +0800 CST

Recriando o índice clusterizado com eficiência

  • 772

Em um banco de dados de teste, estou procurando:

  • Elimine o índice clusterizado (são restrições clusterizadas de chave primária em uma linha que é super inútil para nós.)
  • Criar novo índice clusterizado
  • recriar restrição de chave primária como índice não clusterizado
  • reconstruir todos os outros índices não clusterizados.

Meu fluxo de trabalho é o mesmo acima, com a adição de desabilitar todos os índices não clusterizados antes de descartar o cluster.

Como a eliminação do índice de restrição clusterizado exige que a tabela seja salva como um HEAP, a quantidade de tempo que esse processo leva em nossa tabela de 45 milhões de linhas é enorme. A queda na restrição está acontecendo há 1:17:00 e parece estar apenas em cerca de 31m (com base em Leituras Lógicas em Destaque para a Sessão).

Existe uma maneira mais eficiente de lidar com esse fluxo de trabalho? Talvez uma maneira de descartar o índice de restrição e reconstruir como o novo índice clusterizado, em vez de um HEAP?

Obrigado, Wes

Declarações DDL:

ESTRUTURA DA TABELA

    CREATE TABLE [dbo].[hist](
    [prrowid] [varchar](36) NOT NULL,
    [part] [varchar](30) NULL,
    [date] [datetime] NULL,
    [per_date] [datetime] NULL,
    [type] [varchar](80) NULL,
    [loc] [varchar](80) NULL,
    [loc_begin] [decimal](28, 10) NULL,
    [begin_qoh] [decimal](28, 10) NULL,
    [qty_req] [decimal](28, 10) NULL,
    [qty_chg] [decimal](28, 10) NULL,
    [qty_short] [decimal](28, 10) NULL,
    [um] [varchar](30) NULL,
    [last_date] [datetime] NULL,
    [nbr] [varchar](30) NULL,
    [so_job] [varchar](80) NULL,
    [ship_type] [varchar](30) NULL,
    [addr] [varchar](80) NULL,
    [rmks] [varchar](80) NULL,
    [xdr_acct] [varchar](80) NULL,
    [xcr_acct] [varchar](80) NULL,
    [mtl_std] [decimal](28, 10) NULL,
    [lbr_std] [decimal](28, 10) NULL,
    [bdn_std] [decimal](28, 10) NULL,
    [price] [decimal](28, 10) NULL,
    [trnbr] [int] NULL,
    [gl_amt] [decimal](28, 10) NULL,
    [xdr_cc] [varchar](30) NULL,
    [xcr_cc] [varchar](30) NULL,
    [lot] [varchar](80) NULL,
    [sub_std] [decimal](28, 10) NULL,
    [gl_date] [datetime] NULL,
    [qty_loc] [decimal](28, 10) NULL,
    [userid] [varchar](80) NULL,
    [serial] [varchar](50) NULL,
    [effdate] [datetime] NULL,
    [prod_line] [varchar](30) NULL,
    [xslspsn1] [varchar](80) NULL,
    [xslspsn2] [varchar](80) NULL,
    [xcr_proj] [varchar](80) NULL,
    [xdr_proj] [varchar](80) NULL,
    [line] [int] NULL,
    [user1] [varchar](80) NULL,
    [user2] [varchar](80) NULL,
    [curr] [varchar](30) NULL,
    [ex_rate] [decimal](28, 10) NULL,
    [rev] [varchar](30) NULL,
    [time] [int] NULL,
    [ovh_std] [decimal](28, 10) NULL,
    [site] [varchar](80) NULL,
    [status] [varchar](80) NULL,
    [grade] [varchar](30) NULL,
    [expire] [datetime] NULL,
    [assay] [decimal](28, 10) NULL,
    [xgl_ref] [varchar](30) NULL,
    [_chr01] [varchar](80) NULL,
    [_chr02] [varchar](80) NULL,
    [_chr03] [varchar](80) NULL,
    [_chr04] [varchar](80) NULL,
    [_chr05] [varchar](80) NULL,
    [_chr06] [varchar](80) NULL,
    [_chr07] [varchar](80) NULL,
    [_chr08] [varchar](80) NULL,
    [_chr09] [varchar](80) NULL,
    [_chr10] [varchar](80) NULL,
    [_chr11] [varchar](80) NULL,
    [_chr12] [varchar](80) NULL,
    [_chr13] [varchar](80) NULL,
    [_chr14] [varchar](80) NULL,
    [_chr15] [varchar](80) NULL,
    [_dte01] [datetime] NULL,
    [_dte02] [datetime] NULL,
    [_dte03] [datetime] NULL,
    [_dte04] [datetime] NULL,
    [_dte05] [datetime] NULL,
    [_dec01] [decimal](28, 10) NULL,
    [_dec02] [decimal](28, 10) NULL,
    [_dec03] [decimal](28, 10) NULL,
    [_dec04] [decimal](28, 10) NULL,
    [_dec05] [decimal](28, 10) NULL,
    [_log01] [bit] NULL,
    [_log02] [bit] NULL,
    [ref] [varchar](80) NULL,
    [msg] [int] NULL,
    [program] [varchar](30) NULL,
    [ord_rev] [int] NULL,
    [ref_site] [varchar](80) NULL,
    [rsn_code] [varchar](80) NULL,
    [vend_lot] [varchar](30) NULL,
    [vend_date] [datetime] NULL,
    [daycode] [varchar](80) NULL,
    [for] [varchar](30) NULL,
    [slspsn##1] [varchar](82) NULL,
    [slspsn##2] [varchar](82) NULL,
    [slspsn##3] [varchar](82) NULL,
    [slspsn##4] [varchar](82) NULL,
    [fsm_type] [varchar](80) NULL,
    [upd_isb] [bit] NULL,
    [auto_install] [bit] NULL,
    [ca_int_type] [varchar](80) NULL,
    [covered_amt] [decimal](28, 10) NULL,
    [fcg_code] [varchar](80) NULL,
    [batch] [varchar](30) NULL,
    [fsc_code] [varchar](80) NULL,
    [sa_nbr] [varchar](80) NULL,
    [sv_code] [varchar](80) NULL,
    [eng_area] [varchar](30) NULL,
    [sys_prod] [varchar](30) NULL,
    [svc_type] [varchar](30) NULL,
    [ca_opn_date] [datetime] NULL,
    [cprice] [decimal](28, 10) NULL,
    [eng_code] [varchar](80) NULL,
    [wod_op] [int] NULL,
    [enduser] [varchar](80) NULL,
    [ship_inv_mov] [varchar](80) NULL,
    [ship_date] [datetime] NULL,
    [ship_id] [varchar](30) NULL,
    [ex_rate2] [decimal](28, 10) NULL,
    [ex_ratetype] [varchar](80) NULL,
    [exru_seq] [int] NULL,
    [promise_date] [datetime] NULL,
    [fldchg_cmtindx] [int] NULL,
    [SrcPDB] [varchar](12) NULL,
     CONSTRAINT [hist_PK] PRIMARY KEY CLUSTERED 
    (
        [prrowid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

ÍNDICES ATUAIS

ALTER TABLE [dbo].[hist] ADD CONSTRAINT [hist_PK] PRIMARY KEY CLUSTERED ( [prrowid] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##addr_eff] ON [dbo].[hist] ( [addr], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##batch] ON [dbo].[hist] ( [batch] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##date_trn] ON [dbo].[hist] ( [date], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##eff_trnbr] ON [dbo].[hist] ( [effdate], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##nbr_eff] ON [dbo].[hist] ( [nbr], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##part_eff] ON [dbo].[hist] ( [part], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##part_trn] ON [dbo].[hist] ( [part], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##ref_filter] ON [dbo].[hist] ( [ref] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##serial] ON [dbo].[hist] ( [serial] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##trnbr] ON [dbo].[hist] ( [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##type] ON [dbo].[hist] ( [type], [effdate] ) WITH (FILLFACTOR=100);

ÍNDICES DESEJADOS

CREATE UNIQUE CLUSTERED INDEX [hist##date_trn_CX] ON [dbo].[hist] ( [date], [trnbr] ) WITH (FILLFACTOR=100);
ALTER TABLE [dbo].[hist] ADD CONSTRAINT [hist_PK] PRIMARY KEY NONCLUSTERED ( [prrowid] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##addr_eff] ON [dbo].[hist] ( [addr], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##batch] ON [dbo].[hist] ( [batch] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##eff_trnbr] ON [dbo].[hist] ( [effdate], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##part_eff] ON [dbo].[hist] ( [part], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##part_trn] ON [dbo].[hist] ( [part], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##ref_filter] ON [dbo].[hist] ( [ref] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##serial] ON [dbo].[hist] ( [serial] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##trnbr] ON [dbo].[hist] ( [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##nbr_eff] ON [dbo].[hist] ( [trnbr], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##trnbr_char] ON [dbo].[hist] ( [trnbr_char] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##type] ON [dbo].[hist] ( [type], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##vend_lot] ON [dbo].[hist] ( [vend_lot] ) WITH (FILLFACTOR=100);

-- NOTA -- A resposta abaixo funcionou perfeitamente para mim. Eu tive que ter um volume adicionado. Criei um segundo grupo de arquivos e um arquivo de dados na nova unidade. Além disso, outro arquivo de log também.

sql-server index

1 respostas

  1. Best Answer

    O ideal seria fazer algo assim:

    1. Elimine a restrição de chave primária existente, mas mantenha o índice clusterizado.
    2. Recrie o índice clusterizado nas novas colunas com a DROP_EXISTING = ONopção definida.
    3. Crie a restrição de chave primária em um novo índice não clusterizado.

    Isso pularia a etapa da tabela sendo convertida em um heap. Infelizmente, a etapa 1 não parece ser possível no SQL Server.

    Quando a chave primária é excluída, o índice correspondente é excluído.

    Além disso, BOL tem a dizer sobre alterar uma chave primária com DROP_EXISTING = ON:

    Se o índice impor uma restrição PRIMARY KEY ou UNIQUE e a definição do índice não for alterada de forma alguma, o índice será descartado e recriado preservando a restrição existente. No entanto, se a definição do índice for alterada, a instrução falhará. Para alterar a definição de uma restrição PRIMARY KEY ou UNIQUE, elimine a restrição e adicione uma restrição com a nova definição.

    Até onde eu sei, o melhor que você pode fazer é evitar a conversão de heap criando uma cópia da tabela e movendo todos os dados para lá. A remoção ou adição de um índice clusterizado cria uma cópia interna dos dados de qualquer maneira, portanto, não exigirá mais espaço. Aqui estão algumas dicas para acelerar isso:

    • Você provavelmente não deveria usar SELECT INTO. Isso copiará os dados para um heap, que é a etapa que você está tentando evitar. No entanto, tanto a SELECT INTOcriação do índice clusterizado são elegíveis para paralelismo no SQL Server 2014.
    • Aproveite o registro mínimo se o seu modelo de recuperação permitir. Observe que para INSERT INTO... SELECTvocê precisará de uma TABLOCKdica na tabela de destino para obter o mínimo de log.
    • Crie seus índices não clusterizados depois que todos os dados forem carregados.
    • Ao criar os índices não clusterizados, use a SORT_IN_TEMPDB = ONopção se tempdb for dimensionado para isso.
    • Verifique se há chaves estrangeiras em outras tabelas. Se você conseguir desabilitar aqueles que podem ajudar a acelerar as coisas.

    Como um aparte, se você estava curioso para ver a etapa 2 em ação (eu estava), aqui está um código de exemplo que mostra como a etapa de conversão de heap pode ser ignorada:

    DROP TABLE IF EXISTS dbo.X_NUMBERS_1000000;
CREATE TABLE dbo.X_NUMBERS_1000000 (ID INT NOT NULL, ID2 INT NOT NULL, FILLER VARCHAR(500));

CREATE CLUSTERED INDEX CI_X_NUMBERS_1000000 ON dbo.X_NUMBERS_1000000 (ID);

INSERT INTO dbo.X_NUMBERS_1000000 WITH (TABLOCK)
SELECT TOP (1000000) 
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 500)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;


-- option 1
DROP INDEX X_NUMBERS_1000000.CI_X_NUMBERS_1000000;
CREATE CLUSTERED INDEX CI_X_NUMBERS_1000000_2_COL ON dbo.X_NUMBERS_1000000 (ID, ID2);

    Tempos de execução do SQL Server: tempo de CPU = 31 ms, tempo decorrido = 51 ms.

    Tempos de execução do SQL Server: tempo de CPU = 2406 ms, tempo decorrido = 3484 ms.

    -- option 2 (after resetting the table)
CREATE CLUSTERED INDEX CI_X_NUMBERS_1000000 ON dbo.X_NUMBERS_1000000 (ID, ID2) 
WITH (DROP_EXISTING = ON);

    Tempos de execução do SQL Server: tempo de CPU = 2.422 ms, tempo decorrido = 3.411 ms.

    • 4

relate perguntas