January 24, 2013 at 10:13 am
We have Database AUENG and tables called AUSM it has two date columns and it's very big table and we would like to make a partition table.
This is a frequently accessed table.
I was reading some documents and found some info but little confused as i need to add cluser key on partition or need to make a copy and then add the partition or i can directly
add partition into exisisting table?
We need partition for different date ranges.
Ex:
If Open_date > Today's date then i need to move into Partition1
If Open_date = Today's date then i need to move into Partition2
and for another column
If Close_date < Today's date - 24 hrs then i need to move into another Partition3
IF you guide me please.
Thanks,
January 24, 2013 at 10:17 am
poratips (1/24/2013)
We have Database AUENG and tables called AUSM it has two date columns and it's very big table and we would like to make a partition table.
Why? What's the purpose of partitioning? What's the intended goal?
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 24, 2013 at 10:25 am
Thanks.
We need to improve the performance. We have few slower queries whcih we also tried to improve with indexes and we have everyday lots of blocking due to insert/update.
We also have Purge job running everyday hourly
January 24, 2013 at 11:30 am
I will create Partition function, scheme and add the partition but i need to know that i can create partition into exisisting table or i have to create new table, add partition, copy data from original table to new table, rename new table?
January 24, 2013 at 11:33 am
poratips (1/24/2013)
Thanks.We need to improve the performance.
Partitioning is not primarily for performance. It can improve performance, but that usually requires queries constructed to take advantage of the partitioning. If your sole goal for this partitioning is improved performance, don't waste your time.
Primary use for partitioning is the fast load (switch data in as a metadata operation) and fast removal of old data (switch partitions out as a metadata operation)
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 24, 2013 at 11:53 am
Gail,
you right and i am agreed with you as we are planningto switch the query to access the specific partition and same thing for Delete.
January 24, 2013 at 2:31 pm
Cool, then start by doing a lot of reading on partitioning, it's not something you want to implement without a good understanding.
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 24, 2013 at 2:49 pm
Thanks Gail.
Is it Ican add the partition into exisisting table or i need to create new table and then copy it??
January 24, 2013 at 3:01 pm
You can partition an existing table.
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 24, 2013 at 4:37 pm
Thanks Gail.
I will doing following steps:
1) I will be creating one or two new filegroup as we have only one PRIMARY FILE GROUP
2) I will create the partition function for my DateTime Column
3) I will create the partition scheme to link the partition function to the new filegroup
Now i have to DateTime Column - OpenDate and closeDate, I need to use following logic:
If Opendate > Today's date then i need to move into Partition1
If Opendate = Today's date then i need to move into Partition2
and for another column
If Closedate < (Today's date - 24 hrs) then i need to move into another Partition3
Could you please suggest, how i can handle it?
January 25, 2013 at 1:55 am
poratips (1/24/2013)
Now i have to DateTime Column - OpenDate and closeDate, I need to use following logic:If Opendate > Today's date then i need to move into Partition1
If Opendate = Today's date then i need to move into Partition2
and for another column
If Closedate < (Today's date - 24 hrs) then i need to move into another Partition3
Could you please suggest, how i can handle it?
Not possible. Partition boundaries must be fixed values. 'Today's date' is not a fixed value.
Please, go and do some reading up on partitioning. It's not something you want to be trying to implement with minimal knowledge
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 25, 2013 at 10:07 am
Hello,
If anyone has idea?
January 25, 2013 at 10:23 am
If your primary concern is query speed, partitioning isn't the way you want to go. It could be part of the solution, but as Gail suggested you'd need a lot of research first, more than can be provided in forum answers.
The way you're going to get a performance boost is by creating archive tables that are identical in every way (other than the name) to your existing tables and move all the data that doesn't need to be accessed into those tables. Partitioning, if implemented properly, can make moving the data into the archive tables on a daily basis quick, but it isn't going to give you performance benefits on its own.
The only area where partitioning will give you immediate benefits is in re-building indexes on tables as you can re-build single partitions. Other than that, you'll need very well designed queries and even then it isn't going to gaurantee any performance boost.
My advice is to not partition right now, go for archive tables and when you have time to do the research, look into partitioning and play with them a LOT in a dev environment before implementing them.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 26, 2013 at 12:51 pm
As Gail (GilaMonster) said, what you want to do respecting dates is not possible with partitions. She is one of THE strongest people you will ever have answer a question for you. When she speaks, listen respectfully.
You could, however maintain three separate tables and use your date logic to determine which table to insert a row into. For retrieving rows, you could use a view which does a UNION ALL of the three tables. However this approach is more complicated and I do NOT advise you to do it.
It might help if you could explain to us why that logic is so important to your application. Don't just say "performance", please explain your thinking in detail. An alternate solution might exist to get you to the same destination by a different route.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply