September 19, 2008 at 5:16 am
Hi All,
I have created filegroups, partition function and partition scheme. Issue is that how can I convert my existing table into partition table.
Is there any way to do this with sql server 2005.
Thanks in advance.
Rajnish
September 19, 2008 at 6:47 am
1- You create your partitioned table
2- You populate your partitioned table by moving the data you have in your non-partitioned table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2008 at 7:28 am
Make sure you understand it !
Then play around and test a bit !
Then make a "plan of attack" for production :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 19, 2008 at 7:49 am
ALZDBA (9/19/2008)Then make a "plan of attack" for production
Oh boy, that phrase scares the s*%$#W out of me :w00t:
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2008 at 8:04 am
and it should !
Partitioning is an advanced topic.
It needs proper peparation and if performed untidily it will bite you in the back !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 19, 2008 at 8:12 am
Agreed. There are many cases where people think that partitioning is the solution for performance and it is not unless used correctly and in the right situation. We just went through a fairly rigorous test sequence using several different table layout methodologies as well as partition schemes associated with those layouts. With all that we had the base set of queries already defined so that we could truly test them against every scenario to ensure that we were picking the proper one.
Some will say that they don't know all that information at startup and to that I would say be careful. When you are considering partitioning it is because you are looking at large data sets and you will NEED to have a deep knowledge of the data AND the data access methods in order to properly design your tables and if appropriate your partitioning strategy.
Hope I encouraged you to consider ALZDBA's input seriously.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 19, 2008 at 8:29 am
There is a two bullets check list for you to decide wether to partition or not a table. You have to ask yourself the next two questions:
1- Does partitioning strategy would help query performance?
2- Does partitioning strategy would help purge strategey?
If you get two NAY... forget about it.
If you get two AYE... go for it without looking back.
If you get one of each... take into consideration that most likely paritioning is gonna hurt whoever said NAY.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 20, 2008 at 4:58 am
I can understand that partition table is advance topic.
I have two tables, each table size is around more than 2 GB. Tables normally used for reporting purpose and noncluster indexes are placed on 5-6 fields.
Please suggest me that really should i go for partition table to improve its performance or any other solution which can improve query speed.
Thanks & Regards
Rajnish
September 20, 2008 at 6:38 am
Improving query performance depends upon many things. You have to look at the queries, see if more indexes would help. Or if different indexes would help. How often do the tables change? How are they accessed overall? More indexes slow insert/update performance, so you have to watch this.
Are queries in a range of some sort? Perhaps partitioning will help. Do you have Enterprise Edition? If not, no partitioning.
Is the server overloaded? Maybe you need more memory or CPU? Or your disk subsystem can't keep up.
Lots to look at to speed up queries.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply