Thursday, March 20, 2014

SQL Server Database Table Partitioning...

If we work with large databases, definitely we need to partition large database tables. Depending on the requirement, we can partition data on yearly, monthly or daily basics. In this example, tables are partitions based on the below scenario.

  • Data is partitioned based on the partition granularity, whether Daily or Monthly 
  • For each year, separate file group was created.
  • If data is older than the prior year, those are partitioned on yearly basics for both the daily and monthly partitions. 
  • For monthly partitioning, data within the current year and the prior year are partitioned on monthly basics.
  • For daily partitions, if data is older than 3 months, then those are partitioned on monthly basics, else data is partitioned on daily basics.

To partition data on the above criteria, stored procedure was implemented as below. It was implemented to dynamically create partitions for any table on any database. Also it is possible to provide any column with the date key in the 'YYYYMMDD' format as the PartitionKey. Also have to provide the file storage location. Partition granularity is provided as 'D' for Daily partitions and as 'M' for monthly partitions.

   1: IF EXISTS (
   2:        SELECT NAME
   3:        FROM   sys.procedures
   4:        WHERE  NAME = 'SP_ManageDBPartitions'
   5:    )
   6:     DROP PROCEDURE SP_ManageDBPartitions
   7: GO
   8:  
   9: CREATE PROCEDURE SP_ManageDBPartitions
  10: (
  11:  @partitionTableName VARCHAR(100)
  12:  ,@partitionGranularity VARCHAR(100)
  13:  ,@databaseName VARCHAR(100)
  14:  ,@tableFilePath VARCHAR(1000)
  15:  ,@partitionKey VARCHAR(100)
  16: )
  17: AS
  18: BEGIN
  19:  
  20: DECLARE @dateToday                  DATETIME
  21: DECLARE @yearToday                  VARCHAR(4)
  22: DECLARE @previousYear               VARCHAR(4)
  23: DECLARE @previous2Year VARCHAR(4)
  24: DECLARE @fileGroupName              VARCHAR(100)
  25: DECLARE @previousYearFileGroupName  VARCHAR(100)
  26: DECLARE @previous2YearFileGroupName  VARCHAR(100)
  27: DECLARE @tableName                  VARCHAR(100)
  28: DECLARE @granularity VARCHAR(100)
  29: DECLARE @partitionFunctionName VARCHAR(100)
  30: DECLARE @partitionSchemeName VARCHAR(100)
  31: DECLARE @datePartitionStartDate DATETIME
  32: DECLARE @datePartitionEndDate DATETIME
  33:  
  34:  
  35: SET @tableName = @partitionTableName
  36: SET @granularity=@partitionGranularity
  37: SET @partitionFunctionName=@tableName+'_PartitionFunction'
  38: SET @partitionSchemeName=@tableName+'_PartitionScheme'
  39: SET @dateToday = DATEADD(DAY,1,GETDATE())
  40: SET @datePartitionStartDate=SUBSTRING(CONVERT(VARCHAR(8),DATEADD(MONTH, -2,@dateToday),112),1,6)+'01'
  41: SET @datePartitionEndDate=CONVERT(VARCHAR(8),@dateToday,112)
  42: SET @yearToday = CAST((YEAR(@dateToday)) AS VARCHAR(4))
  43: SET @previousYear = CAST((YEAR(@dateToday)-1) AS VARCHAR(4))
  44: SET @previous2Year = CAST((YEAR(@dateToday)-2) AS VARCHAR(4))
  45: SET @fileGroupName = @databaseName + '_' + @yearToday
  46: SET @previousYearFileGroupName = @databaseName + '_' + @previousYear
  47: SET @previous2YearFileGroupName = @databaseName + '_' + @previous2Year
  48:  
  49: --Create FileGroups / Files
  50:  
  51: IF NOT EXISTS(
  52:        SELECT groupname
  53:        FROM   sysfilegroups
  54:        WHERE  groupname = @fileGroupName
  55:    )
  56: BEGIN
  57:     DECLARE @sql NVARCHAR(4000)
  58:     SET @sql = 'ALTER DATABASE '+@databaseName+' ADD FILEGROUP [' + @fileGroupName
  59:         + ']'
  60:    
  61:     EXEC sp_executesql @sql
  62: END
  63:  
  64: IF NOT EXISTS(
  65:        SELECT groupname
  66:        FROM   sysfilegroups
  67:        WHERE  groupname = @previousYearFileGroupName
  68:    )
  69: BEGIN
  70:     DECLARE @sqlPrevious NVARCHAR(4000)
  71:     SET @sqlPrevious = 'ALTER DATABASE '+@databaseName+' ADD FILEGROUP [' + @previousYearFileGroupName
  72:         + ']'
  73:    
  74:     EXEC sp_executesql @sqlPrevious
  75: END
  76:  
  77:  
  78: IF NOT EXISTS(
  79:        SELECT groupname
  80:        FROM   sysfilegroups
  81:        WHERE  groupname = @previous2YearFileGroupName
  82:    )
  83: BEGIN
  84:     DECLARE @sqlPrevious2 NVARCHAR(4000)
  85:     SET @sqlPrevious2 = 'ALTER DATABASE '+@databaseName+' ADD FILEGROUP [' + @previous2YearFileGroupName
  86:         + ']'
  87:    
  88:     EXEC sp_executesql @sqlPrevious2
  89: END
  90:  
  91: DECLARE @fileName  VARCHAR(500)
  92: DECLARE @previousYearFileName VARCHAR(500)
  93: DECLARE @previous2YearFileName VARCHAR(500)
  94: DECLARE @sqlFile   NVARCHAR(4000)
  95: DECLARE @sqlFilePrevious   NVARCHAR(4000)
  96: DECLARE @sqlFilePrevious2   NVARCHAR(4000)
  97: DECLARE @dataFileLocation VARCHAR(1000)
  98:  
  99:  
 100: SET @dataFileLocation=@tableFilePath
 101:  
 102: SET @fileName =
 103:     @dataFileLocation +
 104:     @fileGroupName + '_Partition.mdf'
 105: SET @previousYearFileName =
 106:     @dataFileLocation +
 107:     @previousYearFileGroupName + '_Partition.mdf'
 108:    
 109: SET @previous2YearFileName =
 110:     @dataFileLocation +
 111:     @previous2YearFileGroupName + '_Partition.mdf'
 112:  
 113: IF NOT EXISTS(
 114:        SELECT physical_name
 115:        FROM   sys.database_files
 116:        WHERE  physical_name = @fileName
 117:    )
 118: BEGIN
 119:     SET @sqlFile = 'ALTER DATABASE '+@databaseName+
 120: ' ADD FILE
 121: ( NAME = ' + @fileGroupName+ '_Partition' + ',
 122: FILENAME = [' + @fileName +
 123:         '],
 124: SIZE = 1MB,
 125: MAXSIZE = UNLIMITED,
 126: FILEGROWTH = 1MB)
 127: TO FILEGROUP [' + @fileGroupName
 128:         + ']'
 129:    
 130:     EXEC sp_executesql @sqlFile
 131: END
 132:  
 133:  
 134: IF NOT EXISTS(
 135:        SELECT physical_name
 136:        FROM   sys.database_files
 137:        WHERE  physical_name = @previousYearFileName
 138:    )
 139: BEGIN
 140:     SET @sqlFilePrevious = 'ALTER DATABASE '+@databaseName+
 141: ' ADD FILE
 142: ( NAME = ' + @previousYearFileGroupName + '_Partition'+ ',
 143: FILENAME = [' + @previousYearFileName +
 144:         '],
 145: SIZE = 1MB,
 146: MAXSIZE = UNLIMITED,
 147: FILEGROWTH = 1MB)
 148: TO FILEGROUP [' + @previousYearFileGroupName
 149:         + ']'
 150:    
 151:     EXEC sp_executesql @sqlFilePrevious
 152: END
 153:  
 154: IF NOT EXISTS(
 155:        SELECT physical_name
 156:        FROM   sys.database_files
 157:        WHERE  physical_name = @previous2YearFileName
 158:    )
 159: BEGIN
 160:     SET @sqlFilePrevious2 = 'ALTER DATABASE '+@databaseName+
 161: ' ADD FILE
 162: ( NAME = ' + @previous2YearFileGroupName+ '_Partition' + ',
 163: FILENAME = [' + @previous2YearFileName +
 164:         '],
 165: SIZE = 1MB,
 166: MAXSIZE = UNLIMITED,
 167: FILEGROWTH = 1MB)
 168: TO FILEGROUP [' + @previous2YearFileGroupName
 169:         + ']'
 170:    
 171:     EXEC sp_executesql @sqlFilePrevious2
 172: END
 173:  
 174:  
 175: DECLARE @minDateKey                       INT
 176: DECLARE @minDate                          DATETIME
 177: DECLARE @MinDateMonth                     VARCHAR(6)
 178: DECLARE @currentPartitionMonthDate        DATETIME
 179: DECLARE @currentPartitionMonthDateVale    VARCHAR(6)
 180: --DECLARE @previousPartitionMonthDate       DATETIME
 181: --DECLARE @previousPartitionMonthDateValue  VARCHAR(6)
 182: DECLARE @currentMonthPartitionName        VARCHAR(100)
 183: DECLARE @previousMonthPartitionName       VARCHAR(100)
 184:  
 185: --DECLARE @minRangeDateKey            INT
 186: --DECLARE @maxRangeDateKey            INT
 187: DECLARE @MonthKey                         INT
 188:  
 189:  
 190:  
 191: --SELECT @minDateKey=MIN(DateKey) FROM FactMain fm
 192: DECLARE @sqlMinDate                       NVARCHAR(500)
 193: SET @sqlMinDate = 'SELECT @Result = ISNULL(MIN('+@partitionKey+'),20120101) FROM [' + @tableName + ']'
 194: EXEC sp_executesql @sqlMinDate,
 195:      N'@Result int out',
 196:      @minDateKey OUT
 197:  
 198: --SELECT @minDate = FullDate
 199: --FROM   DimDate dd
 200: --WHERE  dd.DateKey = @minDateKey
 201:  
 202: IF(@partitionGranularity='D')
 203: BEGIN
 204: SELECT @currentPartitionMonthDate = DATEADD(MONTH, -2, @dateToday)
 205: END
 206:  
 207:  
 208: IF(@partitionGranularity='M')
 209: BEGIN
 210: SELECT @currentPartitionMonthDate = @dateToday
 211: END
 212: --SELECT @previousPartitionMonthDate = DATEADD(MONTH, -3, @dateToday)
 213:  
 214:  
 215:  
 216: SET @currentPartitionMonthDateVale = CAST(YEAR(@currentPartitionMonthDate) AS VARCHAR)
 217:     +
 218:     RIGHT(
 219:         '0' + CAST(MONTH(@currentPartitionMonthDate) AS VARCHAR),
 220:         2
 221:     )
 222:    
 223: --SET @previousPartitionMonthDateValue = CAST(YEAR(@previousPartitionMonthDate) AS VARCHAR)
 224: --    +
 225: --    RIGHT(
 226: --        '0' + CAST(MONTH(@previousPartitionMonthDate) AS VARCHAR),
 227: --        2
 228: --    )
 229:  
 230: DECLARE @maxPartitionMonthDate DATETIME
 231: SET @maxPartitionMonthDate = DATEADD(MONTH, 1, @currentPartitionMonthDate)
 232: DECLARE @MaxPartitionYearMonthDateValue      DATETIME
 233: SET @MaxPartitionYearMonthDateValue= CAST(YEAR(@maxPartitionMonthDate) AS VARCHAR)
 234:         +
 235:         RIGHT('0' + CAST(MONTH(@maxPartitionMonthDate) AS VARCHAR), 2) + '01'
 236:    
 237: DECLARE @currentPartitionYearMonthDateValue  DATETIME
 238: SET @currentPartitionYearMonthDateValue= CAST(YEAR(@currentPartitionMonthDate) AS VARCHAR)
 239:         +
 240:         RIGHT(
 241:             '0' + CAST(MONTH(@currentPartitionMonthDate) AS VARCHAR),
 242:             2
 243:         ) + '01'
 244:  
 245: SET @MinDateMonth = SUBSTRING(CAST(@minDateKey AS VARCHAR(8)), 1, 6)
 246: --SET @currentMonthPartitionName = @tableName + '_' + @currentPartitionMonthDateVale
 247: --SET @previousMonthPartitionName = @tableName + '_' + @previousPartitionMonthDateValue
 248:  
 249:  
 250: DECLARE @DatePartitionFunction  NVARCHAR(MAX)
 251: DECLARE @i                      DATETIME
 252: DECLARE @DatePartitionScheme    NVARCHAR(MAX)
 253: DECLARE @CreatePartitionIndex NVARCHAR(MAX)
 254: DECLARE @indexName VARCHAR(500)
 255:  
 256:  
 257: --Create / Alter Partition Function
 258:  
 259: IF EXISTS (
 260:        SELECT *
 261:        FROM   sys.partition_functions
 262:        WHERE  NAME = @partitionFunctionName
 263:    )
 264: BEGIN
 265:  
 266:   ---- Start Merge Daily Partitions to Month Partitions
 267:   DECLARE @maxRangeValue VARCHAR(8)
 268:   SELECT @maxRangeValue=CAST(MAX(VALUE) AS VARCHAR(8)) FROM sys.partition_range_values v
 269:   INNER JOIN sys.partition_functions f ON v.function_id=f.function_id
 270:   WHERE f.[name]=@partitionFunctionName
 271:   
 272:   DECLARE @maxRangeDateValue DATETIME
 273:   SET @maxRangeDateValue=@maxRangeValue
 274:   
 275:  
 276:    
 277:   IF(DATEDIFF(MONTH,@maxRangeDateValue,@dateToday)>0)
 278:   BEGIN
 279:   
 280:   SET @i = @currentPartitionYearMonthDateValue;
 281:   DECLARE @j DATETIME=SUBSTRING(CONVERT(VARCHAR(8),DATEADD(MONTH,-1,@currentPartitionYearMonthDateValue),112),1,6)+'02'
 282:   
 283:   WHILE (@j<=@i)
 284:   BEGIN
 285:    
 286:   IF EXISTS(SELECT VALUE FROM sys.partition_range_values v
 287:   INNER JOIN sys.partition_functions f ON v.function_id=f.function_id
 288:   WHERE f.[name]=@partitionFunctionName AND CAST(v.[value] AS VARCHAR(8))=CONVERT(VARCHAR(8),@j,112))
 289:   BEGIN
 290:    
 291:    SET @DatePartitionFunction = N'ALTER PARTITION FUNCTION '+@partitionFunctionName
 292:             + ' () MERGE RANGE ('+CONVERT(VARCHAR(8),@j,112)+')';
 293:  
 294:         EXEC sp_executesql @DatePartitionFunction;
 295:  
 296:    END
 297:   SET @j = DATEADD(DAY, 1, @j);
 298:   END
 299:   END
 300:   
 301:   --- Merge Monthly partitions
 302:   DECLARE @minimumMonthlyPartitionValue DATETIME
 303:   DECLARE @minimumPartitionValue DATETIME
 304:   
 305:   SET @minimumMonthlyPartitionValue=SUBSTRING(CONVERT(varchar(8),DATEADD(YEAR,-1,@dateToday),112),1,4)+'0101'
 306:   SELECT @minimumPartitionValue=CAST(MIN(VALUE) AS VARCHAR(8)) FROM sys.partition_range_values v
 307:   INNER JOIN sys.partition_functions f ON v.function_id=f.function_id
 308:   WHERE f.[name]=@partitionFunctionName
 309:   
 310:   DECLARE @m DATETIME
 311:   SET @m=DATEADD(MONTH,1,@minimumPartitionValue)
 312:   
 313:   WHILE (@m<@minimumMonthlyPartitionValue)
 314:   BEGIN
 315:    IF EXISTS(SELECT VALUE FROM sys.partition_range_values v
 316:     INNER JOIN sys.partition_functions f ON v.function_id=f.function_id
 317:     WHERE f.[name]=@partitionFunctionName AND CAST(v.[value] AS VARCHAR(8))=CONVERT(VARCHAR(8),@m,112))
 318:    BEGIN
 319:     SET @DatePartitionFunction = N'ALTER PARTITION FUNCTION '+@partitionFunctionName
 320:      + ' () MERGE RANGE ('+CONVERT(VARCHAR(8),@m,112)+')';
 321:  
 322:      EXEC sp_executesql @DatePartitionFunction;
 323:  
 324:     END
 325:   SET @m = DATEADD(MONTH, 1, @m);
 326:  
 327:    END
 328:  
 329:    SET @indexName='IX_'+@tableName+'_'+@partitionKey
 330:    
 331:   SET @CreatePartitionIndex='IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name ='''+@indexName+''')'+
 332:   'CREATE CLUSTERED INDEX '+@indexName+' ON '+@tableName+' ('+@partitionKey+') ON '+@partitionSchemeName+ ' ('+@partitionKey+')'
 333:    
 334:   EXEC sp_executesql @CreatePartitionIndex
 335:     END
 336: ELSE
 337: BEGIN
 338:  ---- Create Partition Function and Partition Scheme
 339:     SET @DatePartitionFunction = N'CREATE PARTITION FUNCTION '+@partitionFunctionName
 340:         + ' (int) AS RANGE RIGHT FOR VALUES (';
 341:    
 342:     SET @DatePartitionScheme = N'CREATE PARTITION SCHEME '+@partitionSchemeName
 343:         + ' AS PARTITION '+@partitionFunctionName-- + @tableName
 344:         + ' TO (';
 345:     SET @i = CAST((SUBSTRING(CAST(@minDateKey AS VARCHAR(8)), 1, 6) + '01') AS DATETIME);
 346:     WHILE @i < @MaxPartitionYearMonthDateValue
 347:     BEGIN
 348:         SET @DatePartitionFunction += '''' + CONVERT(VARCHAR(8),@i,112) + ''''
 349:             + N', ';
 350:         SET @DatePartitionScheme += '''' + @databaseName + '_' + CAST(YEAR(@i) AS VARCHAR(4))
 351:             + '''' + N', ';
 352:         SET @i = DATEADD(MM, 1, @i);
 353:     END
 354:     SET @DatePartitionFunction += '''' + CONVERT(VARCHAR(8),@i,112) + '''' +
 355:         N');';
 356:     SET @DatePartitionScheme += '''' + @databaseName + '_' + CAST(YEAR(@i) AS VARCHAR(4))
 357:   + '''' + N', '+
 358:   '''' +'PRIMARY'
 359:             + '''' + N');';
 360:         --+ '''' + N');';
 361:     EXEC sp_executesql @DatePartitionFunction;
 362:     EXEC sp_executesql @DatePartitionScheme;
 363:    
 364:    
 365:     SET @indexName='IX_'+@tableName+'_'+@partitionKey
 366:    
 367:     SET @CreatePartitionIndex='IF  EXISTS (SELECT name FROM sys.indexes WHERE name ='''+@indexName+''')'+
 368: ' DROP INDEX '+@indexName+' ON '+@tableName+' WITH (ONLINE = ON, MAXDOP = 2) '+
 369:     'CREATE CLUSTERED INDEX '+@indexName+' ON '+@tableName+' ('+@partitionKey+') ON '+@partitionSchemeName+ ' ('+@partitionKey+')'
 370:    
 371:     EXEC sp_executesql @CreatePartitionIndex
 372: END
 373:  
 374:  
 375: --Create Daily partitions
 376: IF(@partitionGranularity='D')
 377: BEGIN
 378:  
 379: DECLARE @k DATETIME
 380: SET @k=@datePartitionStartDate
 381:  
 382: WHILE @k<=@datePartitionEndDate
 383: BEGIN
 384:  
 385:  IF NOT EXISTS(SELECT VALUE FROM sys.partition_range_values v
 386:   INNER JOIN sys.partition_functions f ON v.function_id=f.function_id
 387:   WHERE f.[name]=@partitionFunctionName AND CAST(v.[value] AS VARCHAR(8))=CONVERT(VARCHAR(8),@k,112))
 388:   
 389:   BEGIN
 390:     SET @DatePartitionScheme = N'ALTER PARTITION SCHEME '+@partitionSchemeName
 391:             + ' NEXT USED '+@databaseName + '_' + CAST(YEAR(@k) AS VARCHAR(4)) ;
 392:        
 393:    
 394:    SET @DatePartitionFunction = N'ALTER PARTITION FUNCTION '+@partitionFunctionName
 395:             + ' () SPLIT RANGE ('+CONVERT(VARCHAR(8),@k,112)+')';
 396:        
 397:      
 398:         EXEC sp_executesql @DatePartitionScheme;
 399:         EXEC sp_executesql @DatePartitionFunction;
 400:        
 401:   END
 402:   SET @k = DATEADD(DAY, 1, @k);
 403: END
 404: END
 405: END
 406: GO

 

Hope this will be helpful.

No comments:

Post a Comment