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 [' + @fileGroupName128: + ']'
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 [' + @previousYearFileGroupName149: + ']'
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 [' + @previous2YearFileGroupName169: + ']'
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: 2243: ) + '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