Will separation of data file and log file of SQL sever help if all are on the SAN? From performance perspective not management.
Posted by: Denny Cherry
EMC, IBM, SQL Server, Storage
Most likely yes, but it really depends on the storage array and the storage array configuration. If the array is a shared everything array like an IBM XIV then their may not be any benefit to breaking everything onto separate disks as each LUN is spread over every spindle in the device. However if you are using an EMC array where you manually configure which LUNs are created on which disks then it will definitely matter.
One thing to remember which I can’t stress enough if that if you are writing directly to disk in a SAN environment then there is probably something wrong, as the array has a ton of cache which would be accepting all the writes, which are then destaged to the disk later on.
Now even when using a system like an IBM XIV array having multiple LUNs can give you some benefits as Windows will now have multiple disks queues and multiple paths over which to send the data.
Basically like everything else, you’ll need to test what works best in your environment, and test the other options occasionally to ensure that what you are using is still the best configuration later on.