Partitioning in SQL Server 2008

  • MuhammadSiddiqi

    SSC Veteran

    Points: 202

    Comments posted to this topic are about the item Partitioning in SQL Server 2008

  • Paul Williams

    SSCommitted

    Points: 1899

    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

    SSCoach

    Points: 17998

    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/[/url]

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    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
    [font="Arial Black"]here[/font][/url][/right]

  • Gabriel P

    SSCarpal Tunnel

    Points: 4228

    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

    Ten Centuries

    Points: 1338

    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

    SSChampion

    Points: 13157

    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

    SSC Veteran

    Points: 209

    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

    SSCarpal Tunnel

    Points: 4228

    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

    Say Hey Kid

    Points: 670

    I expect you didn't expect anyone to try your code. If you had tested it, you would find it generates errors.

  • SID-746731

    SSC Veteran

    Points: 209

    Could you tell me what do you mean by create a non-aligned index on that field. Is there any example.

    I used following code:

    --------------This took 6 minutes on partition table

    use mydb

    go

    declare @P1 char(9)

    set @P1=' '

    SELECT top 1000 a.t_hrea,a.t_bpid,a.t_bprt,a.t_dtbl,a.t_dtrl,a.t_erdt,a.t_lobl,a.t_lorl,a.t_opon,a.t_orno,a.t_pono,a.t_pstc,a.t_rlam,a.t_rlty,a.t_sqnb

    FROM dbo.ttdsls421900_par a

    WHERE (a.t_orno > @P1) ORDER BY 10,11,15,1,4 OPTION (FAST 5)

    --------------This took seconds on non-partition table

    use mydb

    go

    declare @P1 char(9)

    set @P1=' '

    SELECT top 1000 a.t_hrea,a.t_bpid,a.t_bprt,a.t_dtbl,a.t_dtrl,a.t_erdt,a.t_lobl,a.t_lorl,a.t_opon,a.t_orno,a.t_pono,a.t_pstc,a.t_rlam,a.t_rlty,a.t_sqnb

    FROM dbo.ttdsls421900 a

    WHERE (a.t_orno > @P1) ORDER BY 10,11,15,1,4 OPTION (FAST 5)

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

    If I remove ORDER BY Clause from code and run against partition table it will give same result as non partition table.

  • Gabriel P

    SSCarpal Tunnel

    Points: 4228

    Aligned index (separate indexes split by partition):

    create index ix_pt_index on partitioned_table (indexed_value)

    ON [ps_PartitionScheme](partition_key) --Name of partition scheme

    Non-aligned index (one index that covers all values across all partitions):

    create index ix_index on partitioned_table (indexed_value)

    ON [FG_Index] --Name of filegroup

  • SID-746731

    SSC Veteran

    Points: 209

    You mean non partition index. I did created cluster index ( non allign) on partition table and after that Partition table became non partition table.

  • Gabriel P

    SSCarpal Tunnel

    Points: 4228

    Yes I meant a nonclustered index.

  • SID-746731

    SSC Veteran

    Points: 209

    for example when I creted cluster index, partition table lost the partition scheme and becomes non partion.

    And when I created just the non cluster index , execution plan doesnt take this index and keeps go to sort 92%.

    Thanks

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply