SQL Server Performance Forum – Threads Archive
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 rows2) 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.
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
]]>