Best practices for File groups in OLTP Database?

  • 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

  • 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.


    - Craig Farrell

    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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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" 😉

  • ananda.murugesan (1/27/2012)


    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

    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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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" 😉

  • 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.


    - Craig Farrell

    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

  • 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

  • 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.


    - Craig Farrell

    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

  • 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