Partition Table using min/max functions and Top N - Index selection and performance
אחד הבאגים שקיימים בSQL Server, ובאופן אישי מאד מתסכלים אותי, הוא באג שקשור לpartitioned tables. כאשר אנו משתמשים בpartitioned table, ומבצעים שאילתה שמביאה כמות ספציפית של רשומות ע"י השימוש באופרטור top יחד עם order by או שאילתה אגרגטיבית עם min או max השרת יבצע table scan, גם אם יש לנו אינדקס מתאים. כמובן שלרוב הpartitioned table שלנו מכילה מאות מיליוני רשומות (אם לא מיליארדי רשומות ויותר), ופעולת הtable scan נמשכת זמן רב. הסיבה שהבאג הזה מאד מתסכל אותי, היא שהוא קיים כבר מגרסת 2005 ומייקרוספט מתעלמת ממנו למרות שיש עליו connect פתוח כבר מעל 10 שנים (אם בא לכם לקרוא ו/או להצביע אפשר להיכנס ל
בעיקרון יש דרכים לעקוף את הבאג ומטרת הפוסט הזה היא להראות אותם, אבל תחילה נראה את הבעיה. נקים 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. אני חייב להודות שאני לא מת על קוד כזה, אבל לצערי מייקרוסופט לא מוכנה לתקן את הבאג, והיא מאלצת אותנו למצא פתרונות מהסוג הזה.
