Sql-Server

如何在重建超大表的索引時保留較小的事務日誌文件

  • April 25, 2019

我需要我能獲得的所有幫助才能成功重建索引。特別是關於事務日誌管理的專家建議。

背景

目標數據庫是在聚集列儲存索引 (CCIX) 中託管 3 個大表的 DW 數據庫。最大的表叫Analog。它擁有約 370 億行和約 600 GB 的高壓縮數據。根據我們使用較小表的初步估計，150 GB 的 CCIX 表可以擴展到 5 TB，因此我們為這次重建提供了額外的 29 TB。

  • SQL Server 企業版 2016
  • 恢復模式 = 簡單

為什麼我們必須這樣做？

在我們的 ETL 服務的第一個版本中，數據在載入到 CCIX 之前不會先合併和排序。這導致 CCIX 的非最佳使用，因為許多行段在壓縮之前沒有完全填充。最佳段必須壓縮 1,0485,76 行，並且必須按時間排序，這樣基於時間的查詢才能在 sql server 中獲得最多的行組消除，並且要處理的段更少。我們正在學習更多的 CCIX，因為有更多的文件可用。

我的對齊摘錄在這裡 https://drive.google.com/file/d/0BzGLNskaj70UQUtZYW9CZF9iUUk/view?usp=sharing

新的 ETL 保證數據在載入到 CCIX 之前及時合併和排序。所以未來的載入將被排序，我們在這里關注在首次載入期間載入的現有數據。

有關完整的案例描述，請閱讀此處。 https://drive.google.com/open?id=0BzGLNskaj70URmZURlVDWVNYd2M

這是我們的第二次嘗試，儘管新腳本具有基於分區的索引重建，但我可以看到日誌文件仍在堆積。我們需要控制這一點。

我的問題是：

  1. 我已經執行了第二次嘗試（正在進行中），是否可以檢查哪些分區已經處理和排序？我在重建腳本中有 OFFLINE = ON 。
  2. 當基於分區的索引重建正在進行時，我們如何管理事務日誌的大小？如果可能，我們需要檢查並定期截斷每個分區。
  3. 由於日誌文件空間不足，索引重建最初失敗。我們添加了一個新的日誌文件並使用了一個單獨的磁碟。但是我們如何確保在第二次執行中不會再次發生這種情況？基於分區的重建是否足以保證我們可以成功重建？

感謝你的幫助。

按分區重建腳本（第二次嘗試） - 進行中

用時：17 小時 34 分鐘

--create a new clustered row index (CRI)
CREATE CLUSTERED INDEX [Analog_ColumnStoreIndex] 
ON [dbo].[Analog]
(
   [LogTime] ASC,
   [CTDIID] ASC,
   [WindFarmId] ASC,
   [StationId] ASC
) 
WITH (
   DROP_EXISTING = ON,
   PAD_INDEX = OFF, 
   STATISTICS_NORECOMPUTE = OFF, 
   SORT_IN_TEMPDB = OFF,
   ALLOW_ROW_LOCKS = ON, 
   ALLOW_PAGE_LOCKS = ON,
   ONLINE = OFF
) 
ON [AnalogMonthlyPScheme]([LogTime])
GO

在此處輸入圖像描述

我會一次處理這個分區。在高水平：

  1. 使用分區聚集行儲存索引創建一個新的空目標表
  2. 使用未分區的聚集列儲存索引創建工作表
  3. SWITCH從源表到工作表的分區 1
  4. 使用將工作表轉換為行儲存聚集索引DROP_EXISTING
  5. 向工作表添加一個CHECK約束，該約束涵蓋分區 1 中的值範圍
  6. SWITCH工作表進入目標表的分區 1
  7. 放下空工作台
  8. 從第 2 步開始重複下一個分區
  9. 處理完所有分區後，刪除原始表並重命名新表

這最大限度地利用了最少記錄的操作，最小化了工作空間，並允許在必要時在分區之間清除事務日誌。

展示

分區

CREATE PARTITION FUNCTION [AnalogMonthlyP] (datetime2(0))
AS RANGE RIGHT FOR VALUES(
N'2014-09-01T00:00:00.000', N'2014-10-01T00:00:00.000', N'2014-11-01T00:00:00.000',
N'2014-12-01T00:00:00.000', N'2015-01-01T00:00:00.000', N'2015-02-01T00:00:00.000',
N'2015-03-01T00:00:00.000', N'2015-04-01T00:00:00.000', N'2015-05-01T00:00:00.000',
N'2015-06-01T00:00:00.000', N'2015-07-01T00:00:00.000', N'2015-08-01T00:00:00.000',
N'2015-09-01T00:00:00.000', N'2015-10-01T00:00:00.000', N'2015-11-01T00:00:00.000',
N'2015-12-01T00:00:00.000', N'2016-01-01T00:00:00.000', N'2016-02-01T00:00:00.000',
N'2016-03-01T00:00:00.000', N'2016-04-01T00:00:00.000', N'2016-05-01T00:00:00.000',
N'2016-06-01T00:00:00.000', N'2016-07-01T00:00:00.000', N'2016-08-01T00:00:00.000',
N'2016-09-01T00:00:00.000', N'2016-10-01T00:00:00.000', N'2016-11-01T00:00:00.000',
N'2016-12-01T00:00:00.000', N'2017-01-01T00:00:00.000', N'2017-02-01T00:00:00.000',
N'2017-03-01T00:00:00.000', N'2017-04-01T00:00:00.000', N'2017-05-01T00:00:00.000',
N'2017-06-01T00:00:00.000', N'2017-07-01T00:00:00.000', N'2017-08-01T00:00:00.000',
N'2017-09-01T00:00:00.000', N'2017-10-01T00:00:00.000', N'2017-11-01T00:00:00.000',
N'2017-12-01T00:00:00.000', N'2018-01-01T00:00:00.000', N'2018-02-01T00:00:00.000',
N'2018-03-01T00:00:00.000', N'2018-04-01T00:00:00.000', N'2018-05-01T00:00:00.000',
N'2018-06-01T00:00:00.000', N'2018-07-01T00:00:00.000', N'2018-08-01T00:00:00.000',
N'2018-09-01T00:00:00.000', N'2018-10-01T00:00:00.000', N'2018-11-01T00:00:00.000',
N'2018-12-01T00:00:00.000');

CREATE PARTITION SCHEME [AnalogMonthlyPScheme]
AS PARTITION [AnalogMonthlyP]
ALL TO ([PRIMARY]);

數字表設置（如果需要）

WITH Ten(N) AS 
(
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)   
SELECT TOP (10000000) 
   n = IDENTITY(int, 1, 1)
INTO   dbo.Numbers
FROM   Ten T10,
      Ten T100,
      Ten T1000,
      Ten T10000,
      Ten T100000,
      Ten T1000000,
      Ten T10000000;

ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_dbo_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);

源表和數據

-- Source table currently partitioned clustered columnstore
CREATE TABLE [dbo].[Analog]
(
   LogTime datetime2(0) NOT NULL,
   CTDID integer NOT NULL,
   WindFarmId integer NOT NULL,
   StationId integer NOT NULL,

   INDEX [Analog_ColumnStoreIndex]
       CLUSTERED COLUMNSTORE
)
ON [AnalogMonthlyPScheme](LogTime);

-- Some test data
INSERT dbo.Analog WITH (TABLOCKX)
   (LogTime, CTDID, WindFarmId, StationId)
SELECT
   DATEADD(SECOND, N.n, '20140801'),
   CHECKSUM(NEWID()),
   CHECKSUM(NEWID()),
   CHECKSUM(NEWID())
FROM dbo.Numbers AS N -- ten million rows
OPTION (MAXDOP 1);

目標台和工作台

-- The final table we want - partitioned rowstore clustered index
CREATE TABLE [dbo].[AnalogNew]
(
   LogTime datetime2(0) NOT NULL,
   CTDID integer NOT NULL,
   WindFarmId integer NOT NULL,
   StationId integer NOT NULL,

   INDEX CCIX_Analog
       CLUSTERED (LogTime, CTDID, WindFarmId, StationId)
)
ON [AnalogMonthlyPScheme](LogTime);

-- Working table
CREATE TABLE dbo.AnalogSwitch
(
   LogTime datetime2(0) NOT NULL,
   CTDID integer NOT NULL,
   WindFarmId integer NOT NULL,
   StationId integer NOT NULL,

   INDEX CCI CLUSTERED COLUMNSTORE
);

分區 1

-- Process the first partition
ALTER TABLE dbo.Analog 
SWITCH PARTITION 1 
TO dbo.AnalogSwitch;

-- Optional, may help parallel plan
CREATE STATISTICS s ON dbo.AnalogSwitch (LogTime);

-- Convert to rowstore clustered index
CREATE CLUSTERED INDEX CCI
ON dbo.AnalogSwitch (LogTime, CTDID, WindFarmId, StationId)
WITH (DROP_EXISTING = ON);

-- Specify data range
ALTER TABLE dbo.AnalogSwitch
   ADD CONSTRAINT CK 
   CHECK (LogTime >= '20140801' AND LogTime < '20140901');

-- Move to target table
ALTER TABLE dbo.AnalogSwitch 
SWITCH TO dbo.AnalogNew 
PARTITION 1;

-- Recreate switch table
DROP TABLE dbo.AnalogSwitch;

CREATE TABLE dbo.AnalogSwitch
(
   LogTime datetime2(0) NOT NULL,
   CTDID integer NOT NULL,
   WindFarmId integer NOT NULL,
   StationId integer NOT NULL,

   INDEX CCI CLUSTERED COLUMNSTORE
);

分區 2

-- Process the second partition
ALTER TABLE dbo.Analog 
SWITCH PARTITION 2
TO dbo.AnalogSwitch;

-- Optional, may help parallel plan
CREATE STATISTICS s ON dbo.AnalogSwitch (LogTime);

-- Convert to rowstore clustered index
CREATE CLUSTERED INDEX CCI
ON dbo.AnalogSwitch (LogTime, CTDID, WindFarmId, StationId)
WITH (DROP_EXISTING = ON);

-- Specify data range
ALTER TABLE dbo.AnalogSwitch
   ADD CONSTRAINT CK 
   CHECK (LogTime >= '20140901' AND LogTime < '20141001');

-- Move to target table
ALTER TABLE dbo.AnalogSwitch 
SWITCH TO dbo.AnalogNew 
PARTITION 2;

-- Recreate switch table
DROP TABLE dbo.AnalogSwitch;

CREATE TABLE dbo.AnalogSwitch
(
   LogTime datetime2(0) NOT NULL,
   CTDID integer NOT NULL,
   WindFarmId integer NOT NULL,
   StationId integer NOT NULL,

   INDEX CCI CLUSTERED COLUMNSTORE
);

…等等。這是相當簡單的腳本。

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

相關問答

Sql-Server

Columnstore：糟糕的執行計劃 - 過濾而不是搜尋

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

SQL Server log_reuse_wait_desc 是 ACTIVE_TRANSACTION 但沒有活動事務

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

是否可以將數據批量插入到具有使用 Always Encrypted 加密的列的表中？

  • July 16, 2021
檢視問答
Sql-Server

重建事務日誌

  • May 13, 2021
檢視問答
Sql-Server

如何從表格中檢索大文本？

  • May 6, 2021
檢視問答
Sql-Server

在數據庫備份之前和之後縮小事務日誌文件的原因可能是什麼？

  • February 15, 2021
檢視問答