January 9, 2014 at 10:47 am
Hello,
I am new to SQL Server table partitioning. I have a large table, 800 million rows. I have a purge process but the inserts per minute are faster than the deletes per minute. So the purge job continuously runs and the table continuously grows. The requirement is to keep 21 days of data.
A developer recommended partitioning the table, 31 partitions, one for each day of the month. A daily "purge" job would "drop" the partitions for days > 21 days ago and "create" a partition for tomorrow's date.
Sounded like a good idea but I don't think the SQL Server partition function and partition scheme design supports this dropping and creating partitions. Is that true? Any suggestions? thanks
January 9, 2014 at 11:12 am
Partitioning would work great for your case, the only issue I see is the administration of having to prepare all of those partitions for every day. What your developer is talking about is the ability to switch out partitions and merge the partitions (get rid of old partitions). It is very quick (meta data operation only if you setup partition right). What I would suggest is that you read up on partitioning and find out if it is going to be right for you and then setup a test area to practice it. I have linked to Kendra Little's article on partitioning as a starting point for you:
http://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/
January 9, 2014 at 1:45 pm
Thanks Keith. I watched Kendra's videos. Good stuff. I will work on implementing this solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply