Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDF and LDF locations


MDF and LDF locations

Author
Message
jansub07
jansub07
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 130
MDF and LDF in different locations make any sense in performance of SQL?
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
Yes and best practice and good for recovery



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
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


spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 13305
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
derek.colley
derek.colley
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 603
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 http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

okbangas
okbangas
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 1387
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
jansub07
jansub07
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 130
locations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 13305
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
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


bchernick
bchernick
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
Given a RAID composed of SSDs, would there still be a need to separate the MDFs and LDFs?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search