July 20, 2009 at 11:48 pm
My table is growing and growing and it is simply inserting the rows daily from monitoring systmes.
I am thinking to go ahead for partitioning on the basis of date.
couple of questions
1) how i can partition my existing table?
2) what is better approach in my case as if i partition with 'date' so it will be kind of archiving soln and table will again grow down the line as data will keep on inserting on one of the current date partition.
any insight is appreciated..
July 21, 2009 at 4:17 am
Look up 'partitioning' in BOL or on the Microsoft Technet site. I suggest you need to implement what is known as a 'sliding window' type of partitioning whereby data to be archived is 'switched' out of a partition to a separate (often slower) storage device which may then be marked as read-only. A new partiton is then created to hold new (most current) data and the partitions are 'shuffled along'..hence the term 'sliding window'.
You'll need to create a partition scheme and partition function, but as I say, there is a tremendous amount of information available which you should research to find a solution that best suits your needs.
HTH
Lempster
July 21, 2009 at 8:27 am
use http://msdn.microsoft.com/en-us/library/aa964122(SQL.90).aspx for sliding window partitioning.
July 21, 2009 at 8:50 am
before you move to a partitioning solution you should look at the table design and the reasons why the table is getting so big or your performance is getting poor. How big is this table (in rows and size?)? "BIG" to some people is not really that big to someone else.
For example, a logging table of this type should to have a clustered index on that date column. Most of the time logging type table's don't need a primary key, unless individual rows are going to updated a lot. Most of the time log tables are simply used in queries and so the clustered index on the date column will minimize fragmentation and provide for fast means of aging off rows older than some date, or moving them to an archive table. The archive table could be on a different file group and physical drive.
The probability of survival is inversely proportional to the angle of arrival.
July 21, 2009 at 6:20 pm
sturner (7/21/2009)
before you move to a partitioning solution you should look at the table design and the reasons why the table is getting so big or your performance is getting poor. How big is this table (in rows and size?)? "BIG" to some people is not really that big to someone else.
That's good advice especially about the word "BIG". I generally won't promote code unless it has been tested with a million row test table or two just for starters.
--Jeff Moden
July 23, 2009 at 4:26 am
There's an article in today's SQLServerCentral.com about horizontal partitioning:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply