July 14, 2016 at 5:14 am
Hi
Before asking the question let me tell I had no previous experience in partition.
I have 1 table whose size is 1.5 TB, currently the table has Yearly partition from 2013 to 2016.But still there is lot of issues as the performance is not up to the level. The client told us to maintain only 13 months of data, we are planning to implement monthly partition.
We are planning to convert the yearly partition to monthly partition by creating a new table as explained below.
•Create Filegroups, Datafiles, Partition Function and Partition Scheme.
•Create a new table on the new partition scheme with Clustered index.
•Create Non Clustered indexes and apply foreign key constraints.
•Copy records from old table to new table.
•Rename old table as tablename_old and new table to actual tablename.
•Truncate and drop old table.
•Drop yearly Partition function and scheme.
My doubt is for monthly partition how much filegroups i had to create ,do i need to create 13 filegroups
as i read this text from this http://www.databasejournal.com/features/mssql/partitioning-in-sql-server-part-2.html
The Partition Scheme maps the partitions of the partitioned table or index to particular file groups (basically the physical locations). As I said above, N number of boundary values creates N+1 partitions and hence you also need to specify N+1 file groups with partition scheme definition, one for each partition. You can specify the same file groups (using the ALL keyword or specifying file groups multiple times) for all the partitions or you can have different file groups, one for each partition or combination of both.
As i told at any time i have to maintain only 13 months of data for example
consider the table had data from January 2015 to January 2016 , so the table has 13 months of data now i am going to load the February 2016 data into the table so that means 14th month. I have few questions here
1) loading the February 2016 data do i have to create a new partition.
2) I have to delete the data of January 2015 to maintain 13th month business requirement, after deleting do i have to delete the January partition or it can be reused.
Thanks
July 14, 2016 at 7:43 am
1) Going to be blunt here, sorry: You should absolutely not be responsible for this evolution. Partitioning is a VERY COMPLEX SUBSYSTEM, with LOTS of caveats, limitations, gotchas, provisos, etc. I have come across more than a few DISASTROUS attempts at this at clients (and on forums) over the years. It also needs to be VERY WELL THOUGHT OUT, implemented and maintained.
2) Oh, and partitioning was NOT DESIGNED NOR BUILT BY MICROSOFT TO IMPROVE PERFORMANCE!!!! It is to facilitate VLDB data management, loading and maintenance. Forums are littered with those that complain that implementing partitioning didn't magically make their EDW queries run faster.
3) So if performance isn't acceptable then you have a tuning exercise.
4) I STRONGLY recommend you get a qualified professional on board to do step 3 FIRST, then evaluate if any form of partitioning is necessary at all. There are a number of tuning pros here on SSC.com.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 14, 2016 at 1:51 pm
Google "partition sliding window" and you will find answers for all your question 🙂
July 14, 2016 at 11:45 pm
Thanks for the response...let me search with the partition sliding
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply