SQL Server Performance

partitioning the existing large table

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by SQLDBAS, May 7, 2007.

  1. SQLDBAS New Member

    Hi ,
    I have a table which is having 130Million records(Not partitioned) based on every week. This is what i did ..
    1. Created PF(Function)
    2. Created PS(Schema) ON FILE GROUP ( consisits of 10 physical files)
    3. Created CLUSTERED index on PS .

    When I did SelecT * From sys.partitions It is showing rows in each partition.

    I have indexid=1 for all partitions . that means is my index is not partioned ?
    How do I achive the partitioned index on each of partition??
  2. MohammedU New Member

    Indexid = 1 is clustered index, cluster index is nothing but the table...

    Microsoft SQL Server 9.0 Technical Articles
    Partitioned Tables and Indexes in SQL Server 2005
    http://msdn2.microsoft.com/en-us/library/ms345146.aspx#sql2k5parti_topic27


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. alzdba Member

    ... 2. Created PS(Schema) ON FILE GROUP ( consisits of 10 physical files) ...

    If you only have one filegroup, it will spread the data over the physical files within the filegroup. (cfr striping)

    If you want control over what data gets into a single file, you should create a number of filegroups (with at least one file) and alter your PS to route to its wanted filegroup.

    have al look athttp://www.sqlskills.com/resources/Whitepapers/Partitioning in SQL Server 2005 Beta II.htm (is the workdoc for the bol ref ! you can download the scripts overthere)
  4. satya Moderator

    http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=cf223632-c93b-4242-b0f2-af493e051266 &http://www.davidemauri.it/DasBlog/PermaLink,guid,c9d877ed-3c22-4ff9-87ce-3bf693ada233.aspx this blog too

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. SQLDBAS New Member

    Thank you so much!
  6. SQLDBAS New Member

    One more quick question. does IDentity columns gives any negative impact on partitions?
  7. satya Moderator

    I don't think so, as I haven't come across any issues so far, for instance to partition a table, you must identify which column will be used for partitioning and the ranges assigned to each partition. In this case the values of an identity column can define partition ranges; values one through one million could reside on one partition, one million through two million on the next partition, and so forth. I can see the int based partition will perform better than non-number based column.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. MohammedU New Member

    SQL Server 2005 Books Online
    Transferring Data Efficiently by Using Partition Switching
    http://msdn2.microsoft.com/en-us/library/ms191160.aspx

    Performing partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC CHECKIDENT to check the identity values of your tables and correct the values if you want.



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. SQLDBAS New Member

    AS always .. Thank you so much!
    CAn't we restrict insert the data into the partition which does not exist.
    Eg: my partition Key is 200711 .. I haven't created the partition for this. When I try to insert the record it is inserting into last partition. how can I restrict this.? In oracle it will not allow until you create that partition..
  10. satya Moderator

    It is by default and when you haven't created any partition it will see for available partition, BTW have you enabled AUTOGROW for that specified filegroup/http://msdn2.microsoft.com/en-us/library/ms345146.aspx fyi and further read.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page