Description
Horizontal Sharding is used to separate rows between several tables based on applicative logic. Microsoft introduced in MS SQL Server 2005 a build in mechanism named Partitioning to support this need without extra code in the business logic itself. This mechanism enables you deciding in which filegroup each row will be placed supporting regular queries to retrieve and update the data, while boosting performance.
Some syntax and code examples are available if you want to master this feature.
What Can be done with this great feature?
Pros (Why should I use SQL Server Partitioning rather than Horizontal Sharding)
Brent Ozar: "outside of data warehouses, I like to think of partitioning as the nuclear bomb option. When things are going out of control way faster than you can handle with any other strategy, then partitioning works really well. However, it’s expensive to implement (Enterprise Edition plus a SAN) and you don’t want to see it in the hands of people you don’t trust."
Bottom line:
Ask your DBA if they feel safe with this feature, and with your clients DBA. If the answer is no, consider choosing another solution.
I hope now you have all the information to make the decision by yourself. Otherwise, post your comments and we'll be glad to help you,
Keep Performing,
Moshe Kaplan. RockeTier. The Performance Experts.
Horizontal Sharding is used to separate rows between several tables based on applicative logic. Microsoft introduced in MS SQL Server 2005 a build in mechanism named Partitioning to support this need without extra code in the business logic itself. This mechanism enables you deciding in which filegroup each row will be placed supporting regular queries to retrieve and update the data, while boosting performance.
Some syntax and code examples are available if you want to master this feature.
What Can be done with this great feature?
- You can break large tables into smaller chunks which fits the application logic (e.g partioning client's data according to its id or data according to date).
- You can put heavily accessed parts of the table onto fast storage, and less-accessed data onto slower, cheaper storage.
- You can boost the backup time in static date partitioning.
- You can boost many queries including DELETE, SELECT, UPDATE and so on based on right design.
Pros (Why should I use SQL Server Partitioning rather than Horizontal Sharding)
- Horizontal Sharding out of the box.
- A lot of thought and effort were invested in this feature to make it working in just few lines of code
- Relatively new (well not so new since it was presented in SQL Server 2005), and many issues were fixed in SQL Server 2008.
- Requires Enterprise Edition (10X licensing cost relative to standard edition or in other words: $25K per CPU)
- Relatively complex (no support in enterprise manager, and few DBAs will be able to support it) so you probably should master this white paper before taking it into production
- Will bound you to SQL Server (Enterprise Edition).
Brent Ozar: "outside of data warehouses, I like to think of partitioning as the nuclear bomb option. When things are going out of control way faster than you can handle with any other strategy, then partitioning works really well. However, it’s expensive to implement (Enterprise Edition plus a SAN) and you don’t want to see it in the hands of people you don’t trust."
Bottom line:
Ask your DBA if they feel safe with this feature, and with your clients DBA. If the answer is no, consider choosing another solution.
I hope now you have all the information to make the decision by yourself. Otherwise, post your comments and we'll be glad to help you,
Keep Performing,
Moshe Kaplan. RockeTier. The Performance Experts.
No comments:
Post a Comment