Post reply

MDF and LDF locations

  • June 7, 2012 at 12:37 am

    #257297

    MDF and LDF in different locations make any sense in performance of SQL?

  • June 7, 2012 at 1:01 am

    #1498011

    Yes and best practice and good for recovery

  • June 7, 2012 at 1:36 am

    #1498019

    Depends on what those different locations are.

    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

  • June 7, 2012 at 1:46 am

    #1498022

    jansub07 (6/7/2012)

    MDF and LDF in different locations make any sense in performance of SQL?

    Generally speaking, yes, but depends what "locations" means.

    In order to increase performance, they must reside on different physical disks. This means different spindles, not logical volumes on the same spindles.

    Data and log files are read and written with very different I/O patterns:

    data files --> mostly random reads and writes

    log files --> mostly sequential writes

    Separating those I/O patterns can improve performance and recoverability.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • June 7, 2012 at 1:58 am

    #1498028

    Check out sys.dm_io_virtual_file_stats (use parameters NULL,NULL) to get an idea of your current I/O performance since the last restart of SQL Server. If you can identify any bottlenecks here, double-check by checking wait statistics (scripts available on SSC i.e. from Glenn Berry) or use the DMVs for I/O related waits, and use perfmon too (Current Disk Queue Length is a good indicator together with a few others).

    Best practice is to split MDF and LDF into separate physical locations but sometimes this is not always possible, e.g. when using LUNs mapped to a SAN or when there aren't enough drives available. Diagnosing poor I/O will give you an indication of where the stress is and which databases need the files moving.

    Ideally you'd split tempdb out onto a different volume too.

    Check Brent Ozar's training videos page (http://www.brentozar.com/sql-server-training-videos/), specifically 'How to prove it's a SAN problem' and 'Performance 101' for some good advice about I/O-related performance problems.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • June 7, 2012 at 2:17 am

    #1498034

    Even separate spindles may not always be satisfactory, as the bus subsystem (eg the FC or iSCSI connection) may be the most limiting factor.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • June 7, 2012 at 2:47 am

    #1498039

    locations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..

  • June 7, 2012 at 2:52 am

    #1498041

    jansub07 (6/7/2012)

    locations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..

    Same disk = no performance gain.

    -- Gianluca Sartori

  • June 7, 2012 at 3:29 am

    #1498050

    jansub07 (6/7/2012)

    locations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..

    Different partitions of the same drive? No advantage (performance or recovery) whatsoever.

    That said, don't put data or log files on C drive. Windows gets very twitchy if the C drive fills up.

    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

  • October 8, 2012 at 3:02 pm

    #1546941

    Given a RAID composed of SSDs, would there still be a need to separate the MDFs and LDFs?

  • October 8, 2012 at 4:04 pm

    #1546965

    For recoverability, definitely. For performance, maybe. What's the IOPS for the array? What's the required IOPS for the app?

    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

  • October 8, 2012 at 5:27 pm

    #1546977

    Not sure. So far this is theoretical. but I'll find out. For now assume reasonably modern equipment.

  • October 9, 2012 at 6:27 am

    #1547192

    Consider windows page file, tempdb, db files, and log files as candidates to move.

    Also part of the picture is as others point out, what kind of IO patterns there are.

    And if this is a dedicated SQL server, or runs other applications.

    There also is a balance of cost to service level and recoverability, unless budget is of no concern.

    If you have a test environment, and a few extra old disks available (nothing fancy needed), you could set up and move the files.

    Then run some load tests.

    Lots of things to consider.

    I tend to get some local disk for some activity, but have some SAN partitiions carved out for those I need throughput and recoverability on.

    You could also search for some of the whitpapers on SQL Server Performance.

    They have some very good information, although these tend to be more for those where budget does not play a factor.

    I don't have the neeed to load 1 TB in less than 30 minutes.

    But it is interesting to see what it takes.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply