December 21, 2009 at 10:43 pm
I need to partition a fact table for 36 months using a monthly range. I also want to use one file and one filegroup for each partition to ease backups and maintenance.
I know that I will only need 36 partitions because I will be aging off the data.
Can I create all 36 files and filegroups from the get-go so I don't have to build scripts to create them each month? I have the disk space to do it.
If it's feasible, is it a good idea?
I am assuming that the lowest partition ID that has not been used automatically gets the NEXT USED designation.
Thanks.
---------------------------
|Ted Pin >>
December 22, 2009 at 2:35 pm
pre-configuring like that will reduce external fragmentation which occurs when other file-groups or applications are competing for disk space on the same physical drive.
This results in better performance on all the inserts and can help the queries since the data in the filegroups is all contiguous on the disk.
The downside is that if you have multiple file groups on the same disk containing tables that need to be joined with tables in other file groups on the the same drive there will be more seek time involved and thus performance could be degraded.
You need to know not only how much data you have but how it might interact before you can arrive at a good answer.
The probability of survival is inversely proportional to the angle of arrival.
December 22, 2009 at 2:43 pm
sturner has offered some good advice.
In addition, by doing this up front - you may save yourself some administration time down the road. Before doing, you will need to have an in-depth analysis and understanding of your database and data usage so that you can design it correctly. If you are able to create multiple luns for the filegroups to limit the seek time issue that sturner mentioned, you could be very well situated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2009 at 4:44 pm
We are joining a somewhat large fact table with dimensions and I am going to put the fact table and dimensions on different LUNs.
Your answers were exactly what I need to move forward.
Thanks!
---------------------------
|Ted Pin >>
December 22, 2009 at 4:54 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2009 at 6:08 pm
Follow-up question: Can I use a rotating window scenario here?
This way I can reuse the existing filegroups after we reach 36 months of data.
Of course, we would archive off and the oldest filegroup and switch into it an empty staging table.
---------------------------
|Ted Pin >>
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply