0

We have a dilemma…
We have all of our MDF and LDF files in the same…\192.168.6.22\d$\Program Files\Microsoft SQL Server\DATA\Production...folder.
We have all of our TRN and BAK files on the…\192.168.6.22\r$...drive.
We have read several articles stating that our Tempdb.MDF+LDF files need to be relocated to a different drive.
We have read several articles stating that our Userdb.LDF files also need to be relocated to a different drive.

http://www.brentozar.com/archive/2009/02/when-should-you-put-data-and-logs-on-the-same-drive/
http://www.tech-recipes.com/rx/19178/sql-server-20082008-r2-move-tempdb-to-improve-performance/

  1. This system is in a virtual environment, so it’s more than likely all of these drives are all on the same array anyway, and if that’s the case would relocation make any difference?

  2. If they are on separate arrays, or if it will make a difference, then should there be four separate locations or five, and do each of these need their own array?.:
    a. One for the MDF files; (1)
    b. One for the LDF files; (2)
    c. One for the BAK and TRN files; (3)
    d. One for the Tempdb.MDF+LDF files; (4) or…
    …One for the Tempdb.MDF file, and another for the Tempdb.LDF file; (5)

Improve this question
3
  • 1
    Are you having performance issues? If you are, have you looked at root causes, wait stats, etc? It is fairly likely that changing the locations of the files won't make all that much difference if the performance issue lies elsewhere. Also, you're storing these files across the network???
    – Hannah Vernon ♦
    May 22, 2015 at 16:21
  • 1
    You mention that this is "in a virtual environment, so it’s more than likely all of these drives are all on the same array anyway" but I think that's a rather important fact. What's your hypervisor? How is the host connected to the storage (fiber channel? iscsi?)? How is the storage provisioned within the hypervisor (e.g. do you have multiple guests sharing one big storage container? do you have separate storage allocated to each guest? if vmware, are they vmdk? are you using raw device mapping?)? how many guests are on the host? etc.
    – swasheck
    May 22, 2015 at 16:35
  • "all on the same array" Then nothing what you do impacts IO performance. It matters little at what physical offset an IO is issued. What matters is what physical drive it eventually hits.
    – usr
    May 23, 2015 at 16:05

1 Answer 1

Reset to default
1

A few notes here:

  1. You definetely have to separate the files logically using different partitions (I recommend separate partitions for: system, data files (mdf/ndf), transaction log files (ldf) and tempdb files (mdf/ndf). Where you put the tempdb transaction log file is up to you (tempdb drive or log drive), I'd go with log drive.
  2. Backup files should not be on the located same device and especially the same VM (I recommend a different SAN/NAS).
  3. Placing your data, log and tempdb files on different drives is mostly a way of improving performance. Transaction log is heavily written and is probably the slowest component of SQL Server. The second bottleneck is by design the tempdb database. If these 2 components are placed on different drives (especially if they reside on SSD/RAID10 disks), the performance will be definitely better.
  4. You can keep the transaction log and tempdb on the same array as the user database files if you aren’t experiencing performance problems (at least not yet). I would look at the sys.dm_io_virtual_file_stats DMV to verify if there really is a problem with IO performance.
  5. If you want to move these files to a different disk in a virtual environment you can create a separate virtual disk for every partition you want to move and assign these disks to different LUNs in the array.
Improve this answer
2
  • Also my colleagues usually create an extra partition for pagefile.sys but I guess it can be placed on C:\ if its only dedicated to system. What is your opinion on that approach?
    – BuahahaXD
    May 22, 2015 at 22:43
  • place pagefile.sys on a seperate partition is typically for virtual machines, so that snapshots of the OS drive can be taken without having the pagefile in there. Leave that up to your sysadmin, but if it's a physical server then it won't matter.
    – Jonathan Fite
    Oct 23, 2015 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.