none
What is the benefits of Multiple mdf files and ldf files ?

Answers

  • Hi Bhavesh,

    multiple ldf is nonsens because log will be written sequential.
    Multiple mdf makes sense when you use multiple filegroups.

    This scenario may improve following situations:

    - dedicated FG for indexes (not clustered!)
    - separation of read only data from daily data
    - partitioning
    - offload of historical data in separate FG


    Uwe Ricken

    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, January 23, 2013 10:08 AM
  • NO benefit of having multiple log files

    data file please read

    http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/ 

    vt

     

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Wednesday, January 23, 2013 10:11 AM
  • 1.Index on Different file Group advantageous.

    2.One of the really great benefits of usage of filegroups is the separation of "historacal data" and actual.
    Partitioning and partitioned views can use them perfectly!

    3."By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. Data and index information can then be read in parallel by the multiple disk heads"

    4.multiple ldf is nonsens because log will be written sequential-

    Still need more suggestion make this as combined answers.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Thursday, January 24, 2013 11:24 AM

All replies

  • Hi Bhavesh,

    multiple ldf is nonsens because log will be written sequential.
    Multiple mdf makes sense when you use multiple filegroups.

    This scenario may improve following situations:

    - dedicated FG for indexes (not clustered!)
    - separation of read only data from daily data
    - partitioning
    - offload of historical data in separate FG


    Uwe Ricken

    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, January 23, 2013 10:08 AM
  • NO benefit of having multiple log files

    data file please read

    http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/ 

    vt

     

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Wednesday, January 23, 2013 10:11 AM
  • 1.multiple ldf is nonsens because log will be written sequential-->Means only one log it is wise advice.

    - dedicated FG for indexes (not clustered!)--If I put clustered index on it .My Whole table storage goes to Index_Group FileGroup instead of on Primary

    Why this happened and  why we do not put clsutered index on Another filegroup.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Wednesday, January 23, 2013 10:14 AM
  • 1.multiple ldf is nonsens because log will be written sequential-->Means only one log it is wise advice.

    Yes..

    - dedicated FG for indexes (not clustered!)--If I put clustered index on it .My Whole table storage goes to Index_Group FileGroup instead of on Primary. Why this happened and why we do not put clsutered index on Another filegroup.

    No this is not possible. Because when you create a CL index table records are get sorted based on CL index key and when you move cl index to a different file group table get moved to the new as-well . 

    <edit>

     A clustered index consists of index pages as well as data pages. This means that, although the name clustered index suggests that this is an index, it is not just an index, but also contains the table data

    vt 


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Proposed as answer by Satheesh Variath Wednesday, January 23, 2013 10:32 AM
    • Unproposed as answer by Database Experts Wednesday, January 23, 2013 10:36 AM
    • Edited by v.vt Wednesday, January 23, 2013 10:45 AM
    Wednesday, January 23, 2013 10:29 AM
  • Create Table t(t1 int)

    Create Clsuter index cls_indx on t

    (

    t Asc

    ) on [Index_Group]

    That step i do on table and it moved to Index_Group instead of primary.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Wednesday, January 23, 2013 10:34 AM
  • 1.multiple ldf is nonsens because log will be written sequential-->Means only one log it is wise advice.

    Multiple log files and why they’re bad

    - dedicated FG for indexes (not

    clustered!)--If I put clustered index on it .My Whole table storage goes to Index_Group FileGroup instead of on Primary

    You can put both the clustered index and non-clustered index of a table on same file group. However if you move the clustered index and non-clustered index to different file groups that will improve query performance further during key lookups

    Krishnakumar S

    Wednesday, January 23, 2013 10:36 AM
  • if its an existing table then you use cl index method. but if its new table you can specify the file group while creating the table 

    CREATE TABLE dbo.Table_2
    (
    id char(10) NULL
     ) ON [Index_Group]

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by v.vt Wednesday, January 23, 2013 10:37 AM
    Wednesday, January 23, 2013 10:37 AM
  • Create Table t(t1 int)

    Create Clsuter index cls_indx on t

    (

    t Asc

    ) on [Index_Group]

    That step i do on table and it moved to Index_Group instead of primary.

    Since clustered index physically represents table data the table will move to the specified file group when you create the clustered index

    Krishnakumar S

    Wednesday, January 23, 2013 10:39 AM
  • I don't believe that putting indexes on a separate file group is a best practice.  If the file groups are on the same disks, it shouldn't help, and if they are on different disks you will probably waste both storage and IOPS.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, January 23, 2013 12:33 PM
  • Index and data in different file group and different storage.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Wednesday, January 23, 2013 12:42 PM
  • >Index and data in different file group and different storage

      is a waste of time and money.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, January 23, 2013 12:49 PM
  • Index and data in different file group and different storage-->  Database on this stageDo you have any specific example about waste of tome and money.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Wednesday, January 23, 2013 1:06 PM
  • >Index and data in different file group and different storage

      is a waste of time and money.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Hallo David,

    this is not true in "general". By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. Data and index information can then be read in parallel by the multiple disk heads


    Uwe Ricken

    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, January 23, 2013 1:17 PM
  • Even as Oracle DBA and migrated to SQL Server.

    I found something that ..we can do this by filegroup.

    We created separate data files for index storage.

    I was very confuse before not knowing about file group.

    It is true about data file and log files.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Wednesday, January 23, 2013 1:25 PM
  • Yes that is possible, at least in a controlled performance test. 

    But it's actually more likely that the opposite will happen, and you will loose performance doing this.  For a given number of disks, controllers, IOPS, and for a given workload there's an optimal partition of database objects across the disks.  However it is vanishingly unlikely that the partition of "indexes go here, tables go there" is anywhere near optimal.  On the other hand, if you just put all the storage resources behind a single filegroup and put all the database objects there, you are guaranteed a nearly optimal storage configuration, as each table and index will be spread out over all the disks, controllers, etc.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, January 23, 2013 1:26 PM
  • @David

    Totally agree with Uwe..

    Please read

    http://msdn.microsoft.com/en-gb/library/ms190433(v=sql.105).aspx

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by v.vt Wednesday, January 23, 2013 1:28 PM
    Wednesday, January 23, 2013 1:27 PM
  • @David

    Totally agree with Uwe..

    Please read

    http://msdn.microsoft.com/en-gb/library/ms190433(v=sql.105).aspx

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Which states:

    "Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. This is also a simpler approach for system administrators."

    Which is my point exactly.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, January 23, 2013 1:30 PM
  • Hi David,

    you are right - a real discussion about performance with separated nc indexes from the clustered tables or heaps isn't worth the time unless complex tests with the given hardware are made. I tried to follow this strategy with separated indexes but have drawn back from it because it is - in many cases- to complex to maintain and no real performance boost has been measured.

    But I throw this option into the discussion because the TE wanted to know the pro and con about multiple files for data and log :)
    So one of my options "can" be a benefit but don't has to be...


    Uwe Ricken

    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, January 23, 2013 1:34 PM
  • @David

    Totally agree with Uwe..

    Please read

    http://msdn.microsoft.com/en-gb/library/ms190433(v=sql.105).aspx

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Which states:

    "Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. This is also a simpler approach for system administrators."

    Which is my point exactly.

    David


    David http://blogs.msdn.com/b/dbrowne/

    it also say..

    "By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. Data and index information can then be read in parallel by the multiple disk heads"


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Wednesday, January 23, 2013 1:35 PM
  • Index and data in different file group and different storage-->  Database on this stageDo you have any specific example about waste of tome and money.

    Consider a typical OLTP workload where the most queries read/write only a few rows.  This results on purely random I/O to both data and index filegroups/files.  With random I/O, it is best to distribute I/O evenly over as many spindles as possible.  Spreading both data and indexes among all available spindles will achieve this.  In contrast, isolating data and indexes may result in an I/O imbalance and not maximize IOPS.

    A reporting workload may benefit by segregating data and indexes (perhaps with partitioning too) on different filegroups if the result isolates random from sequential I/O or allows parallel sequential operations on different storage devices.

    So there is no single answer to the question of proper file/filegroup planning and placement.  But there are some best practices that should be followed in nearly all databases. 

    • There is only one primary data file per database, which should always be named with the 'mdf' extension
    • All other data files should be named with the 'ndf' extension to denote secondary data files
    • Log file should be placed on separate storage

    For all but very small databases, I suggest that the primary mdf data file be the only file in the primary filegroup and no user objects should be created on primary.  Create a default user-defined filegroup for user objects.  Create other filegroups as needed for the specific situation at hand.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, January 23, 2013 1:42 PM
    Moderator
  • I agree with david that creating more filegroups are a waste of time especially if your sql servers are on virtual machines. I like the microsft model, just one filegroup for ease of maintenance. If you come from oracle, you get all these tablespaces which to me complicates things. Thong
    Thursday, January 24, 2013 10:26 AM
  • I agree with david that creating more filegroups are a waste of time especially if your sql servers are on virtual machines. I like the microsft model, just one filegroup for ease of maintenance. If you come from oracle, you get all these tablespaces which to me complicates things. Thong

    Please keep in mind that David has mentioned the disadvantage in conjunction with indexing strategies.
    For other scenarios a separation of filegroups can be a fantastic solution for performance boots.

    One of the really great benefits of usage of filegroups is the separation of "historacal data" and actual.
    Partitioning and partitioned views can use them perfectly!

    Furthermore a dedicated filegroup for "export" of historical data which will never change anymore can lead to a huge advantage of decreasing backup storage. I would create a backup of the historical data only once than set the FG to read only.

    The next step would be the change of the backup strategie to backup the active data only and the read only data never again!


    Uwe Ricken

    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)


    Thursday, January 24, 2013 11:13 AM
  • 1.Index on Different file Group advantageous.

    2.One of the really great benefits of usage of filegroups is the separation of "historacal data" and actual.
    Partitioning and partitioned views can use them perfectly!

    3."By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. Data and index information can then be read in parallel by the multiple disk heads"

    4.multiple ldf is nonsens because log will be written sequential-

    Still need more suggestion make this as combined answers.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Thursday, January 24, 2013 11:24 AM
  • Hi Bhavesh,

    I assume all has been said. All cons and pros have been discussed.
    Get more information by using google.

    Search for "advantage disadvantage filegroup"

    That will give you a whole bunch of information.


    Uwe Ricken

    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Thursday, January 24, 2013 11:49 AM
  • Hi Bhavesh,

    I found Uwe’s reply is an answer of your question and marked it as an answer, if you still have concern after reading the above replies and related links, please post it here.


    Allen Li
    TechNet Community Support

    Tuesday, January 29, 2013 2:45 AM
    Moderator
  • This is enough for my question.

    Thanks for wonderful discussion.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Tuesday, January 29, 2013 4:20 AM