April 30, 2009 at 3:08 pm
Great article presented with very easy to follow and implement approach.
Rashid Akhter, PMP
Project Manager, Scrum Master
May 4, 2009 at 8:36 am
Very nice article. Easy to read and very instructive. Thanks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 17, 2009 at 12:41 pm
Do you plan to update your article with the correct t-sql?
September 23, 2009 at 12:06 pm
Does any one know if partitioning on two fields is possible? I guess worst case I'd could combine two fields into one and do it that way but then I have to have all of the TSQL written with the concatenation...pretty ugly. I haven't been able to find anything on this on the web. I'd like to partition a table by geography and year. Thanks for any help.
January 14, 2010 at 3:38 pm
tomtait415 (9/23/2009)
Does any one know if partitioning on two fields is possible? I guess worst case I'd could combine two fields into one and do it that way but then I have to have all of the TSQL written with the concatenation...pretty ugly. I haven't been able to find anything on this on the web. I'd like to partition a table by geography and year. Thanks for any help.
AFAIK the only way to partition on multiple columns is to use a computed column.
May 1, 2010 at 11:30 am
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
September 7, 2010 at 4:26 pm
Hi
when i am making a partition of a table base on date time. i am geting result like this
STARTDATE ENDDATE
PARTITION 1 =NULLNULL
PARTITION 2 =2010-03-02 00:02:22.0002010-03-31 23:59:33.000
PARTITION 3 =2010-04-01 00:01:06.0002010-06-30 23:59:22.000
PARTITION 4 =2010-07-01 00:00:40.0002010-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.0002010-03-31 23:59:33.000
PARTITION 2 =2010-04-01 00:01:06.0002010-06-30 23:59:22.000
PARTITION 3 =2010-07-01 00:00:40.0002010-09-08 03:25:23.000
PARTITION 4 = NULL NULL
September 30, 2010 at 9:51 pm
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.
October 1, 2010 at 3:16 am
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!
October 1, 2010 at 3:33 am
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.
October 1, 2010 at 6:54 am
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.
October 1, 2010 at 8:35 am
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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
October 1, 2010 at 8:43 am
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.
October 1, 2010 at 9:15 am
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.
October 1, 2010 at 9:18 am
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.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply