x

Data Table Partitioning Pros and cons

could you tell me the pros and cons of Table partition

more ▼

asked Mar 23, 2010 at 12:47 AM in Default

Sineetha gravatar image

Sineetha
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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.

more ▼

answered Mar 23, 2010 at 04:29 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Matt, what performance hits are introduced? Isn't partitioning designed to focus data access?
Mar 23, 2010 at 09:33 AM Blackhawk-17
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.
Mar 23, 2010 at 10:59 AM Matt Whitfield ♦♦
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.
Mar 23, 2010 at 01:11 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x51

asked: Mar 23, 2010 at 12:47 AM

Seen: 2854 times

Last Updated: Mar 23, 2010 at 06:28 AM