none
Partition Function SQL Server 2008 RRS feed

  • שאלה

  • שלום,

    אני רוצה להקים מערך Partition על חלק מהטבלאות שלנו - סביבת 2008 (לא R2).

    ה-Partition key יהיה מבוסס תאריך.

    יש שתי שאלות (לעת עתה)

      • האם יש כלל אצבע לגבי כמות שורות לכל partition - אני מניחה שהתשובה היא - "תלוי", אבל לפחות אשמח לדעת מה צריך לקחת בחשבון.
      • האם יש בעיה מיוחדת לעשות interval משתנה ב-Partition function? כלומר, שהמידע של 2008,2009,2010 יהיה ב-partition שנתי, ומ-2011 והלאה המרווח יהיה לפי חודש?
        המידע הישן הוא סטטי.

    כרגע יש תהליך ארכיוני שמעביר מידע ישן ל-DB נפרד ומוחק את המידע מה- online DB.
    אנחנו צריכים לשמור את המידע לצרכי רגולציות, ופה ושם כאשר צריך לתשאל את המידע הישן, הצורך בלתשאל שני DBS גורם לסירבול בתהליך,
    לכן חשבתי על partitions.
    בכל הטבלאות יש עמודת תאריך, ותמיד השאילתות כוללות את התאריך.

    תודה

    רוני.

    יום רביעי 17 אוקטובר 2012 14:07

