Data/Log - Separating them

Question

Data/Log - Separating them

PhilipC

Hall of Fame

Points: 3124
More actions
June 5, 2011 at 4:49 pm

#238707

I'm not much of a disk expert at all, and after requesting a new server by the SAN administrator for split Data and Log drives he came back with this.
"The ‘Array’ is presented via a NetApp LUN. This data is distributed over 32 physical spindles. Presenting a separate LUN would show no performance gains."
Does this make sense? I always thought it was best practice to seperate Data and Log files.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Samantha J SSC Veteran Points: 212 More actions · Answer 1

Heya!

A LUN is just like a disk partition, when it comes to a SAN environment normally the bottle neck is at the IO going from the SAN to the server.

So say I had 10 - 1 TB disks, my SAN would say (You have 10 terrabyte... Or 5 if I raided it) And from there I would say "I need 100 gig here in a LUN for my AD environment)

So when it comes to putting the DB/Log files on different LUNs the performance would be the same as if you partitioned a local disk - I wouldn't stress.

I hope this helps!!

Sam

PhilipC Hall of Fame Points: 3124 More actions · Answer 2

PhilipC

Hall of Fame

Points: 3124

June 5, 2011 at 7:29 pm

#1334167

Thanks very much

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

PhilipC (6/5/2011)
I'm not much of a disk expert at all, and after requesting a new server by the SAN administrator for split Data and Log drives he came back with this.
"The ‘Array’ is presented via a NetApp LUN. This data is distributed over 32 physical spindles. Presenting a separate LUN would show no performance gains."
Does this make sense? I always thought it was best practice to seperate Data and Log files.

If all 32 disks are set up as a single storage group (likely RAIDDP given that it is NetApp) then multiple drives won't be helpful because they are all working together under the covers already. There are goods and bads of this arrangement, but it is pretty common at the low end of the storage universe.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 4

Performance would be same on different LUN if same group,how many HBA in a Server,it must be 2,you should go for Multi pathing to increase the performance,If you are using Window Server 2008 then go for MPIO,this will use your second HBA,there are several algorithm to balance the load of IO and use disk alignment at the time of format of a disk

My Blog
www.aureus-salah.com

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 5

The way I understood it was that to get the best performance for SQL out of a SAN you would want to split Data and Logs on separate groups of disks so 2 physical LUNs if you like. Otherwise you will be getting (as said above) the same performance as on a physical machine with logical partitions i.e. no gain i.e. rubbish performance. Which rather than "I wouldn't stress." I would be thinking "Stress on its way" if you have a heavy load sql server.

Separate LUNs, and a HBA for each.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Shark Energy (6/6/2011)
The way I understood it was that to get the best performance for SQL out of a SAN you would want to split Data and Logs on separate groups of disks so 2 physical LUNs if you like. Otherwise you will be getting (as said above) the same performance as on a physical machine with logical partitions i.e. no gain i.e. rubbish performance. Which rather than "I wouldn't stress." I would be thinking "Stress on its way" if you have a heavy load sql server.
Separate LUNs, and a HBA for each.

Not quite true - it really depends on the SAN, the fabric, the head ends, and a whole lot of other details.

If your SAN has a lot of cache available, whether or not your data files and log files are actually sharing physical spindles might not matter. Again, it depends on a lot of other factors.

With that said - I prefer separating out to multiple LUNs for management purposes regardless of whether or not there are performance gains. For example, by separating out tempdb to its own LUN - if you have a process that grows the files to fill the LUN, it isn't going to affect the log or data files ability to grow.

It is also easier to manage because tempdb and log LUNs should stay fairly static. These should not grow unless their is an increase in the utilization of the system. Whereas, your data and backup LUNs will grow on a regular basis.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 7

Yeah true. I have a tendency to judge things on my real life experience and I've had system admin's rave about their latest SAN and "no need to split log and data" and seen terrible performance on a par with the physical server it used to be on whereas on the other side of the coin we have one super SAN with great spec and 4 separate physical LUNs and it runs like a dream.

I'd suggest you do some benchmarks on this new system to gather a good feel for what they have provided you, perhaps with a test db that you can replicate on another machine to see the difference.

PhilipC Hall of Fame Points: 3124 More actions · Answer 8

Thanks for the further thoughts guys, much appreciated 😎