January 3, 2012 at 6:30 am
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.
January 3, 2012 at 6:42 am
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
January 5, 2012 at 3:52 am
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.
January 5, 2012 at 3:53 am
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.
January 5, 2012 at 4:00 am
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
January 5, 2012 at 4:02 am
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
January 5, 2012 at 9:35 am
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.
January 6, 2012 at 6:44 am
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