Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Partitioning in SQL Server 2008 Expand / Collapse
Author
Message
Posted Saturday, May 1, 2010 11:30 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:10 PM
Points: 49, Visits: 279
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
Post #914257
Posted Tuesday, September 7, 2010 4:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 27, 2012 6:57 AM
Points: 1, 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
Post #981971
Posted Thursday, September 30, 2010 9:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 28, 2014 5:24 AM
Points: 1, 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.
Post #996396
Posted Friday, October 1, 2010 3:16 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:47 AM
Points: 562, Visits: 1,035
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!
Post #996517
Posted Friday, October 1, 2010 3:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 424, Visits: 238
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.
Post #996522
Posted Friday, October 1, 2010 6:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 11:44 AM
Points: 69, 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.
Post #996593
Posted Friday, October 1, 2010 8:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 368, Visits: 1,956
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/
Post #996696
Posted Friday, October 1, 2010 8:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:39 AM
Points: 49, Visits: 265
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.
Post #996714
Posted Friday, October 1, 2010 9:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 424, Visits: 238
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.
Post #996764
Posted Friday, October 1, 2010 9:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:39 AM
Points: 49, Visits: 265
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.
Post #996767
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse