<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
MDF and LDF locations
13 posts, Page 1 of 2
1
2
»»
MDF and LDF locations
Rate Topic
Display Mode
Topic Options
Author
Message
jansub07
jansub07
Posted Thursday, June 07, 2012 12:37 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, April 22, 2013 7:30 AM
Points: 7,
Visits: 44
MDF and LDF in different locations make any sense in performance of SQL?
Post #1312317
anthony.green
anthony.green
Posted Thursday, June 07, 2012 1:01 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
GilaMonster
GilaMonster
Posted Thursday, June 07, 2012 1:36 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 37,677,
Visits: 29,932
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
spaghettidba
spaghettidba
Posted Thursday, June 07, 2012 1:46 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804,
Visits: 8,067
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
Get your two-cent-answer quickly
The Spaghetti DBA
Post #1312335
derek.colley
derek.colley
Posted Thursday, June 07, 2012 1:58 AM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:40 AM
Points: 496,
Visits: 583
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
okbangas
okbangas
Posted Thursday, June 07, 2012 2:17 AM
Right there with Babe
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:36 AM
Points: 721,
Visits: 1,330
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
jansub07
jansub07
Posted Thursday, June 07, 2012 2:47 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, April 22, 2013 7:30 AM
Points: 7,
Visits: 44
locations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..
Post #1312353
spaghettidba
spaghettidba
Posted Thursday, June 07, 2012 2:52 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804,
Visits: 8,067
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.
Get your two-cent-answer quickly
The Spaghetti DBA
Post #1312356
GilaMonster
GilaMonster
Posted Thursday, June 07, 2012 3:29 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 37,677,
Visits: 29,932
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
bchernick
bchernick
Posted Monday, October 08, 2012 3:02 PM
Forum 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 »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
