December 17, 2009 at 8:53 am
Can someone tell me if there's any feasibility to doing horizontal partitioning to a table with 9 million plus rows where the server only has access to one drive?
I always thought that horizontal partitioning was best served if you had multiple drives onto which you could split the table. But if there are other benefits to using it where you only have one drive available, I would appreciate hearing it.
December 17, 2009 at 8:59 am
I'd consider the ability to use SWITCH for archiving large amounts of data from one partitioned table to another a great benefit when only having one disk. The IO saved compared to inserts / deletes is great
December 17, 2009 at 9:15 am
SWITCH? Is this something available in SQL 2005?
I don't see it in BOL.
December 17, 2009 at 9:20 am
Yes I think it came in for 2005.
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
December 17, 2009 at 9:57 am
Just to be on the same page.
Is this a Static partition - Creating a table for storing historical records based on a range OR a Partition for managing simultaneous transactions.
Maninder
www.dbanation.com
December 17, 2009 at 11:34 am
OLTP database. The partition would be storing historical records, but apparently the end users like to do a lot of "inception to current" summary reports. (I just found out about this after posting my question).
Most of the detailed reports are for records 3 or 4 years old and we've got at least 10 years worth of records, so I've been asked to partition off the older stuff. However, I'm not sure how helpful it will be. Hence the reason I'm asking.
December 17, 2009 at 11:52 am
Right, so this is the trickier part. you can create another table for horizontal partition. At least your regular transactions(inserts/updates/deletes) wont be hurt, and the reporting can continue off another table or a diff database.
now with the Drive issues, if it is a SINGLE HDD, there is still that issue of performance. If it is more than that and if i guess it right, it should be RAID 5 and one volume, then you should be Good with the partition. Rest it depends on how you want to approach it.. if you are good with a separate table or a altogether a diff database for reporting.
Maninder
www.dbanation.com
December 17, 2009 at 12:18 pm
The "single drive" is actually a SAN. It's either Raid 5 or Raid 1 + 0, but I'm betting on Raid 5.
The plan is to keep everything in the same database for now, just partition things off into separate tables. I'm just uncertain how much benefit there would be. The ability to use T-SQL commands is all well and good, but what performance or data benefits would there be to this scenario?
December 18, 2009 at 10:20 am
Brandie Tarvin (12/17/2009)
The "single drive" is actually a SAN. It's either Raid 5 or Raid 1 + 0, but I'm betting on Raid 5.The plan is to keep everything in the same database for now, just partition things off into separate tables. I'm just uncertain how much benefit there would be. The ability to use T-SQL commands is all well and good, but what performance or data benefits would there be to this scenario?
We have incomplete information to help you here Brandie. The most important is what is the aggregate throughput available from said 'single drive'? If it has 100 spindles that aren't shared with multiple HBAs, FAs, etc and your sever is up to the task you could get spiffy-fast throughput by using multiple files. If it is a 3-drive RAID 5 sharing load with exchange and a bunch of user files then multiple files could actually reduce performance.
Another big question is what is the fileIO stall situation currently? Buffer hit ratio? waitstats issues? Lots to go over to get optimal perf . . .
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 18, 2009 at 10:25 am
Ah, but your response gives me a lot more information than I had before. Now I know what to look for and how to reconsider the partitioning idea.
Thank you.
December 18, 2009 at 10:27 am
One point that warrents mentioning here is that the Query Optimizer is partition aware. If you use a date column as your partitioning key and your queries against that table use the date column to limit the result set, the Query Optimizer is smart enough to limit the query to touch only those partitions that contain the data within the date range specified in the query. Regardless of how many disks comprise the underlying disk-subsystem, there would be a performance benefit. Just to clarify, the partition key must be used in the query for this benefit to be realized.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply