Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cobains
Starting Member
2 Posts |
 Posted - 2009-07-21 : 11:52:47
|
| Hello all,I was hoping someone could help me out with a SQL 2005 partitioning question I had. I am currently working with a very large database (up to billions of records in one table). I was thinking of using partitioning to manage this size and keep with performance.My problem is I'm not quite sure how to apply it. I want to apply a "growing window" scheme (where we don't ever roll off any data). My application crunches and models data. It allows users to go in and create a "scenario" with user selected parameters. The application assigns each of these scenarios with an ID (an increasing INT field) and calculates/creates about 500 million records per scenario. This all gets saved to a table.So my large table looks like (simplified) 500 million of these rows for one scenario ID:[Scenario Id] [Other PK attributes] [Data Amount]Users query this table and other applications use this table based on the scenario Id, so I'd like to partition on the scenario Id. The problem is, I don't know how to setup the partition function so that say scenario ID 1 is on FG1, ID 2 on FG2, ID 3 on FG3, ID 4 back to FG1, ID 5 back to FG2, etc.The ID field really has no upper bound (since users can go in an create a new scenario whenever they want). I'd like to try to keep each scenario on a different FG since that is all related data (rarely, if ever would they look at data across scenario ids). The problem is, I can't figure out how to do this with the partition function, since that takes in a range that is ever growing.It seems if I could define my range to be scenario ID mod 3, that may work, but I don't think that is allowed. any thoughts or advice would be greatly appreciated. Thanks!Alex |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-22 : 04:12:13
|
| You would need to create a new filegroup on the partition for each scenarioid and add this into the partitioned table. I don'tt hink this would be very economical as any indexes would need to be dropped and re-created each time you add a new FG anyway.How about creating an indexed view for each scenario and letting the users query that instead? This would speed up your querying for each scenario and it would create much less maintenance on your part. |
 |
|
|
Cobains
Starting Member
2 Posts |
Posted - 2009-07-22 : 11:08:28
|
| Hmmm, thanks for the insight Rick. So it may seem that partitioning may not be the key for that.Interesting idea on the indexed view... So on the backend all the data would just be stored in one large table, with an indexed view per scenario? Would the indexed view provide enough performance for each scenario? I'm going to assume since it's indexed, it'll only have pointers to the scenario's it needs in the large table, so it doesn't need to scan the entire billion record table for records...I was looking into the old 2000 view partitioning as well, that may fit into our scheme a little better... |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-22 : 11:28:20
|
| Yeah, thats the basics. You would have a never changing view per scenario, meaning searching should be faily fast. The only downside is the limits of indexed views, but don't think this will affect you in this case as you will not need to do any of this on the basic data at this level, you are just giving a view on the base data where ScenarioID = current scenario and creating your index for the view on the ScenarioID.This link may help you decide:http://technet.microsoft.com/en-us/library/cc917715.aspxThe only overhead you will have is when you create a new Scenario and have to create a new indexed view. This can be done in a few ways, but creating the index will still take time. |
|
|
|
|
|
|
|
|
SQL 2005 Paritioning help