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
MuhammadSiddiqi
MuhammadSiddiqi
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 124
Comments posted to this topic are about the item Partitioning in SQL Server 2008
Paul Williams
Paul Williams
Mr or Mrs. 500
Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)

Group: General Forum Members
Points: 566 Visits: 873
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.
Dugi
Dugi
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1490 Visits: 3511
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!

Cool

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3411 Visits: 5175
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


Gabriel P
Gabriel P
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 946
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.
Brian Jones-401554
Brian Jones-401554
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 289
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
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4073 Visits: 1619
Great article and easy to read. Done it in 2005 last year but had not seen a significant increase in performance.

SQL DBA.
SID-746731
SID-746731
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 196
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.;-)
Gabriel P
Gabriel P
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 946
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.
don_goodman
don_goodman
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 82
I expect you didn't expect anyone to try your code. If you had tested it, you would find it generates errors.
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