Post reply

performance benefit of separating LOG and DATA path

  • April 16, 2013 at 1:51 pm

    #292536

    SQL Server 2008 R2 on Windows Server 2008 R2.

    With a Simple recovery model, is there a performance benefit to creating separate LOG and DATA paths?

    Thanks!

  • April 16, 2013 at 3:05 pm

    #1607056

    I assume you mean diffferent drive instead of different path. Further I assume, the drives come from different physical disk / LUNS.

    Yes it will improve even for simple recovery. The quantum will be based on size of your transaction.

    Seraj

  • April 16, 2013 at 3:08 pm

    #1607057

    Maybe. Depends whether those are separate IO paths or not, whether the DB is IO bottlenecked or not.

    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

  • April 16, 2013 at 3:19 pm

    #1607060

    I am referring to using separate LUNs (on different SAN RAID groups, that is, different physical disk arrays) for DATA and LOG files, with a simple recovery model.

    Is that recommended, or does the simple recovery model make it a moot point?

    Thanks!

  • April 16, 2013 at 3:21 pm

    #1607062

    Recovery model has nothing to do with it, the log is used almost the same (baring only minimally logged operations) in all three recovery models.

    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

  • April 16, 2013 at 3:55 pm

    #1607073

    I believe the confusion here lies in what is the simple recovery model.

    Everything in SQL Server is logged. The Simple Recovery model merely allows for re-usage of the log file without backups to preserve it for point in time recovery.

    The log will still be used at the same volume of writes as bulk.

    Edit: ^ As Gail already said. 😉

    - 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

Viewing 6 posts - 1 through 5 (of 5 total)

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