Partitioning in SQL Server 2005/2008
We have partitioned 5 large tables across 10 partitions (with 10 filegroups) and with a set of 5 - 6 users the performance was adequate, with minimal conflicts (like deadlocks etc), but we want to implement this scheme that can scale to say 300 users. With regard to this I have the following questions:
- What is the maximum number of partitions that works optimally? In other words, even though SQL Server allows 1000 partitions per table, it would become an administrative nightmare were we to deploy such a scheme. - **Degradation Concerns**: What would be the maximum (but practical) number of partitions that one can deploy before one has to worry about degradation in performance and concurrency? - If the number of users access the same partition, what would be the negative impact on the overall scheme (in terms, again of performance)? - Does a multi-CPU environment enhance the overall performace? Of course, we can suggest to the client to use RAID based storage with data striped across multiple drives? - Any other aspects related to partitioning that can help me for implementation?
Please come back with your suggestions or questions and I shall try to provide more information if needed. While I can find several articles of partitioning (even from Microsoft MVPs), I would like answers in particular, to address my concerns expressed above. Thanks in advance...
Software/Hardware used:
SQL Server 2008, Windows Server 2008
- What is the maximum number of partitions that works optimally? In other words, even though SQL Server allows 1000 partitions per table, it would become an administrative nightmare were we to deploy such a scheme. - **Degradation Concerns**: What would be the maximum (but practical) number of partitions that one can deploy before one has to worry about degradation in performance and concurrency? - If the number of users access the same partition, what would be the negative impact on the overall scheme (in terms, again of performance)? - Does a multi-CPU environment enhance the overall performace? Of course, we can suggest to the client to use RAID based storage with data striped across multiple drives? - Any other aspects related to partitioning that can help me for implementation?
Please come back with your suggestions or questions and I shall try to provide more information if needed. While I can find several articles of partitioning (even from Microsoft MVPs), I would like answers in particular, to address my concerns expressed above. Thanks in advance...
Software/Hardware used:
SQL Server 2008, Windows Server 2008
ASKED:
April 27, 2010 5:19 PM
UPDATED:
April 28, 2010 9:23 PM
Answer Wiki:
Tables that are partitioned (and tables that aren't for that matter) should be able to be accessed by hundreds, thousands or tens of thousands of users at any one time.
How many rows in the table are there?
You'll want as many CPUs are you can put in the machine. You'll also want lots of very fast hard drives.
Typically tables which are partitioned have 1 Billion+ records in there, and you partition so that you can control which data is on the fastest disks, and which data is on the slowest disks.
If you are just trying to put the most possible disks under the table, then put the table into its own file group and create files for that filegroup on lots of different RAID arrays. SQL Server will automatically balance the data across the files in the filegroup.
Before I can give any specific recommendations I'll need MUCH more information about the server in question.
To see all answers submitted to the Answer Wiki: View Answer History.
Mr. Denny,
Thanks for your prompt and wonderful response. To answer your questions:
1. The largest table holds about 50 million rows as of now (but this is only a test db..) In other words it can grow to a billion as you have righty guessed.
2. As of now I have about 10 partitions defined out of which only the first 6 are occupied. The number 10 is not a holy number or restrictive in any sense. I have just chosen as a manageable level.
3. The data density in other 4 tables can vary from 1 – 2 million.
4. Since the tables involved participate in JOIN operations, I have currently used the same partition function for all and the data distribution is based on a ‘derived’ partition_column called partition number. This way the associated tables are in the same partition. Each partition is associated with one filegroup that comprises of many files.
5. I have also aligned the indexes for the tables across the same partitions.
6. As for the available hardware, I need to contact the client as to what they have and whether they can upgrade if needed. I needed a ballpark infrastructure that I can use to recommend. In other words, at this juncture, I cannot state with confidence how powerful their servers are, but given a recommendation about the requirements, I am sure they would comply. I DO know that their servers are multi-CPU.
7. As for the RAID configuration to be suggested, would RAID 1 and RAID 10 serve the purpose?
8. Sorry to repeat myself, but i would greatly appreciate if you can confirm about my fears of concurrency/deadlocks with multiple users accessing the same partition at the same time. By your statement, can I safely assume that if the required hardware is provided and data is distributed across the RAID arrays, we should not face degradation problems?
Does this information suffice or do I need to provide more?
Thanks again for your wonderful help and response.
Venky