performance benefit of separating LOG and DATA path

Question

performance benefit of separating LOG and DATA path

inevercheckthis2002

SSCommitted

Points: 1819
More actions
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!

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

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

Seraj Alam-256815 SSC Eights! Points: 804 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 3

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

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