question

Sineetha avatar image
Sineetha asked

Data Table Partitioning Pros and cons

could you tell me the pros and cons of Table partition

partitioning
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Matt Whitfield avatar image
Matt Whitfield answered

The Partitioned Tables and Indexes topic on MSDN really does cover it, there's little point in repeating that here.

However, what it is worth saying is that usually partitioning applied in a generic form across a database will harm performance rather than enhance it, and in the situations where it does enhance it, the improvement is usually in terms of maintenance rather than transactional work. A specifically targeted partition scheme may improve performance, but it needs careful consideration and thorough understanding of the usage patterns involved.

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt, what performance hits are introduced? Isn't partitioning designed to focus data access?
0 Likes 0 ·
Have a read of this http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/03/19/partitioning-data-for-query-performance-where-s-the-benefit.aspx - that's a good example, though I've seen it in my own use where partitioning will work well for a small subset of queries, and harm performance on others. I have edited my answer though because it wasn't as clear as I would have liked.
0 Likes 0 ·
It all depends on the use case. If you have a large dataset and the vast majority of your access is going to be filtered by the partitioning key, then partititioning can be useful, otherwise it is probably a net loss.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.