Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MDF and LDF locations Expand / Collapse
Author
Message
Posted Thursday, June 07, 2012 12:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:11 AM
Points: 7, Visits: 63
MDF and LDF in different locations make any sense in performance of SQL?
Post #1312317
Posted Thursday, June 07, 2012 1:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
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

Post #1312320
Posted Thursday, June 07, 2012 1:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
Depends on what those different locations are.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1312331
Posted Thursday, June 07, 2012 1:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:16 AM
Points: 4,930, Visits: 8,744
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1312335
Posted Thursday, June 07, 2012 1:58 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

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.
Post #1312341
Posted Thursday, June 07, 2012 2:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, February 24, 2014 6:09 AM
Points: 827, Visits: 1,360
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
Post #1312347
Posted Thursday, June 07, 2012 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:11 AM
Points: 7, Visits: 63
locations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..
Post #1312353
Posted Thursday, June 07, 2012 2:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:16 AM
Points: 4,930, Visits: 8,744
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1312356
Posted Thursday, June 07, 2012 3:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
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 2008, MVP
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

Post #1312367
Posted Monday, October 08, 2012 3:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 09, 2012 5:55 AM
Points: 2, Visits: 8
Given a RAID composed of SSDs, would there still be a need to separate the MDFs and LDFs?
Post #1370066
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse