Sql-Server

有效地重新創建聚集索引

  • April 28, 2017

在測試數據庫中，我希望：

  • 刪除聚集索引（它們是對我們來說超級無用的行上的主鍵聚集約束。）
  • 創建新的聚集索引
  • 將主鍵約束重新創建為非聚集索引
  • 重建所有其他非聚集索引。

我的工作流程也和上面一樣，除了在刪除聚集索引之前禁用所有非聚集索引。

由於刪除聚集約束索引需要將表保存為 HEAP，因此此過程在我們的 45m 行表上花費的時間是巨大的。約束的下降一直持續到 1:17:00，似乎只有大約 31m（基於 Spotlight for the Session 中的邏輯讀取）。

有沒有更有效的方法來處理這個工作流程？也許是一種刪除約束索引並重建為新的聚集索引而不是 HEAP 的方法？

謝謝，韋斯

DDL 語句：

表結構

   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]

目前指數

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);

所需的索引

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);

– 注意 – 下面的答案對我來說非常有效。我確實必須添加一個音量。我在新驅動器上創建了第二個文件組和一個數據文件。此外，還有另一個日誌文件。

理想情況下，你會做這樣的事情：

  1. 刪除現有的主鍵約束，但保留聚集索引。
  2. DROP_EXISTING = ON使用選項集在新列上重新創建聚集索引。
  3. 在新的非聚集索引上創建主鍵約束。

這將跳過將表轉換為堆的步驟。不幸的是，步驟 1在 SQL Server 中似乎是不可能的。

當主鍵被刪除時，相應的索引也被刪除。

此外，BOL對使用以下內容更改主鍵有這樣的說法DROP_EXISTING = ON：

如果索引強制執行 PRIMARY KEY 或 UNIQUE 約束並且索引定義未以任何方式更改，則刪除並重新創建索引以保留現有約束。但是，如果更改了索引定義，則該語句將失敗。要更改 PRIMARY KEY 或 UNIQUE 約束的定義，請刪除約束並使用新定義添加約束。

據我所知，您能做的最好的事情就是通過創建表的副本並將所有數據移到那裡來避免堆轉換。無論如何，刪除或添加聚集索引都會創建數據的內部副本，因此它不需要更多空間。以下是一些加快速度的提示：

  • 您可能不應該使用SELECT INTO. 這會將數據複製到堆中，這是您要避免的步驟。但是，SELECT INTO聚集索引的創建和創建都符合 SQL Server 2014 中的並行性。
  • 如果您的恢復模式允許，請利用最少的日誌記錄。請注意，INSERT INTO... SELECT您需要TABLOCK針對目標表的提示才能獲得最少的日誌記錄。
  • 在載入所有數據後創建非聚集索引。
  • 在創建非聚集索引時，SORT_IN_TEMPDB = ON如果為它調整了 tempdb 的大小，請使用該選項。
  • 檢查其他表上的外鍵。如果您能夠禁用那些可能有助於加快速度的功能。

順便說一句，如果您想看看第 2 步的實際效果（我是），這裡有一些範常式式碼，展示瞭如何跳過堆轉換步驟：

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);

SQL Server 執行時間：CPU 時間 = 31 毫秒，經過時間 = 51 毫秒。

SQL Server 執行時間：CPU 時間 = 2406 毫秒，經過時間 = 3484 毫秒。

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

SQL Server 執行時間：CPU 時間 = 2422 毫秒，經過時間 = 3411 毫秒。

引用自：https://dba.stackexchange.com/questions/171961

相關問答

Sql-Server

聚集索引和排序

  • October 3, 2021
檢視問答
Sql-Server

ETL 中的聚集索引（刪除 - 插入 - 創建？）

  • December 1, 2020
檢視問答
Sql-Server

聚集和列儲存索引

  • September 14, 2020
檢視問答
Sql-Server

有什麼方法可以測試哪個聚集索引更適合特定查詢？

  • November 27, 2018
檢視問答
Sql-Server

檢查索引重建操作是否線上的 T-SQL 腳本已關閉

  • April 6, 2018
檢視問答
Sql-Server

如何擺脫這些關鍵查找？

  • May 24, 2017
檢視問答