To partition or not

Hello guys,

What is your opinion on the following:

1) I have a rather large table (for my definition of large). Approx 5 million rows
2) There is one RAID - 10 disk where the database with the table is located (also log file and temp db is there)
3) My table is both OLTP and also a reporting table. Access patterns vary but in most cases records are inserted on a monotonically increasing manner (clustered index is a datetime field), and select queries usually ask for the later two or three months (think of it as a typical sales table). This is not always the case. There are some consolidation queries (not many) during the day that may ask for arbitrary periods of data
5) Assuming I CANNOT add another disk and also I cannot create an archiving table with my historical data

Do you think that there will be any performance gains (not administration gains) if I partition the table (say per month - I have three years worth of data)?

Thanx for your advicde

KKT

There is no choice of adding additional disk then you want to create partition on the same disk.
I don't see any benifit using the table partitioning... on top of that you don't want to archive it... Partional will help in archiving too...

In my view 5 mill. row table is small table as long as it is BLOB free table.
What is the table size in MB?



Mohammed U.

quote:Originally posted by MohammedU

There is no choice of adding additional disk then you want to create partition on the same disk.

[KKT]Exactly.

I don't see any benifit using the table partitioning... on top of that you don't want to archive it... Partional will help in archiving too...

[KKT] Well this is the point of the question. I was wondering if partitioning the table may divert queries to smaller chunks of data that will be read faster. Archiving is not an option as I have lots of queries that will know be diverted to a new table (the archiving table). This is a lot of code that I have to change. I do not understand the concept of partitioning very well yet. I've only recently started investigating. Having a different disks for partitions is an obvious gain in parallelism, but my customer does want to buy disks (you've heard this before). So, I was wondering if I will have any benefits anyway.

In my view 5 mill. row table is small table as long as it is BLOB free table.
What is the table size in MB?

[KKT] Table size is about 3GB. And it is BLOB free. Mostly decimals, nvarchars, integers and some uniqueidentifiers. PK is datetime



Mohammed U.

KKT