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

در مقاله موضوعی نهفته در Data Partitioning نشان دادیم که چگونه یک Aligned Nonclustered Index می‌تواند Plan اجرایی یک کوئری را تغییر دهد و باعث کاهش سرعت اجرای آن کوئری گردد. جهت برطرف نمودن مشکل، مجبور به بازنویسی کوئری شدیم.

در این مقاله به بررسی نمونه دیگری از تاثیر Align نمودن Nonclustered Indexها می‌پردازیم. جهت یادآوری Aligned Nonclustered Index یک ایندکس است که مانند Clustered ایندکس جدول پارتیشن شده است. ابتدا با استفاده از کد زیر یک Database و یک Table ایجاد می نمائیم:

Use master
GO

Create Database Test
GO

Use Test
GO
CREATE TABLE Numbers
(
  Number INT NOT NULL,
  CONSTRAINT PK_Numbers 
    PRIMARY KEY CLUSTERED (Number)
)
GO

جدول ایجاد شده را با استفاده از داده‌های زیر پر می‌نمائیم:

INSERT INTO Numbers
SELECT
  (a.Number * 256) + b.Number AS Number
FROM 
  (
    SELECT number
    FROM master..spt_values
    WHERE 
      type = 'P'
      AND number <= 255
  ) a (Number),
  (
    SELECT number
    FROM master..spt_values
    WHERE 
      type = 'P'
      AND number <= 255
  ) b (Number)
GO

نکته: از جدول ایجاد شده فقط جهت Load دیتا استفاده خواهد شد.

دستورات زیر Partition Scheme و Partition Function را ایجاد می نمایند:

CREATE PARTITION FUNCTION PF (integer) 
AS RANGE RIGHT
FOR VALUES 
  (
  ۱۰۰۰۰, ۲۰۰۰۰, ۳۰۰۰۰, ۴۰۰۰۰, ۵۰۰۰۰,
  ۶۰۰۰۰, ۷۰۰۰۰, ۸۰۰۰۰, ۹۰۰۰۰, ۱۰۰۰۰۰,
  ۱۱۰۰۰۰, ۱۲۰۰۰۰, ۱۳۰۰۰۰, ۱۴۰۰۰۰, ۱۵۰۰۰۰
  );
GO

CREATE PARTITION SCHEME PS 
AS PARTITION PF 
ALL TO ([PRIMARY]);
GO

سپس یک جدول به شکل پارتیشن شده ایجاد و آن را با یکسری داده پر می‌نمائیم:

CREATE TABLE dbo.PartitionedTbl
(
    RowID       integer IDENTITY NOT NULL,
    UserID      integer NOT NULL,
    SessionID   integer NOT NULL,
    LocationID  integer NOT NULL,
 
    CONSTRAINT PK_PartitionedTbl
    PRIMARY KEY CLUSTERED (RowID)
    ON PS (RowID)
);
GO
INSERT dbo.PartitionedTbl WITH (TABLOCKX)
    (UserID, SessionID, LocationID)
SELECT
    ABS(CHECKSUM(NEWID())) % 50,
    ABS(CHECKSUM(NEWID())) % 30,
    ABS(CHECKSUM(NEWID())) % 10
FROM dbo.Numbers AS N
WHERE
    N.Number BETWEEN 1 AND 150000;
GO

فرض کنید می خواهیم کوئری زیر را بر روی جدول PartitionedTbl اجرا نمائیم:

SELECT LocationID, UserID, SessionID, COUNT_BIG(*)
FROM dbo.PartitionedTbl
GROUP BY LocationID, UserID, SessionID;
GO

جهت افزایش سرعت اجرای این کوئری یک Nonclustered Index به شکل زیر ایجاد می‌کنیم:

CREATE NONCLUSTERED INDEX IXNCUserIDSessionIDLocationID
ON dbo.PartitionedTbl (UserID, SessionID, LocationID)
ON [Primary];
GO

این ایندکس به شکل Align نشده ایجاد گردید. تصویر زیر Plan اجرایی کوئری را نشان می دهد:

همان طور که در تصویر مشاهده می‌نمائید Query Optimizer از اپراتور Stream Aggregate استفاده نموده است. حالا Nonclustered Index جدول را اصطلاحا Align می‌نمائیم:

CREATE NONCLUSTERED INDEX IXNCUserIDSessionIDLocationID
ON dbo.PartitionedTbl (UserID, SessionID, LocationID)
With (Drop_Existing = On)
ON PS (RowID);
GO

تصویر زیر Plan اجرایی کوئری را نمایش می‌دهد:

تصویر نشان می‌دهد که اپراتور Stream Aggregate به اپراتور Hash Match Aggregate تغییر یافته است.

اپراتور Hash Match Aggregate دو مشکل بزرگ دارد:

• اصطلاحا یک Blocking Operator است. یعنی تا همه ردیف ها را از اپراتور قبلی دریافت نکند هیچ ردیفی را به کاربر برنمی گرداند.
• این اپراتور جهت نگهداری Hash Table به Memory نیاز دارد.

تا اینجای کار هدف ما فقط نمایش دادن یک مشکل بود. اصولا Data Partitioning زمانی جهت بهبود Execution Plan می تواند مفید باشد که Partition Elimination رخ دهد. به بیان ساده همه پارتیشن ها درگیر اجرای کوئری نشوند.

$Partition در واقع یک Function است که از طریق آن می توان صراحتا حذف یکسری از پارتیشن ها را در کوئری انجام داد. همچنین در این مثال ما نیاز داریم که ترتیب ستون های شرکت کننده در قسمت Group By را تغییر دهیم. این ستون ها دقیقا باید مطابق با ترتیبی که در ایندکس قرار گرفته اند در قسمت Group By استفاده شوند.با توجه به توضیحات ارائه شده کوئری مورد بحث را به شکل زیر بازنویسی می‌نمائیم:

SELECT LocationID, UserID, SessionID, COUNT_BIG(*)
FROM dbo.PartitionedTbl 
WHERE $PARTITION.PF(RowID) = 1
GROUP BY UserID, SessionID, LocationID;

تصویر زیر Plan اجرایی کوئری را نمایش می‌دهد: