4

I'm not a server admin. So be gentle. But I was just at a conference and in one of the training classes the Instructor explained some SQL DBA best practices. One of which was to separate out Mdf,Ldf, and TempDB onto different drives to increase performance.

Now at our office we have a san. The Sys Admins created 3 san drives one for data, one for Logs, and one for TempDB.

My intuition tells me that was a wasted effort...was it?

I don't know alot of the details, but if you ask i'll try to fill in any specs needed to answer this question accurately.

enter image description here

MVCylon
  • 309
  • 2
  • 4
  • 13
  • Since you're not a server admin, nor a DBA, why not invest the effort required to figure this out for yourself? – adaptr Oct 01 '12 at 13:28
  • In addition to the answers below, what happens when the database sees an unprecedented, unpredictable spike in activity and the transaction logs fill up the disk? Better to separate them for resiliency reasons as well. – HopelessN00b Oct 01 '12 at 15:30

3 Answers3

5

Database files, transaction log files and temporary data files are usually placed on different volumes to reduce I/O contention between them, and this can also be extended further by creating multiple data files for each database, spreading them across even more volumes and telling SQL Server where specific tables and indexes should be stored; so, yes, this is a common practice for DBAs, and can indeed lead to significant performance gains.

It is also true than it can become completely meaningless when using a SAN; depending on how that SAN has been actually configured, different LUNs (volumes) can be mapped to any set of phsyical disks, RAID arrays and storage controllers, and thus the I/O contention between two LUNs can range anywhere from "completely isolated" to "effectively sharing the same disks". So, unless the DBA is working closely together with the storage admin, asking for different LUNs in order to spread database files between them can indeed become a completely wasted effort.

However, separating database files and transaction log files is considered a best practice not only for performance, but also for reliability: due to the transactional storage engine being used by SQL Server (and to similar engines used by almost any existing DBMS), log files can be used to replay transactions in a disaster recovery scenario, enabling the system to recover what happened after the last backup was taken. But, in order for this to work, you don't want to lose data files and log files at the same time, so it's better to at least place them on different volumes, even if there is no performance gain from this.

Massimo
  • 70,200
  • 57
  • 200
  • 323
3

The reason you separate the database files from the log files is because database access is random and log access is sequential. Best practice dictates that you don't mix those two I/O types on the same disk, regardless of how that disk is presented to the server (Local, DAS, iSCSI, etc.). So no, it wasn't a wasted effort.

As for tempdb, it seems to be Microsoft's recommendation that you separate that from the databases:

http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • 1
    That's the usual reason, and I totally agree with it. But the question is exactly about how much of this does actually matter when using a SAN, where there is not any direct link between LUNs and phyical disks... – Massimo Oct 01 '12 at 14:27
  • 1
    I see. So hopefully the storage admins are presenting the LUNs from disk arrays that have been provisioned according to I/O type and not all from the same array, otherwise you defeat the purpose of separating out the databases from the logs, from the tempdb. – joeqwerty Oct 01 '12 at 14:31
  • @joeqwerty If I click "Computer" right click on the SAN drive and right click "Properties" go to the Hardware tab, and view the list of Devices All say "LUN 0" You are saying they should say different LUN numbers? I'll add a screenshot to my post – MVCylon Oct 01 '12 at 14:39
  • @MVCylon, when using SAN volumes you can see almost any device description that comes to mind in a system's device list... and yet, this still wouldn't let you know (almost) anything at all about how the storage is actually configured, unless you ask the people administering it. – Massimo Oct 01 '12 at 14:47
  • It's not a completely wasted effort to use separate LUN's even if it's on the same RAID group. You still get better scheduling of SCSI reservations and caching. – pauska Oct 01 '12 at 14:49
0

The I/O on a disc for logs will only be sequential if it contains only one log file. Add another and you have random I/O.

Michael
  • 1