October 25, 2012 at 3:22 pm
Hello,
I will be starting on a project to create table partitions for one of our largest tables. In this case we must keep all history, so once we create a file\filegroup it will never be removed and will always be queried. I am planning to have each file\filegroup to have approximately 40-million records. With the data we have now this will be about 50 files\filegroups. Going out 15-20 years I think there will be another 50 of these files. I was planning to create 1 file per filegroup. I think it would be simplest to create all the files and Filegroups upfront.
My concern is managing all the files particularly if I have to do a restore of the DB to another environment. How do you handle all the files and make sure they are restored to the appropriate place.
Thanks in Advance for any ideas.
October 25, 2012 at 3:30 pm
Why do you want each partition in its own filegroup?
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
October 25, 2012 at 3:41 pm
For ease of moving them around if needed. What would you recommend given the size and amount of data?
October 25, 2012 at 4:09 pm
mishka-723908 (10/25/2012)
For ease of moving them around if needed. What would you recommend given the size and amount of data?
You mentioned 40 mill records per partition, but what's row-width and contained information? Any chance to see the base DDL for this table (and indexes would probably help)?
Also, you might think to cut down the volume of the partitions, but that'll depend on your data access methods. In general, try to keep the partitions to 'groups' of data that will usually be accessed together. Pushing your non-clustered indexes off to other filegroups (and drive spindles) may get you more gain than just a raw data manipulation, depending on what the intent is.
The problem with filegroup manipulation and partitions is that it's not just the tabledata that will really drive all the different choices you could make regarding it. The more information you can give us regarding the data, the usage, and the intent, the better we can advise you towards your best choices.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2012 at 6:39 pm
this is the current table. I will have to keep it mostly as is. I will be adding a unique_id, create, and modified dates at the bottom. "period" will be the partition key. There is the one current-clustered index, and a couple of non-clustered indexes that will change.
CREATE TABLE [dbo].[ABC](
[type_id] [char](3) NOT NULL,
[door_no] [char](5) NOT NULL,
[set_no] [char](3) NOT NULL,
[bank] [varchar](4) NULL,
[debt_id] [char](6) NOT NULL,
[exdebt_id] [varchar](18) NOT NULL,
[last_date [smalldatetime] NULL,
[balance_due] [money] NULL,
[rate] [numeric](6, 3) NULL,
[payment] [money] NULL,
[balance_2] [money] NULL,
[weekly_pymt] [money] NULL,
[active_flag] [varchar](1) NULL,
[stat_flag] [varchar](1) NULL,
[name] [varchar](12) NULL,
[error_flag] [varchar](1) NULL,
[starting_date] [smalldatetime] NULL,
[ending_date] [smalldatetime] NULL,
[ending_flag] [varchar](1) NULL,
[accounting_date] [smalldatetime] NULL,
[include_flag] [varchar](1) NULL,
[second_date] [smalldatetime] NULL,
[inventory_bal] [money] NULL,
[nexr_pyt_date_r] [numeric](6, 3) NULL,
[amount_lost] [money] NULL,
[apr] [numeric](7, 4) NULL,
[period] [smallint] NOT NULL,
[file_name] [char](8) NOT NULL,
CONSTRAINT [PK_ABC] PRIMARY KEY CLUSTERED
([door_no] ASC,
[period] ASC
)
October 26, 2012 at 10:28 am
Good morning. So does anyone have any idea that could help on what are the best methods for deciding how to allocate files\filegroups? Does anyone know if there are any guidelines out ther?
thanks in advance.
October 26, 2012 at 12:27 pm
Roughly, you're looking at ~120 bytes/row. What are Door_no and Period, and is there any additional indexing?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 26, 2012 at 12:35 pm
they compromise the natural (primary key)
October 26, 2012 at 12:52 pm
mishka-723908 (10/26/2012)
they compromise the natural (primary key)
I'm not being explicit enough. I'm trying to determine HOW the data is used, not merely the logical build (I can see it's the compound key in the DDL). I need to know how you access the data, why you access it, what the breakdown of those fields look like.
Blind partitioning is useless, you can hose up your data access or at best see no difference. This isn't a one-size fits all kind of thing. It's iterative. It depends on what you're doing outside of it and need to reference.
I need full, detailed, answers to questions, not vague one-liners, or I cannot assist you in a way that will actually be helpful other than as vague, non-committal types of solutions so I don't end up looking like a moron when it doesn't work in two weeks for anything more specific that didn't ask the right questions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 26, 2012 at 2:31 pm
I appreciate your help.
Period is like a Month, currently we have about 280 periods and the system will have a total of 508 periods. Door_id is like an account number.
Usually the data will be accessed in 12 periods at a time (1 year).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply