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 Wednesday, April 29, 2009 12:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:21 AM
Points: 36, Visits: 124
Comments posted to this topic are about the item Partitioning in SQL Server 2008
Post #706495
Posted Wednesday, April 29, 2009 2:34 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: Yesterday @ 4:30 AM
Points: 544, Visits: 727
A very nice interesting and easy to read article on partitioning.

There are a couple of errors in the syntax though with regards to creating a partition function:
CREATE PARTITION FUNCTION partfunc (int) AS
RANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000);

CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION Mypartfunc TO([FG1], [FG2])
GO

The partition function is named 'partfunc' though the partition scheme references partition function 'Mypartfunc'. Additionally, there are less filegroups set in the partition scheme than there are partitions in the partition function. I guess these are just typos.


I would also have split the article into several articles over a series as most people prefer short articles to absorb and try out the features themselves. Splitting, Merging, Switching and the rolling window scenario are detailed topics in their own right to which only a brief mention has been made in this article. Maybe a detailed follow up ?
Overall though I thought an interesting and worthwhile introductory piece to partitioning. Thanks.


Paul R Williams.
Post #706534
Posted Wednesday, April 29, 2009 3:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:32 AM
Points: 1,260, Visits: 3,423
Hmmm if you compare with SQL Server 2005 here in SQL Server 2008 now is much easy how to partitioning because you have wizards, all what you need to do is to have logic what do you want to do with large data with partitioning!

Anyway, all these SQL users that have experience with partitioning in SQL Server 2005 they will do partitioning in SQL Server 2008 very easy and in brilliant way, but sure if they know the reason and steps why where and how to do partitioning!



============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #706584
Posted Wednesday, April 29, 2009 4:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:48 AM
Points: 3,241, Visits: 5,002
Alot info. Some of the things have really changed in SQL Server 2008.



----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #706633
Posted Wednesday, April 29, 2009 4:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:00 PM
Points: 235, Visits: 834
Have they created a GUI for you to see how your data is split among partitions in SSMS 2008? Before I just used a custom RDL report in SSMS 2005 for querying all the system views pertaining to objects and partitioning.
Post #706637
Posted Wednesday, April 29, 2009 7:02 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 9:10 AM
Points: 424, Visits: 238
Great article - glad you mentioned that horizontal partitioning is only available in the enterprise version.

However, I think it is worth mentioning that vertical partitioning is available in all flavours of SQL and this can help considerably with performance. For those who are not familiar with this, vertical partitioning allows you to split a database down into separate FILEGROUPs, which can then be spread over different physical file systems. This allows you to specify where tables reside, so keeping heavily accessed tables apart from each other and leveraging added performance gained from the separated underlying filesystems. In addition, you can keep your indexes in separate FILEGROUPS - again away from each other and the data, adding even more potential performance. The downside is that you can only specify the whole table or index to belong to a particular FILEGROUP, though the indexes can reside on different FILEGROUPS from each other and the data.

Although vertical partitioning will not give you the gains that horizontal does, it will give significant gains over a single homed database structure.

Also, for those with non Enterprise versions, you can still use partitioned views which will give you the same benefit as horizontal partitioning, but may involve a bit more complexity.

Regards,

Brian Jones
Post #706762
Posted Wednesday, April 29, 2009 8:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Great article and easy to read. Done it in 2005 last year but had not seen a significant increase in performance.

SQL DBA.
Post #706872
Posted Wednesday, April 29, 2009 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 24, 2013 5:30 PM
Points: 7, Visits: 157
Could you tell me how to over come performance issue when using order by clause partition table.I have created couple partition tables with partition indexes and when I ran query including order by clause it seek index but sort 92% and same query without order by clause it doesn't sort and there is no performance issue.If I run same query with order by clause on same non partition table it works perfect.Any comment please.
Post #707096
Posted Wednesday, April 29, 2009 1:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:00 PM
Points: 235, Visits: 834
SID (4/29/2009)
Could you tell me how to over come performance issue when using order by clause partition table.I have created couple partition tables with partition indexes and when I ran query including order by clause it seek index but sort 92% and same query without order by clause it doesn't sort and there is no performance issue.If I run same query with order by clause on same non partition table it works perfect.Any comment please.


I don't understand your issue exactly, but remember if you are ordering on a partitioning table, you are doing a UNION ALL between each partition, then sorting. So if your index is aligned with the partition scheme, it will negate any sorting benefits that you had with a non-partitioned table. You can create a non-aligned index on that field that should give you better performance - however non-aligned indexes would prevent you from doing any partition switching.
Post #707182
Posted Wednesday, April 29, 2009 1:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 6, 2010 1:30 PM
Points: 34, Visits: 82
I expect you didn't expect anyone to try your code. If you had tested it, you would find it generates errors.
Post #707190
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse