Partition Table using min/max functions and Top N - Index selection and performance

February 20, 2017

 

אחד הבאגים שקיימים בSQL Server, ובאופן אישי מאד מתסכלים אותי, הוא באג שקשור לpartitioned tables.  כאשר אנו משתמשים בpartitioned table, ומבצעים שאילתה שמביאה כמות ספציפית של רשומות ע"י השימוש באופרטור top יחד עם order by או שאילתה אגרגטיבית עם min או max השרת יבצע table scan, גם אם יש לנו אינדקס מתאים.  כמובן שלרוב הpartitioned table שלנו מכילה מאות מיליוני רשומות (אם לא מיליארדי רשומות ויותר), ופעולת הtable scan נמשכת זמן רב.  הסיבה שהבאג הזה מאד מתסכל אותי, היא שהוא קיים כבר מגרסת 2005 ומייקרוספט מתעלמת ממנו למרות שיש עליו connect  פתוח כבר מעל 10 שנים (אם בא לכם לקרוא ו/או להצביע אפשר להיכנס ל

https://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance).


  בעיקרון יש דרכים לעקוף את הבאג ומטרת הפוסט הזה היא להראות אותם, אבל תחילה נראה את הבעיה.  נקים partition function, partition scheme וpartitioned table, ונכניס לטבלה 30000 רשומות:

 

use tempdb;
go

--Creating the partition function
create partition function pf(int) as range left for values (10000,20000,30000);
go

--Creating the partition scheme
create partition scheme ps as partition pf ALL TO ( [PRIMARY] );
go

--Creating the partition table
create table PartitionedTable (ID int NOT NULL IDENTITY(1,1) PRIMARY KEY, IntCol int, filler char(1000) not null default ('a')) on ps(ID);
go

--Inserting 30000 records
set nocount on;
declare @i int;
set @i = 1;
while (@i<>30001)
begin
    insert PartitionedTable (IntCol) values(CAST(RAND() * 300000 AS INT) + 1)
    set @i += 1;
end

נבנה אינדקס על השדה IntCol:

CREATE INDEX IX_PartitionedTable_IntCol on PartitionedTable (IntCol)

עכשיו נריץ את השאילתה הבאה:


set statistics io on
go

SELECT TOP 3 * 
FROM PartitionedTable 
ORDER BY IntCol, ID
Go


אם נבדוק את הstatistics io ואת הquery plan, נראה שהשרת ביצע 4716 logial reads, והשתמש בclustered index scan:

 

 


 כמו שאפשר לראות, האינדקס המתאים קיים, אבל השרת מתעלם ממנו ועובר על כל הטבלה.  מאחר שמדובר בבאג מאד ישן, כבר פורסם work around, שמאפשר להמשיך לעבוד עם partitioned table וtop אופרטור בלי שהשרת יבצע table scan.  הדרך היא מאד פשוטה – לעבוד עם derived table שניגשת לכל partition  ספציפי, לקחת ממנו את שלושת הרשומות שעונות לתנאים, ולאחר מכן לקחת מהderived table את הרשומות שחיפשנו.  הקוד יכול להראות כך:

SELECT TOP 3 *
FROM (
    SELECT TOP 3 * 
    FROM PartitionedTable
    WHERE $PARTITION.pf(PartitionedTable.ID)=1
    ORDER BY IntCol
    UNION ALL
    SELECT TOP 3 * 
    FROM PartitionedTable
    WHERE $PARTITION.pf(PartitionedTable.ID)=2
    ORDER BY IntCol
    UNION ALL
    SELECT TOP 3 * 
    FROM PartitionedTable
    WHERE $PARTITION.pf(PartitionedTable.ID)=3
    ORDER BY IntCol) DT
ORDER BY DT.IntCol, ID


אם נבדוק את כמות הlogical reads נראה שיש שיפור ניכר 42 בלבד לעומת ה4716 שהיו לנו קודם.  הquery plan קצת גדול מידי כדי להיכנס להדפסה, אבל אם תבדקו בסביבה שלכם את הקוד, תוכלו לראות שיש שימוש באינדקס שנבנה על העמודה ואין לנו table scan.  הדרך הזאת אולי עובדת ומשפרת לנו את הביצועים, אבל היא מאד בעייתית.  מה קורה אם יש שינוי ומוסיפים לנו partition  נוסף?  נאלץ לשנות גם את הקוד שלנו, לכן הפתרון הזה לא יהיה שימושי במקרים רבים.  ניתן כמובן לשפר אותו ולכתוב פתרון שלא משתמש בצורה hard coded במספרי הpartition.  מספרי הpartition הקיימים בכל טבלה רשומים גם בטבלת sys.partitions.  אפשר לנצל את המידע ששמור בsys.partitions כדי לקבל את מספרי הpartitions הקיימים.  הטבלה שומרת את מספרי הpartition לכל אובייקט ואינדקס, לכן השאילתה הבאה יכולה להביא לנו את מספרי הpartition:


SELECT partition_number
FROM sys.partitions
WHERE object_id = OBJECT_ID('PartitionedTable') AND index_id < 2

 

עכשיו נשאר לקחת את התוצאה של השאילתה ולהריץ עליה את השאילתה המקורית שלנו שמחפשת את שלושת הערכים הנמוכים ביותר בטבלה.  אפשר לעשות את זה עם cross apply.  השאילתה שלנו תיראה כך:


;WITH PartitionsNumbers as (
    SELECT partition_number
    from sys.partitions
    where object_id = object_id('dbo.PartitionedTable') and index_id < 2) 
select top 3 DT.* 
from PartitionsNumbers cross apply (select top 3 *
    from dbo.PartitionedTable where $partition.pf(dbo.PartitionedTable.ID) = PartitionsNumbers.partition_number
    order by IntCol) as DT
order by IntCol, ID

 

הפעם השרת ביצע 38 logical reads.  עכשיו נשאר רק לבצע בדיקה של הוספת partitions  נוספים ובדיקה של השאילתה פעם נוספת.  הוספה של שני הpartitions הנוספים מתבצעת ע"י הקוד הבא:

ALTER PARTITION FUNCTION pf()  
SPLIT RANGE (12000)
go

ALTER PARTITION SCHEME ps
NEXT USED [PRIMARY]
GO

ALTER PARTITION FUNCTION pf()  
SPLIT RANGE (16000)
go


ALTER PARTITION SCHEME ps
NEXT USED [PRIMARY]

 

נריץ את השאילתה שלנו פעם נוספת ונקבל את אותם תוצאות, אבל הפעם יש לנו יותר logical reads – 55 דפים.  הסיבה היא כמובן שככל שיש לנו יותר partitions, כך נבצע יותר פעמים את החיפוש של שלושת הערכים הנמוכים בתוך הpartition, אבל בכל מקרה זה עדיף על table scan.  אני חייב להודות שאני לא מת על קוד כזה, אבל לצערי מייקרוסופט לא מוכנה לתקן את הבאג, והיא מאלצת אותנו למצא פתרונות מהסוג הזה.

Please reload

Featured Posts

I'm busy working on my blog posts. Watch this space!

Please reload

Recent Posts

October 31, 2017

October 29, 2017

Please reload

Archive
Please reload

Search By Tags