InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:18:13 GMT20http://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxConsider windows page file, tempdb, db files, and log files as candidates to move.Also part of the picture is as others point out, what kind of IO patterns there are.And if this is a dedicated SQL server, or runs other applications.There also is a balance of cost to service level and recoverability, unless budget is of no concern.If you have a test environment, and a few extra old disks available (nothing fancy needed), you could set up and move the files.Then run some load tests.Lots of things to consider.I tend to get some local disk for some activity, but have some SAN partitiions carved out for those I need throughput and recoverability on.You could also search for some of the whitpapers on SQL Server Performance.They have some very good information, although these tend to be more for those where budget does not play a factor.I don't have the neeed to load 1 TB in less than 30 minutes.But it is interesting to see what it takes.Tue, 09 Oct 2012 06:27:16 GMTGreg Edwards-268690http://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxNot sure. So far this is theoretical. but I'll find out. For now assume reasonably modern equipment.Mon, 08 Oct 2012 17:27:08 GMTbchernickhttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxFor recoverability, definitely. For performance, maybe. What's the IOPS for the array? What's the required IOPS for the app?Mon, 08 Oct 2012 16:04:47 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxGiven a RAID composed of SSDs, would there still be a need to separate the MDFs and LDFs?Mon, 08 Oct 2012 15:02:32 GMTbchernickhttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspx[quote][b]jansub07 (6/7/2012)[/b][hr]locations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..[/quote]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.Thu, 07 Jun 2012 03:29:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspx[quote][b]jansub07 (6/7/2012)[/b][hr]locations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..[/quote]Same disk = no performance gain.Thu, 07 Jun 2012 02:52:57 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxlocations means different drives but same physical disk like c:\-MDF, D:\-LDF...How the performance exhibit difference..Thu, 07 Jun 2012 02:47:53 GMTjansub07http://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxEven separate spindles may not always be satisfactory, as the bus subsystem (eg the FC or iSCSI connection) may be the most limiting factor.Thu, 07 Jun 2012 02:17:04 GMTokbangashttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxCheck 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 ([url]http://www.brentozar.com/sql-server-training-videos/[/url]), specifically 'How to prove it's a SAN problem' and 'Performance 101' for some good advice about I/O-related performance problems.Thu, 07 Jun 2012 01:58:34 GMTderek.colleyhttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspx[quote][b]jansub07 (6/7/2012)[/b][hr]MDF and LDF in different locations make any sense in performance of SQL?[/quote]Generally speaking, yes, but depends what "locations" means.In order to increase performance, they must reside on different [u]physical[/u] 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 writeslog files --> mostly sequential writesSeparating those I/O patterns can improve performance and recoverability.Hope this helpsGianlucaThu, 07 Jun 2012 01:46:43 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxDepends on what those different locations are.Thu, 07 Jun 2012 01:36:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxYes and best practice and good for recoveryThu, 07 Jun 2012 01:01:27 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1312317-391-1.aspxMDF and LDF in different locations make any sense in performance of SQL?Thu, 07 Jun 2012 00:37:33 GMTjansub07