I read your article(SQL Server partitioning: not the answer to everything) and being amazing of use partitioning for my case or not I must to store about 1000 record per a second this data is about location of mobile nodes, these data make my database too huge do you think i must partitioning my database or not(I have so much reporting in future).
A:
Hi,
That's a lot of data.
What is the lifecycle of the data i.e. do you only need to store the records for a finite amount of time? For example after a month, perhaps certain data can be archived off or moved to a Data warehouse?
Given the volume of data that you intend to work with you are probably going to want to use an architecture that scales easily? For this reason you may want to look at using Cloud type services such as Amazon Ec2, or SQL Data Services on the Azure Platform.
http://www.microsoft.com/azure/data.mspx
Perhaps if you provide more specific details about what it is you are actually looking to do i.e. what business process you are looking to support, we may be able to provide more specific assistance.
Without such details it is not possible to ascertain whether or not SQL Server Partitioning would be an appropriate design approach for you.
John Sansom
2009-02-22 15:26:21
+1
A:
1000 a second isn't that much.
- Is it every second of 24/7?
- In a defined window?
- Is it a peak of 1000 per second but usually less?
We have a recent system growing at 20 million rows/month (after tidy ups of say another 50-80 million) and we're not thinking of anything like partitioning.
gbn
2009-02-22 16:20:44
Only if it 1000 every second. At that level, surely they'd have MS in to design their system instead of asking on a public forum... Random thought: they'll have no maintenance window, ever. Oh what fun.
gbn
2009-02-22 17:00:01
I disagree. In my opinion 86 million records (1000 X 60 X 60 X 24) a day is quite a significant number of records. Particularly given the post indicates the use of spatial data types. A platform that is easily scalable seems to be required, SQL Server may not be the appropriate choice here.
John Sansom
2009-02-22 17:03:25
Also OP may be assuming partitioning for reporting offload?
gbn
2009-02-22 17:13:16
A:
You might need to look at a different RDMS. I would take a look at Vertica.
Richard West
2009-05-09 22:48:00
A:
Presuming the table in question is indexed, then one of two options is certainly warranted when any of the indexes outgrow the available RAM. Not surprisingly, one of them is, increase RAM. The other of course is vertical partitioning.
gbn's answer provides some good things to consider which you have not mentioned, such as how many records per month (or week, or day) are being added. Richard's comment as to how big the (average) record is is also significant, particularly in terms of how big the average records for the indexes are, presuming the indexes do not include all the fields from the table.
gbn's answer however also seems a bit reckless to me. Growing at 20 million rows per month and not even "thinking of anything like partitioning". Without sufficient metrics as alluded to above, this is a possible recipe for disaster. You should at least be thinking about it, even it just to determine how long you can sustain your current and/or expected rate of growth, before needing to consider more RAM or partitioning.
George Jempty
2009-05-09 23:16:41
We'll think about it when we need to: we've removed some indexes, narrowed some data types and dropped an unused column. At this point, we don't need partitioning. We also don't have definitive answer about how long to retain data: we may simply move older data into a separate table.
gbn
2009-05-10 05:41:11
My point is though, that you actually ARE thinking about partitioning, or at least the issues that could lead up to that possibility. One could also argue that moving older data into a separate table is a form of partitioning, bearing in mind, I am answering this in a more general context than "SQL Server" partitioning.
George Jempty
2009-05-10 11:48:25