תשובות

  • על הכיפק, תודה :)

    הוספתי להנאתכם את הפרוצדורה המרוכזת שמפצלת את כל ה-partition functions.

    הפרוצדורה מניחה כמובן שכל ה-partition functions/schemes קיימות, ושמם נמצא בתוך טבלת הניהול.
    עשיתי לזה קצת Debugging ועושה רושם שזה עובד :)

    שבת שלום !

    CREATE TABLE PartitionManagement
    ( PartitionManagementID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    	TableName VARCHAR(20) NOT NULL,
    	PartitionFunction VARCHAR(30) NOT NULL,
    	PartitionScheme VARCHAR(30) NOT NULL,
    	PartitionIntervalType VARCHAR(10) NOT NULL,
    	PartitionsAhead INT NOT NULL,
    )	
    
    INSERT INTO PartitionManagement (TableName ,PartitionFunction ,PartitionScheme ,PartitionIntervalType,PartitionsAhead )
    VALUES							('TRILILI_XDIV',	'pf_TRILILI_XDIV',		'ps_TRILILI_XDIV', 'Year', 5),
    								('TRILILI_XDMU',	'pf_TRILILI_XDMU',		'ps_TRILILI_XDMU', 'Year', 5),
    								('TRILILI_XDPO',	'pf_TRILILI_XDPO',		'ps_TRILILI_XDPO', 'Month', 12),
    								('TRILILI_XEMU',	'pf_TRILILI_XEMU',		'ps_TRILILI_XEMU', 'Month', 12),
    								('TRILILI_XEPO',	'pf_TRILILI_XEPO',		'ps_TRILILI_XEPO', 'Month', 12),
    								('TRILILI_XFMU',	'pf_TRILILI_XFMU',		'ps_TRILILI_XFMU', 'Month', 12),
    								('TRILILI_XFPO',	'pf_TRILILI_XFPO',		'ps_TRILILI_XFPO', 'Month', 12),
    								('TRILILI_XRSK',	'pf_TRILILI_XRSK',		'ps_TRILILI_XRSK', 'Month', 12),
    
    GO
    -- =============================================  
    -- Author:  Roni Vered
    -- Create date: 2012-10-18
    -- Description: split partition FUNCTIONS 
    -- Job name PRD - Partitions - SPLIT_AddPartition
    -- =============================================  
    
    ALTER PROCEDURE [dbo].[usp_Partition_SPLIT_AddPartitions]  
    AS  
       
     declare @d datetime  
     declare @MaxDate datetime  
     declare @NowDateTime datetime  
     declare @ServerTTL smallint  
     declare @PartitionFunctionID int  
     declare @PartitionInterval int   
     --Variables for PartitionManagement table info
     declare @TableName varchar(20), 
    		 @PartitionFunction varchar(30), 
    		 @PartitionScheme varchar(30), 
    		 @PartitionIntervalType varchar(10) , 
    		 @PartitionsAhead int
     
    
    --The partition interval 
     set @PartitionInterval=1
     
      
    DECLARE db_cursor CURSOR FOR      
    	 SELECT  TableName, PartitionFunction, PartitionScheme, PartitionIntervalType, PartitionsAhead
    	 FROM    PartitionManagement
    	 ORDER BY TableName
              
      OPEN db_cursor       
      FETCH NEXT FROM db_cursor INTO  @TableName, @PartitionFunction, @PartitionScheme, @PartitionIntervalType, @PartitionsAhead
           
      WHILE @@FETCH_STATUS = 0       
      BEGIN
      
    		--Finding the partition function of the table @TableName
    		select @PartitionFunctionID= function_id from sys.partition_functions where name=@PartitionFunction
    		
    		--Amount of partitions adhead 
    		set @ServerTTL=@PartitionsAhead
    		
    		--The Max datetime of existing partitions
    		if @PartitionIntervalType='YEAR'
    			set @MaxDate=dateadd (YEAR ,@ServerTTL,getdate())  
    		else if @PartitionIntervalType='MONTH'
    			set @MaxDate=dateadd (month,@ServerTTL,getdate())  
    		
    		--The Current truncated datetime
    		set @NowDateTime=cast (cast(getDate() As Date) as datetime)		
    		
    		 --Checking what is the latest existing partition date range 
    		 select top 1 @d= cast(value as datetime) from sys.partition_range_values   
    		 where function_id=@PartitionFunctionID and cast(value as datetime) <> '2020-01-01 00:00:00'   
    		 order by value desc  
    		
    		 /*
    		 select top 1 cast(value as datetime) from sys.partition_range_values   
    		 where function_id=65556 and cast(value as datetime) <> '2020-01-01 00:00:00'   
    		 order by value desc  
    		 */
    		 
    		 --Advancing @d 1 month/year ahead. (we cannot user variables in the datepart of DATEADD function, hence the IF condition is used)
    		 if @PartitionIntervalType='YEAR'
    			set @d=dateadd(YEAR,@PartitionInterval,@d)
    		 else if @PartitionIntervalType='MONTH'
    			set @d=dateadd(MONTH,@PartitionInterval,@d)
    			
    		 /* --Debugging purposes.
    		 print '--------------------------------------------'
    		 print '@TableName=	'+@TableName
    		 print '@d=			'+convert (varchar(20),@d,120)
    		 print '@MaxDate=	'+convert (varchar(20),@d,120)
    		 print '@PartitionIntervalType= '+@PartitionIntervalType
    		 print '@PartitionsAhead= '+cast ( @PartitionsAhead as varchar(10))		 
    		 */
    		 
    		 --@d<=@MaxDate  then we need to create more partitions
    		while @d <= @MaxDate
    			 begin  
    			  SET LOCK_TIMEOUT 10000  
    			  
    			  DECLARE @sql VARCHAR(256) = 'ALTER PARTITION SCHEME '+@PartitionScheme+' NEXT USED [PRIMARY]'			  
    			  	--print (@sql)			
    			  	EXEC (@sql)			  
    			  
    			  SET @sql = 'ALTER PARTITION FUNCTION '+@PartitionFunction+'() split range ('''+convert (varchar(20),@d,120) +''')'			  
    				--print (@sql)			  
    				EXEC (@sql)				
    			  
    			  if @PartitionIntervalType='YEAR'
    				set @d=dateadd(year,1,@d)  
    			  else if @PartitionIntervalType='MONTH'
    				set @d=dateadd(month,1,@d)  
    						
    			 end  
    		   
    		 if @PartitionIntervalType='YEAR'
    			set @MaxDate= dateadd(YEAR,-1*@PartitionInterval,@NowDateTime)  
    		 else if @PartitionIntervalType='MONTH'
    			set @MaxDate= dateadd(MONTH,-1*@PartitionInterval,@NowDateTime)  
    			
    
           FETCH NEXT FROM db_cursor INTO  @TableName, @PartitionFunction, @PartitionScheme, @PartitionIntervalType, @PartitionsAhead
          
      END       
        
     CLOSE db_cursor       
     DEALLOCATE db_cursor    
     
      
    return 0
    GO
    

    • הוצע כתשובה על-ידי Itai Binyamin יום ראשון 21 אוקטובר 2012 09:21
    • סומן כתשובה על-ידי pituachMVP, Editor יום שישי 11 ינואר 2013 07:15
    יום שישי 19 אוקטובר 2012 13:16
  • הי רוני,

    שאלות טובות את שואלת...

    1. אין כלל אצבע לגבי כמות השורות בכל Partition, ואני לא חושב שזה בכלל צריך להיות השיקול. ההחלטה איך לחלק את ה-Partitions צריכה להיות מבוססת על הדרך שבה את רוצה לנהל את הנתונים בטבלה. אם, למשל, רוב השאילתות מתייחסות לנתונים בחודש האחרון, ואת צריכה לשמור בטבלה 3 שנים אחורה, אז נראה לי הגיוני לחלק את הטבלה ל-Partitions לפי חודשים...

    2. אין בעיה לחלק את הטבלה ל-Partitions בגדלים שונים (שנים, חודשים, וכו'). מבחינת SQL Server, מה שמגדיר את החלוקה ל-Partitions זה ה-Partition Function, שהוא בסך הכל אוסף של ערכי גבולות בין Partitions. את יכולה לשים שם איזה ערכים שאת רוצה. אני מבין את הרציונל של Partitions גדולים יותר למידע ישן ו-Partitions קטנים יותר למידע חדש, אבל אני לא חושב שיש סיבה לעשות את זה. בכל אופן, זה יסרבל מאוד את התחזוקה של ה-Partitions. את תצטרכי לכתוב לוגיקה יותר מורכבת כדי לבצע Split ו-Merge באופן שוטף על מנת להתאים את הטבלה לדרך שבה את רוצה לחלק את ה-Partitions. אם כל ה-Partitions הם בגודל אחיד (חודש, נניח), אז התחזוקה הרבה יותר פשוטה.

    3. אם יש לך כמה טבלאות, שכולן צריכות להיות מחולקות ל-Partitions באותה צורה בדיוק, זה יכול להיות מפתה ליצור רק Partition Function אחת ורק Partition Scheme אחת לכולן. זה בהחלט אפשרי, אבל אני חושב שזאת טעות, מכיוון שזה מייצר תלות בין הטבלאות. כל פעולת Split וכל פעולת Merge משפיעות על כל הטבלאות בו-זמנית. זה יכול להיות כבד ומסוכן. אני מעדיף ליצור Partition Function וגם Partition Scheme לכל טבלה בנפרד, ואז לטפל בכל טבלה בנפרד, בלי שום תלות ביניהן.

    4. אם יש לך טבלת ניהול מסודרת שמכילה את כל המידע הדרוש על כל ה-Partition Functions, אני לא רואה סיבה לא לבצע את כל ה-Splits בפרוצדורה אחת. זה נראה לי הרבה יותר נוח וקל לתחזוקה.

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Roni Vered Adar יום שישי 19 אוקטובר 2012 13:17
    יום שישי 19 אוקטובר 2012 07:47
    מנחה דיון

כל התגובות

  • שאלות נוספות שצצו לי.

      • אם יש לי מספר טבלאות שאני רוצה לחלק אותן לפי חודשים, האם מומלץ לעשות partition function/scheme נפרד לכל טבלה (הם יהיו זהים למעט השם), או שאפשר לחלוק - כלומר, ליצור partition function monthly ולשייך אליהן מספר טבלאות?
      • כרגע אנחנו רוצים לאגור ולא למחוק, אז צריך רק ליצור מנגנון שיבצע split.
        מכיוון שיש לנו בערך 15 טבלאות שיהיו עם Partitions - חצי מהן על בסיס שנתי וחצי על בסיס חודשי, חשבתי לעשות פרוצדורה אחת שתבצע את כל ה-partition split לכל ה-functions.
        היא תרוץ בלולאה כמספר ה-functions. המידע על כל function יישמר בתוך משתנה שיילקח מתוך טבלת ניהול שתכיל את כל הפרטים על ה-functions.
      • אופציה נוספת לשאלה 2, היא לעשות פרוצדורה נפרדת לכל function.

    אני נוטה לכיוון פרוצדורה אחת לכולם, כי זה קריא יותר, ונוח לניהול אח"כ, אבל תוהה אם יש השלכות בעייתיות בריכוז כל פקודות הsplit של כל הfunction בפרוצדורה אחת.

    יום חמישי 18 אוקטובר 2012 09:50
  • הי רוני,

    שאלות טובות את שואלת...

    1. אין כלל אצבע לגבי כמות השורות בכל Partition, ואני לא חושב שזה בכלל צריך להיות השיקול. ההחלטה איך לחלק את ה-Partitions צריכה להיות מבוססת על הדרך שבה את רוצה לנהל את הנתונים בטבלה. אם, למשל, רוב השאילתות מתייחסות לנתונים בחודש האחרון, ואת צריכה לשמור בטבלה 3 שנים אחורה, אז נראה לי הגיוני לחלק את הטבלה ל-Partitions לפי חודשים...

    2. אין בעיה לחלק את הטבלה ל-Partitions בגדלים שונים (שנים, חודשים, וכו'). מבחינת SQL Server, מה שמגדיר את החלוקה ל-Partitions זה ה-Partition Function, שהוא בסך הכל אוסף של ערכי גבולות בין Partitions. את יכולה לשים שם איזה ערכים שאת רוצה. אני מבין את הרציונל של Partitions גדולים יותר למידע ישן ו-Partitions קטנים יותר למידע חדש, אבל אני לא חושב שיש סיבה לעשות את זה. בכל אופן, זה יסרבל מאוד את התחזוקה של ה-Partitions. את תצטרכי לכתוב לוגיקה יותר מורכבת כדי לבצע Split ו-Merge באופן שוטף על מנת להתאים את הטבלה לדרך שבה את רוצה לחלק את ה-Partitions. אם כל ה-Partitions הם בגודל אחיד (חודש, נניח), אז התחזוקה הרבה יותר פשוטה.

    3. אם יש לך כמה טבלאות, שכולן צריכות להיות מחולקות ל-Partitions באותה צורה בדיוק, זה יכול להיות מפתה ליצור רק Partition Function אחת ורק Partition Scheme אחת לכולן. זה בהחלט אפשרי, אבל אני חושב שזאת טעות, מכיוון שזה מייצר תלות בין הטבלאות. כל פעולת Split וכל פעולת Merge משפיעות על כל הטבלאות בו-זמנית. זה יכול להיות כבד ומסוכן. אני מעדיף ליצור Partition Function וגם Partition Scheme לכל טבלה בנפרד, ואז לטפל בכל טבלה בנפרד, בלי שום תלות ביניהן.

    4. אם יש לך טבלת ניהול מסודרת שמכילה את כל המידע הדרוש על כל ה-Partition Functions, אני לא רואה סיבה לא לבצע את כל ה-Splits בפרוצדורה אחת. זה נראה לי הרבה יותר נוח וקל לתחזוקה.

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Roni Vered Adar יום שישי 19 אוקטובר 2012 13:17
    יום שישי 19 אוקטובר 2012 07:47
    מנחה דיון
  • על הכיפק, תודה :)

    הוספתי להנאתכם את הפרוצדורה המרוכזת שמפצלת את כל ה-partition functions.

    הפרוצדורה מניחה כמובן שכל ה-partition functions/schemes קיימות, ושמם נמצא בתוך טבלת הניהול.
    עשיתי לזה קצת Debugging ועושה רושם שזה עובד :)

    שבת שלום !

    CREATE TABLE PartitionManagement
    ( PartitionManagementID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    	TableName VARCHAR(20) NOT NULL,
    	PartitionFunction VARCHAR(30) NOT NULL,
    	PartitionScheme VARCHAR(30) NOT NULL,
    	PartitionIntervalType VARCHAR(10) NOT NULL,
    	PartitionsAhead INT NOT NULL,
    )	
    
    INSERT INTO PartitionManagement (TableName ,PartitionFunction ,PartitionScheme ,PartitionIntervalType,PartitionsAhead )
    VALUES							('TRILILI_XDIV',	'pf_TRILILI_XDIV',		'ps_TRILILI_XDIV', 'Year', 5),
    								('TRILILI_XDMU',	'pf_TRILILI_XDMU',		'ps_TRILILI_XDMU', 'Year', 5),
    								('TRILILI_XDPO',	'pf_TRILILI_XDPO',		'ps_TRILILI_XDPO', 'Month', 12),
    								('TRILILI_XEMU',	'pf_TRILILI_XEMU',		'ps_TRILILI_XEMU', 'Month', 12),
    								('TRILILI_XEPO',	'pf_TRILILI_XEPO',		'ps_TRILILI_XEPO', 'Month', 12),
    								('TRILILI_XFMU',	'pf_TRILILI_XFMU',		'ps_TRILILI_XFMU', 'Month', 12),
    								('TRILILI_XFPO',	'pf_TRILILI_XFPO',		'ps_TRILILI_XFPO', 'Month', 12),
    								('TRILILI_XRSK',	'pf_TRILILI_XRSK',		'ps_TRILILI_XRSK', 'Month', 12),
    
    GO
    -- =============================================  
    -- Author:  Roni Vered
    -- Create date: 2012-10-18
    -- Description: split partition FUNCTIONS 
    -- Job name PRD - Partitions - SPLIT_AddPartition
    -- =============================================  
    
    ALTER PROCEDURE [dbo].[usp_Partition_SPLIT_AddPartitions]  
    AS  
       
     declare @d datetime  
     declare @MaxDate datetime  
     declare @NowDateTime datetime  
     declare @ServerTTL smallint  
     declare @PartitionFunctionID int  
     declare @PartitionInterval int   
     --Variables for PartitionManagement table info
     declare @TableName varchar(20), 
    		 @PartitionFunction varchar(30), 
    		 @PartitionScheme varchar(30), 
    		 @PartitionIntervalType varchar(10) , 
    		 @PartitionsAhead int
     
    
    --The partition interval 
     set @PartitionInterval=1
     
      
    DECLARE db_cursor CURSOR FOR      
    	 SELECT  TableName, PartitionFunction, PartitionScheme, PartitionIntervalType, PartitionsAhead
    	 FROM    PartitionManagement
    	 ORDER BY TableName
              
      OPEN db_cursor       
      FETCH NEXT FROM db_cursor INTO  @TableName, @PartitionFunction, @PartitionScheme, @PartitionIntervalType, @PartitionsAhead
           
      WHILE @@FETCH_STATUS = 0       
      BEGIN
      
    		--Finding the partition function of the table @TableName
    		select @PartitionFunctionID= function_id from sys.partition_functions where name=@PartitionFunction
    		
    		--Amount of partitions adhead 
    		set @ServerTTL=@PartitionsAhead
    		
    		--The Max datetime of existing partitions
    		if @PartitionIntervalType='YEAR'
    			set @MaxDate=dateadd (YEAR ,@ServerTTL,getdate())  
    		else if @PartitionIntervalType='MONTH'
    			set @MaxDate=dateadd (month,@ServerTTL,getdate())  
    		
    		--The Current truncated datetime
    		set @NowDateTime=cast (cast(getDate() As Date) as datetime)		
    		
    		 --Checking what is the latest existing partition date range 
    		 select top 1 @d= cast(value as datetime) from sys.partition_range_values   
    		 where function_id=@PartitionFunctionID and cast(value as datetime) <> '2020-01-01 00:00:00'   
    		 order by value desc  
    		
    		 /*
    		 select top 1 cast(value as datetime) from sys.partition_range_values   
    		 where function_id=65556 and cast(value as datetime) <> '2020-01-01 00:00:00'   
    		 order by value desc  
    		 */
    		 
    		 --Advancing @d 1 month/year ahead. (we cannot user variables in the datepart of DATEADD function, hence the IF condition is used)
    		 if @PartitionIntervalType='YEAR'
    			set @d=dateadd(YEAR,@PartitionInterval,@d)
    		 else if @PartitionIntervalType='MONTH'
    			set @d=dateadd(MONTH,@PartitionInterval,@d)
    			
    		 /* --Debugging purposes.
    		 print '--------------------------------------------'
    		 print '@TableName=	'+@TableName
    		 print '@d=			'+convert (varchar(20),@d,120)
    		 print '@MaxDate=	'+convert (varchar(20),@d,120)
    		 print '@PartitionIntervalType= '+@PartitionIntervalType
    		 print '@PartitionsAhead= '+cast ( @PartitionsAhead as varchar(10))		 
    		 */
    		 
    		 --@d<=@MaxDate  then we need to create more partitions
    		while @d <= @MaxDate
    			 begin  
    			  SET LOCK_TIMEOUT 10000  
    			  
    			  DECLARE @sql VARCHAR(256) = 'ALTER PARTITION SCHEME '+@PartitionScheme+' NEXT USED [PRIMARY]'			  
    			  	--print (@sql)			
    			  	EXEC (@sql)			  
    			  
    			  SET @sql = 'ALTER PARTITION FUNCTION '+@PartitionFunction+'() split range ('''+convert (varchar(20),@d,120) +''')'			  
    				--print (@sql)			  
    				EXEC (@sql)				
    			  
    			  if @PartitionIntervalType='YEAR'
    				set @d=dateadd(year,1,@d)  
    			  else if @PartitionIntervalType='MONTH'
    				set @d=dateadd(month,1,@d)  
    						
    			 end  
    		   
    		 if @PartitionIntervalType='YEAR'
    			set @MaxDate= dateadd(YEAR,-1*@PartitionInterval,@NowDateTime)  
    		 else if @PartitionIntervalType='MONTH'
    			set @MaxDate= dateadd(MONTH,-1*@PartitionInterval,@NowDateTime)  
    			
    
           FETCH NEXT FROM db_cursor INTO  @TableName, @PartitionFunction, @PartitionScheme, @PartitionIntervalType, @PartitionsAhead
          
      END       
        
     CLOSE db_cursor       
     DEALLOCATE db_cursor    
     
      
    return 0
    GO
    

    • הוצע כתשובה על-ידי Itai Binyamin יום ראשון 21 אוקטובר 2012 09:21
    • סומן כתשובה על-ידי pituachMVP, Editor יום שישי 11 ינואר 2013 07:15
    יום שישי 19 אוקטובר 2012 13:16