January 27, 2012 at 3:05 am
HI,
what is the best practices and how many files can be created?
OLTP Database storing application data and Index data (primary, secondary file group) ?
thanks
January 27, 2012 at 3:17 am
Um, erm... :ermm:
"It Depends". What's your physical spindle separation look like? Are you dealing with BLOBs? What's your table access look like? What's your lookup data vs. what's your primary data?
There's a lot that goes into splitting up filegroups. The primary purpose of it is for spindle speed. A secondary purpose of it is for piecemeal restoration. Last but not least it keeps LOB data out of your real data.
So, whatcha got and whatcha doin'? Maybe we can help you figure out what you would really prefer. And if you don't have information on the physical spindles, you'll want to go get it before we continue. I don't mean C:/, D:/ on the box, either. I mean the physical raid array configuration, LUN data, shared spindle resources, and if you're in a SAN you're going to have to talk to your admin there to determine if you're sharing drives amongst separate LUNs.
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
January 27, 2012 at 3:22 am
There are two main reasons you'd split a DB into multiple files or filegroups
1) Performance - requires that the DB is IO bottlenecked and the split is well designed in order to have any effect
2) Recoverability - requires that the split is carefully designed and tested in order to have the desired effect
There's no one-size-fits-all solution here.
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 27, 2012 at 3:42 am
Hi, Thanks for yours reply..
This OLTP Project not used advanced data storing
1. What's your physical spindle separation look like?
"D" drive for Data files MDF, NDF..
"E" drive for Tlog
"F" drive for TempDB
Here there is one single array configured (all RAID 5) this server, so those drive configuration is not getting much more benifits. inseted of using those drive configuration, can placed data, Tlog in one single "D" drive?
2. Are you dealing with BLOBs?
This application not used BLOB datatype..
3. What's your table access look like?
only trasnactional and monitor data likes plant activity
Should I use in Single primary filegroup and create multiple files MDF for using application date, NDF for using index if created?
thanks
ananda
January 27, 2012 at 4:26 am
If there's only a single RAID 5 array, then there is going to be no performance benefit at all in separating out the files, they'll all still be on the same physical drives.
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 27, 2012 at 4:51 am
ok, please confirm as below configuration
So will decide to change disk configuration for before implementation SQL server 2008 in OLTP environment
Total five HD 300x5 15K RPM SAS, and get total usable space is 1050 GB after formatting.
RAID 1
100GB “C” drive – an install OS,
170GB “D” drive – an install SQL Server Executable files, Transaction Logs
RAID 5
380 GB “E” – Data files (mdf, ldf)
100 GB “F” – Tempdb database
300 GB “Backup purpose
thanks
January 30, 2012 at 1:25 am
ananda.murugesan (1/27/2012)
Should I use in Single primary filegroup and create multiple files MDF for using application date, NDF for using index if created?thanks
ananda
Do you have a valid reason for creating multiple files per file group?
If your primary file in the primary file group already has data\objects created then adding more files at this point will just destroy the proportionate fill, rather, create a new file group with new files and create your objects upon the new file group. Again, providing you have a valid reason for multiple files per group.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 31, 2012 at 7:16 am
ananda.murugesan (1/27/2012)
ok, please confirm as below configurationSo will decide to change disk configuration for before implementation SQL server 2008 in OLTP environment
Total five HD 300x5 15K RPM SAS, and get total usable space is 1050 GB after formatting.
RAID 1
100GB “C” drive – an install OS,
170GB “D” drive – an install SQL Server Executable files, Transaction Logs
RAID 5
380 GB “E” – Data files (mdf, ldf)
100 GB “F” – Tempdb database
300 GB “Backup purpose
thanks
1) There is no reason to create multiple LUNs on the RAID 5 group. You are setting yourself up for running out of space eventually on one or another of them. You get no benefit and can actually DECREASE throughput by spreading files out on the physical platters, leading to more head movement and head thrashing both.
2) If this is a busy write system, be prepared for some transaction log write issues with just one spindle accepting tlog activity.
3) backups on SAME DRIVES AS DATA!!! This is a CRITICAL flaw and exposes you to COMPLETE LOSS OF ALL DATA!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 1, 2012 at 12:08 pm
Perry Whittle (1/30/2012)
ananda.murugesan (1/27/2012)
Should I use in Single primary filegroup and create multiple files MDF for using application date, NDF for using index if created?thanks
ananda
Do you have a valid reason for creating multiple files per file group?
If your primary file in the primary file group already has data\objects created then adding more files at this point will just destroy the proportionate fill, rather, create a new file group with new files and create your objects upon the new file group. Again, providing you have a valid reason for multiple files per group.
According to Microsoft (I am studying for 70-432) you should always create at least 3 files and 2 filegroups. 1 file being the t-log, and 2 datafiles. Each data file resides in its own filegroup. Primary for system objects and the other for user objects created; i.e. change default to other file group immediately after creating database. Does this hold true on a general level?
Jared
CE - Microsoft
February 1, 2012 at 12:32 pm
No. It's an option, and it makes some sense (especially when there are multiple filegroups and splitting is done for recoverability reasons), but it is by no means a universal rule.
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
February 1, 2012 at 12:53 pm
SQLKnowItAll (2/1/2012)
Each data file resides in its own filegroup.
That's not the same as multiple files per file group, which if you don't have a need for, leave well alone.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2012 at 1:47 pm
SQLKnowItAll (2/1/2012)
According to Microsoft (I am studying for 70-432) you should always create at least 3 files and 2 filegroups. 1 file being the t-log, and 2 datafiles. Each data file resides in its own filegroup. Primary for system objects and the other for user objects created; i.e. change default to other file group immediately after creating database. Does this hold true on a general level?
That's a new best practice to me. I can understand the theory (as Gail mentioned above, for recoverability), but personally I feel it's a bit of overkill. I've never had a reason to do that personally.
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
February 1, 2012 at 1:53 pm
Awesome... So for the test, it is what it is. In practice, as always, it depends 🙂 Can you give an example of a situation where you would choose not to do it and how that situation might change to make you want to do it? Just looking for 1 good example to help me visualize it.
Jared
CE - Microsoft
February 1, 2012 at 2:24 pm
SQLKnowItAll (2/1/2012)
Awesome... So for the test, it is what it is. In practice, as always, it depends 🙂 Can you give an example of a situation where you would choose not to do it and how that situation might change to make you want to do it? Just looking for 1 good example to help me visualize it.
I would almost never choose to do it. I can't imagine a situation where splitting my system tables off my data tables would affect recoverability speed. By the time I'd be looking to split my single file database into multiple files to assist in recoverability, the system tables would be the least of my concern. Otherwise it's just a headache, in my perspective.
There's what seems to be a decent discussion here: http://dba.stackexchange.com/questions/1359/sql-server-filegroup-for-system-tables-only
I don't run into one page corruption (or other likely reasons recoverability at this level would be necessary) that often, if ever. I start getting corruption I start swapping hardware and doing RCAs, not filegroup splits. If I'm dealing with recoverability time concerns, system tables are not large enough to matter at that point.
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
February 1, 2012 at 2:37 pm
Evil Kraig F (2/1/2012)
SQLKnowItAll (2/1/2012)
Awesome... So for the test, it is what it is. In practice, as always, it depends 🙂 Can you give an example of a situation where you would choose not to do it and how that situation might change to make you want to do it? Just looking for 1 good example to help me visualize it.I would almost never choose to do it. I can't imagine a situation where splitting my system tables off my data tables would affect recoverability speed. By the time I'd be looking to split my single file database into multiple files to assist in recoverability, the system tables would be the least of my concern. Otherwise it's just a headache, in my perspective.
There's what seems to be a decent discussion here: http://dba.stackexchange.com/questions/1359/sql-server-filegroup-for-system-tables-only
I don't run into one page corruption (or other likely reasons recoverability at this level would be necessary) that often, if ever. I start getting corruption I start swapping hardware and doing RCAs, not filegroup splits. If I'm dealing with recoverability time concerns, system tables are not large enough to matter at that point.
Great link, thanks! I actually posted Microsoft's reasoning there just to help them out. I see your point though as well.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply