Need Details for Database Table partition in Sql server 2005 And 2008 Developer/Enterprise edition

  • Hi,

    Is that correct that Table Partitioning will be available in Sql server Developer and Enterprise Edition only ?

    Can you guide me by providing some step by step details to partition a table in an existing database.

    I have a very big DB, some of the table belongs to this db is very big , we are planing to implement table partition over them.

    Its better if it can be implemented using SQL Server Management Studio for understanding the process.

    please be specific about the edition/version you refer in your steps.

    Thanks in advance.

  • yo_jain (1/3/2012)


    Hi,

    Is that correct that Table Partitioning will be available in Sql server Developer and Enterprise Edition only ?

    Yup. Enterprise only

    Can you guide me by providing some step by step details to partition a table in an existing database.

    I have a very big DB, some of the table belongs to this db is very big , we are planing to implement table partition over them.

    Its better if it can be implemented using SQL Server Management Studio for understanding the process.

    Start with Books Online. There's a lot in there on partitioning, both on planning and the implementation details. Then read through these blog posts:

    http://blogs.msdn.com/b/craigfr/archive/2006/11/27/introduction-to-partitioned-tables.aspx

    http://blogs.msdn.com/b/craigfr/archive/2008/07/15/partitioned-tables-in-sql-server-2008.aspx

    http://blogs.msdn.com/b/craigfr/archive/2008/08/05/partitioned-indexes-in-sql-server-2008.aspx

    Then a whitepaper or two

    http://msdn.microsoft.com/en-us/library/dd578580.aspx

    http://msdn2.microsoft.com/en-us/library/ms345146.aspx

    One question before you start though...

    Why are you partitioning the tables? What's the goal here?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for reply.

    i read these articles.

    But still it is better if someone guide me by providing some precise and step by step details to partition a table in an existing database.

    I have a very big DB, some of the table belongs to this db is very big , we are planing to implement table partition over them.

    Its better if it can be implemented using SQL Server Management Studio for understanding the process.

    please be specific about the edition/version you refer in your steps.

  • Regarding partitioned Index :-

    One more thing if suppose there is a table A, having primary key over column 'A1'

    ,this contains 700 columns and out of these 15 columns are having indexes.

    then after implementing partition over this table ,Do i need to implement partitioned over all the indexes aslo or just only on inxed attached to primary key.

  • yo_jain (1/5/2012)


    But still it is better if someone guide me by providing some precise and step by step details to partition a table in an existing database.

    I have a very big DB, some of the table belongs to this db is very big , we are planing to implement table partition over them.

    Its better if it can be implemented using SQL Server Management Studio for understanding the process.

    please be specific about the edition/version you refer in your steps.

    Sure. I charge $100/hour for training or consulting work.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • yo_jain (1/5/2012)


    then after implementing partition over this table ,Do i need to implement partitioned over all the indexes aslo or just only on inxed attached to primary key.

    Both are valid options, and both have pros and cons. A couple of the articles I referred you (especially the whitepaper) do discuss whether or not to align the indexes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • We're not going to explain in detail the steps that are needed for your very large database. You need to learn how to do it.

    How? Ask specific questions on things you don't understand, but first make a small database on a test server, add a table, then partition the table. If you make a mistake, start over. If you don't understand, ask about the specific thing you don't understand.

  • Steve Jones - SSC Editor (1/5/2012)


    We're not going to explain in detail the steps that are needed for your very large database. You need to learn how to do it.

    How? Ask specific questions on things you don't understand, but first make a small database on a test server, add a table, then partition the table. If you make a mistake, start over. If you don't understand, ask about the specific thing you don't understand.

    I will take it a step further and say that if you REALLY have a significant system that needs optimal maintenance/ETL/performance then you simply MUST engage a KNOWLEDGEABLE consultant in order to do partitioning right (and even to understand if it is required in the first place). Partitioning is a COMPLEX subsystem with many areas that can become problems. I have come across MANY situations where partitioning was implemented by unqualified individuals/teams and the results were no improvement AT BEST and horrible performance/maintenance nightmares at worst. In at least half of those situations there was absolutely no need for partitioning in the first place. It was put in because it was "shiny" and/or "it will improve our performance, which sucks". Wrong answer!

    Gail is one of the best at the relational engine (although I have no direct knowledge of her experience level with partitioning), and believe me her rate is an EXCEPTIONAL value. Unless you live in South Africa it might be tough to get some onsite time with her. If you desire/need onsite activity there are a few others on this site that consult or you can bring in one of any number of other consultants or consulting firms.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 8 posts - 1 through 7 (of 7 total)

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