Table partitioned on date

Question

Table partitioned on date

sqlqueue

SSC Eights!

Points: 922
More actions
July 20, 2009 at 11:48 pm

#207197

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..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

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

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 2

use http://msdn.microsoft.com/en-us/library/aa964122(SQL.90).aspx for sliding window partitioning.

sturner SSC-Insane Points: 22411 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lempster SSCoach Points: 15651 More actions · Answer 5

There's an article in today's SQLServerCentral.com about horizontal partitioning:

http://www.mssqltips.com/tip.asp?tip=1796&home