موضوعی نهفته در Data Partitioning – بخش اول

مقدمه

پارتیشن نمودن جداول مزایای خاصی را به دنبال دارد مانند Partition Switch. همان طور که می دانیم Rebuild کلاستر ایندکس یک جدول بزرگ عملیات بسیار سنگینی می باشد، اما با استفاده از امکان Data Partitioning می توانیم فقط یک یا چند پارتیشن خاص را در مدت زمان کوتاهی Rebuild نمائیم. اگر Partition Key در شرط کوئری یا شرط Join مورد استفاده قرار گیرد، سرعت اجرای کوئری می تواند افزایش یابد. با استفاده از Data Partitioning بحث فشرده سازی را به ازای پارتیشن های جاری می توان انجام نداد و فقط پارتیشن های قدیمی را فشرده نمود. Data Partitioning مزایای دیگری نیز دارد.
با همه این مزایا، پارتیشن نمودن جداول معایبی نیز دارد که در این مقاله قصد داریم به بررسی مهمترین این موارد بپردازیم.

برای نشان دادن این موضوع جدول زیر را ایجاد و با داده های فرضی پر می نمائیم:

Use Test
GO
CREATE TABLE dbo.DATA
(
  ID INT NOT NULL,
  DATECREATED DATETIME NOT NULL
  CONSTRAINT DEF_DATA_DATECREATED DEFAULT GETUTCDATE(),
  DATEMODIFIED DATETIME NOT NULL
  CONSTRAINT DEF_DATA_DATEMODIFIED DEFAULT GETUTCDATE(),
  PLACEHOLDER CHAR(500) NULL
);
DECLARE @STARTDATE DATETIME = '2016-01-01';
;WITH N1(C) AS (SELECT 0 UNION ALL SELECT 0) -- 2 ROWS
,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2) -- 4 ROWS
,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2) -- 16 ROWS
,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2) -- 256 ROWS
,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2) -- 65,536 ROWS
,N6(C) AS (SELECT 0 FROM N5 AS T1 CROSS JOIN N2 AS T2 CROSS JOIN N1 AS T3) -- 524,288
,IDS(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N6)
INSERT INTO dbo.DATA WITH (TABLOCK)(ID, DATECREATED, DATEMODIFIED)
SELECT ID, DATEADD(SECOND,35 * ID,@STARTDATE),
CASE
WHEN ID % 10 = 0
THEN DATEADD(SECOND, 24 * 60 * 60 * (ID % 31) + 11200 + ID % 59 + 35 * ID,
@STARTDATE)
ELSE DATEADD(SECOND,35 * ID,@STARTDATE)
END
FROM IDS;
GO

 دستور زیر یک Unique Clustered Index و همچنین یک NonClustered Index غیر یکتا را بر روی جدول Data ایجاد می نماید:

CREATE UNIQUE CLUSTERED INDEX IDXDATAID
ON dbo.DATA(DATECREATED, ID)
WITH (DATA_COMPRESSION = PAGE);
CREATE NONCLUSTERED INDEX IDXDATADATEMODIFIED
ON dbo.DATA(DATEMODIFIED)
WITH (DATA_COMPRESSION = PAGE);

اکنون پروسیجر زیر را ایجاد و اجرا می نمائیم :

CREATE PROCEDURE USP_TEST
(@TOP TINYINT, @LASTDATEMODIFIED DATETIME)
AS
SELECT TOP (@TOP) ID, DATECREATED, DATEMODIFIED, PLACEHOLDER
FROM dbo.DATA
WHERE DATEMODIFIED > @LASTDATEMODIFIED
ORDER BY DATEMODIFIED
GO
EXEC USP_TEST 100, '2016-08-25 00:00:00.000'

تصویر زیر Actual Execution Plan مربوط به اجرای پروسیجر را نمایش می دهد:همان طور که در تصویر مشاهده می نمائید SQL Server از NonClustered Index ایجاد شده بر روی جدول به خوبی استفاده نموده است.
می خواهیم جدول را پارتیشن نموده و مجدد پروسیجر را اجرا نمائیم.
دستورات زیر یک FileGroup به دیتابیس تست اضافه نموده و سپس Partition Function و Partition Scheme را ایجاد می نمایند:

ALTER DATABASE TEST ADD FILEGROUP FG1
ALTER DATABASE TEST ADD FILE
(NAME = F1, FILENAME = N'D:\DATABASES\DATA\TESTF1.NDF', SIZE = 1024 MB, FILEGROWTH = 256 MB) TO FILEGROUP FG1
CREATE PARTITION FUNCTION PFDATA(DATETIME)
AS RANGE RIGHT FOR VALUES
('۲۰۱۶-۰۲-۰۱', '۲۰۱۶-۰۳-۰۱','۲۰۱۶-۰۴-۰۱','۲۰۱۶-۰۵-۰۱','۲۰۱۶-۰۶-۰۱','۲۰۱۶-۰۷-۰۱','۲۰۱۶-۰۸-۰۱');
CREATE PARTITION SCHEME PSDATA AS PARTITION PFDATA ALL TO ([FG1]);

 دستورات زیر نیز جدول را پارتیشن و NonClustered Index آن را اصطلاحا Aligned می نمایند:

CREATE UNIQUE CLUSTERED INDEX IDXDATAID
ON DBO.DATA(DATECREATED, ID)
WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON)
ON PSDATA(DATECREATED);
CREATE NONCLUSTERED INDEX IDXDATADATEMODIFIED
ON DBO.DATA(DATEMODIFIED)
WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON)
ON PSDATA(DATECREATED);
GO

 مجدد پروسیجر را اجرا می نمائیم:

EXEC USP_TEST 100, '2016-08-25 00:00:00.000'

تصویر زیر پلن اجرایی کوئری را نشان می دهد:همان طور که مشاهده می نمائید پلن اجرایی پروسیجر تغییر یافته و Clustered Index Scan رخ داده است.

راه حل ها

حل مسئله مطرح شده می تواند یک Trade Off باشد. به عنوان یک راه حل می توان NonClustered Index را به صورت Aligned شده ایجاد نمود و پروسیجر را به صورت زیر بازنویسی کرد:

CREATE OR ALTER PROCEDURE USP_TEST
(@TOP TINYINT, @LASTDATEMODIFIED DATETIME)
AS
DECLARE
@BOUNDARYVALUESCOUNT INT
CREATE TABLE #PARTITIONS(PARTITIONNUM SMALLINT NOT NULL PRIMARY KEY);
-- GETTING NUMBER OF BOUNDARY VALUES IN PARTITION FUNCTION
SELECT @BOUNDARYVALUESCOUNT = MAX(BOUNDARY_ID)
FROM SYS.PARTITION_FUNCTIONS PF JOIN SYS.PARTITION_RANGE_VALUES PRF ON
PF.FUNCTION_ID = PRF.FUNCTION_ID
WHERE PF.NAME = 'PFDATA';
;WITH PARTITIONS(PARTITIONNUM)
AS
(
SELECT 1
UNION ALL
SELECT PARTITIONNUM + 1
FROM PARTITIONS
WHERE PARTITIONNUM <= @BOUNDARYVALUESCOUNT
)
INSERT INTO #PARTITIONS(PARTITIONNUM)
SELECT PARTITIONNUM FROM PARTITIONS;
;WITH STEPS1AND2(ID, DATECREATED, DATEMODIFIED)
AS
(
SELECT TOP 100 PARTDATA.ID, PARTDATA.DATECREATED, PARTDATA.DATEMODIFIED
FROM #PARTITIONS P
CROSS APPLY
(
SELECT TOP 100 ID, DATECREATED, DATEMODIFIED
FROM dbo.DATA
WHERE
DATEMODIFIED > @LASTDATEMODIFIED AND
$PARTITION.PFDATA(DATECREATED) = P.PARTITIONNUM
ORDER BY DATEMODIFIED
) PARTDATA
ORDER BY PARTDATA.DATEMODIFIED
)
-- STEP 3 - CI SEEK AS KEY LOOKUP OPERATION
SELECT S.ID, S.DATECREATED, S.DATEMODIFIED, D.PLACEHOLDER
FROM STEPS1AND2 S JOIN DBO.DATA D ON
D.ID = S.ID AND S.DATECREATED = D.DATECREATED
ORDER BY S.DATEMODIFIED

بخشی از پلن اجرایی پروسیجر بعد از بازنویسی:هدف مقاله این است که، صرفا ایجاد ایندکس بر روی شرط کوئری باعث بهبود Performance نمی شود، همواره و با توجه به شرایط باید Plan اجرای کوئری مورد بررسی قرار گیرد که آیا SQL Server از ایندکس ایجاد شده به شکل مطلوبی استفاده می نماید یا خیر. ضمن اینکه بسته به شرایط سیستم و نیازها می توان از یکسری امکانات ارائه شده در SQL Server استفاده نمود. این گونه نیست که همه Solution ها برای همه سیستم ها مناسب باشند.