{"id":7163,"date":"2016-02-26T08:55:38","date_gmt":"2016-02-26T07:55:38","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/changing-an-existing-partition-configuration-well-not-so-easy\/"},"modified":"2016-02-26T08:55:38","modified_gmt":"2016-02-26T07:55:38","slug":"changing-an-existing-partition-configuration-well-not-so-easy","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/changing-an-existing-partition-configuration-well-not-so-easy\/","title":{"rendered":"Changing an existing partition configuration … Well, not so easy!"},"content":{"rendered":"

This time let\u2019s talk about an interesting customer scenario where table partitioning was implemented on a table with 100 GB of data on SQL Server 2014. Partitioning in this context aimed to save disk space\u00a0 (archive data were compressed)\u00a0, help to reduce maintenance time and consumed resources as well (by using index and statistic maintenance operations on the\u00a0active partition).\u00a0 Finally, it will help to improve the queries performance on the concerned table that mainly focused on only the recent customer commands. Let’s say that it exists a few reporting queries that\u00a0 are designed to get older data for analysis. So the partition strategy was pretty simple: my customer decided to archive and compress data older than the year 2015 and leave recent records (since 2015) in the active partition. We implemented partitionning respectively only on the two filegroups ARCHIVE and PRIMARY because this implementation was supposed to be temporary and the estimated data growth small and steady. This first implementation was successful in terms of performance and manageability<\/p>\n

But of course this world is not perfect and the estimated size for the last year was higher than expected and my customer also wanted to archive data from last year in order to guarantee the application performance. In some ways, this is a good news because it means that we may guess that the business activity was better than expected but this is purely an assumption from my part \ud83d\ude42<\/p>\n

So let\u2019s go back to my customer need and in order to meet this new requirement, we have to deal with the existing partition strategy and this is not as simple as we may imagine because changing the existing partition boundaries may be an intensive resource and locking operation. In addition, my customer wants this operation to be as fast as possible to avoid blocking too long\u00a0his business activity. Let’s first to thank him because I know that almost of their requests are a challenging \ud83d\ude42<\/p>\n

Then let\u2019s demonstrate with this similar scenario that I implemented to my lab environment which includes a bigTransactionHistory_stg<\/em> table in the adventureWorks2012 database with the following specification:<\/p>\n

exec sp_spaceused 'dbo.bigtransactionhistory_stg'<\/pre>\n
\"blog<\/a><\/p>\n
exec sp_helpindex 'dbo.bigtransactionhistory_stg'<\/pre>\n
\"blog<\/a><\/p>\n
My scenario does not include a very big table in size (1% of my customer’s data) but this is sufficient to point out the problems of changing an existing partition configuration.<\/p>\n
So let\u2019s begin by setting the scene: my table has a partitioned clustered index with the following information:<\/p>\n
SELECT\n       p.object_id,\n       o.name AS table_name,\n       p.partition_number,\n       p.rows,\n       au.total_pages,\n       au.total_pages \/ 128 AS total_size_mb,\n       au.type_desc,\n\t   p.data_compression_desc,\n       g.name AS [filegroup_name],\n       RVL.value AS left_range_boundary,\n       RVR.value AS right_range_boundary\n       --PF.[name], RV.boundary_id, RV.[value]\nFROM sys.partitions AS p (nolock)\n       LEFT JOIN sys.objects AS o (nolock)\n             ON o.object_id = p.object_id\n       LEFT JOIN sys.indexes i (nolock)\n\t\t\t ON p.object_id = i.object_id\n                    AND p.index_id = i.index_id\n       LEFT JOIN sys.allocation_units AS au (nolock)\n             ON p.hobt_id = au.container_id\n       LEFT JOIN sys.filegroups AS g (nolock)\n             ON g.data_space_id = au.data_space_id\n       LEFT JOIN sys.partition_schemes AS PS (nolock)\n             ON ps.data_space_id = i.data_space_id\n       LEFT JOIN sys.partition_functions AS PF (nolock)\n             ON PF.function_id = ps.function_id             \n       LEFT JOIN sys.partition_range_values AS RVL (nolock)\n             ON RVL.function_id = PF.function_id\n                    AND RVL.boundary_id + 1 = p.partition_number\n       LEFT JOIN sys.partition_range_values AS RVR (nolock)\n             ON RVL.function_id = PF.function_id\n                    AND RVR.boundary_id = p.partition_number\nWHERE p.object_id = object_id('bigtransactionhistory_stg')\n       AND p.index_id = 1\nORDER BY table_name, partition_number\nGO<\/pre>\n
 <\/p>\n
\"blog<\/a><\/p>\n
As expected you may notice a simple partitioning stuff with only two partitions based on right range boundary:<\/p>\n