Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning in SQL Server 2008


Partitioning in SQL Server 2008

Author
Message
Philippe Cand
Philippe Cand
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 294
We have a debate about partitioned tables vs the old partitioned view in SQL2008. I have a troublesome table with over 30M rows. I thought that horizontal partitioning with aligned indexes and aligned cubes partitions would significantly increase performance since most queries touch 1 partition and SQL2008 allocate many threads to these queries as opposed to SQL2005 which allocate only one thread. A Colleague of mine bought the SQL Server 2008 Bible and there is a call-out that says that table partitioning will HURT performance unless your table is a real monster table with billions of rows. As a consequence this colleague advise against table partitioning and instead advise to use the old technique of breaking down the table in multiple individual tables, then create a partitioned view on the top of this. I am a little confused since I cannot find any other advise against table partitioning in SQL2008. What is your take? Thanks.

BI Guy
sandeepyal
sandeepyal
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 33
Hi

when i am making a partition of a table base on date time. i am geting result like this



STARTDATE ENDDATE
PARTITION 1 = NULL NULL
PARTITION 2 =2010-03-02 00:02:22.000 2010-03-31 23:59:33.000
PARTITION 3 =2010-04-01 00:01:06.000 2010-06-30 23:59:22.000
PARTITION 4 =2010-07-01 00:00:40.000 2010-09-08 03:25:23.000

SO CAN U PLEASE HELP ME I WANT THE RESULT LIKE THIS:

STARTDATE ENDDATE
PARTITION 1 =2010-03-02 00:02:22.000 2010-03-31 23:59:33.000
PARTITION 2 =2010-04-01 00:01:06.000 2010-06-30 23:59:22.000
PARTITION 3 =2010-07-01 00:00:40.000 2010-09-08 03:25:23.000
PARTITION 4 = NULL NULL
szmulder
szmulder
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 67
Only enterprise version support this function, too expensive!
I think most company only buy standard version that's why most people never have chance to use it.
grahamc
grahamc
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 1039
szmulder (9/30/2010)
Only enterprise version support this function, too expensive!
I think most company only buy standard version that's why most people never have chance to use it.


I think you'd be wrong on that one. All the companies I have worked for (4 in the last 5 years) have had multiple instances of Enterprise Edition across different versions of SQL Server.

Good article, about to go into this in more detail over the next couple of days (looking at implementing on one of our PROD systems), so should be fun!
Brian Jones-401554
Brian Jones-401554
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 289
You can achieve a similar result using Standard and views should your budget not stretch to Enterprise. Really it's horses for courses, if you databases are large enough to warrant partitioning, then the other features that come with Enterprise (for instance compression, resource governing, rebuild indexes online) are probably essential too. I phased Enterprise out here due to cost/features benefit was not stacking up (databases are typically small, largest is less than 100GB). However, now need to deal with some big data (about 30 million rows per week) and as such to be assured that we can provide the data services required, Enterprise had to be figured into the budget.
VinceV
VinceV
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 136
Someone mentioned that the author probably should have broken the article into a series. I agree. It would certainly make the material more digestable (if that's a word). However, it could ultimately be down to personal preference and learning style, so this is not to criticize the author. Just my $0.02. Good intro to the feature and it's good to know it's available. Might need it one day.
Solomon Rutzky
Solomon Rutzky
Say Hey Kid
Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)

Group: General Forum Members
Points: 662 Visits: 2937
Philippe Cand (5/1/2010)
We have a debate about partitioned tables vs the old partitioned view in SQL2008. I have a troublesome table with over 30M rows. I thought that horizontal partitioning with aligned indexes and aligned cubes partitions would significantly increase performance since most queries touch 1 partition and SQL2008 allocate many threads to these queries as opposed to SQL2005 which allocate only one thread. A Colleague of mine bought the SQL Server 2008 Bible and there is a call-out that says that table partitioning will HURT performance unless your table is a real monster table with billions of rows.


Hello. In my experience from using SQL Server 2005 Partitioning on decently-sized tables I can offer two insights:

1) 30M rows is generally NOT a large table. If you are having performance problems you first need to look into the data model (maybe it can be improved by breaking the table into two one-to-one tables, etc.) and index structure (and don't forget about index rebuilds / reorgs).

2) The book is correct as far as I have seen. We have implemented Partitioning and I have tested a 125 Million row table that we partitioned and made a copy that was not partitioned and the performance was about the same between the two. Although to be fair, I believe I only tested queries that hit the PK.

I believe Microsoft has also stated that Partitioning is really only meant to increase performance of large data loads, such as in a Data Warehouse and was not meant to increase query performance (even though intuition says that it would). I am going to forward this thread to someone who might be able to give more details.

Take care,
Solomon...

SQL# - http://www.SQLsharp.com/
chapman.tim
chapman.tim
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 332
Solomon is correct. Partitioning is NOT intended to increase query performance, it is intended to increase data manageability and administration. SQL 2008 did some things to make better parallel plans against partitioned tables, but it still doesn't really do much in terms of helping query performance. Often, if misused, it can hinder performance significantly.
Brian Jones-401554
Brian Jones-401554
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 289
chapman.tim (10/1/2010)
Solomon is correct. Partitioning is NOT intended to increase query performance, it is intended to increase data manageability and administration. SQL 2008 did some things to make better parallel plans against partitioned tables, but it still doesn't really do much in terms of helping query performance. Often, if misused, it can hinder performance significantly.


Agreed, and if your database is of such a size that you can not reasonably do a full backup, then filegroups and data partitioning is the way to go.

However, I think it will also give you performance gains as a bonus. If you partition your data sensibly, then SQL will not have to read through millions and millions of rows to satisfy the criteria, just through the partitions that are required. That's got to give a performance gain.
chapman.tim
chapman.tim
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 332
Its actually not as much of a benefit as you'd expect. You'd always have to include your partitioning key in your predicate so that SQL is able to identify the partition directly. From there, it STILL only performs as well as a non-partitioned table. Does not perform better. No benefit in terms of reads as one would expect.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search