Sql-server – SQL Server – Separating data, log, and TempDB files on a SAN

performancesansql serverstorage

I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) from the SAN administrator, but given the new vendor's recommendation, is there any point in creating separate LUNs? Or would I see the same performance if everything was in one LUN since it would span all disks anyway?

Great article here, but doesn't quite address my exact situation:
http://www.brentozar.com/archive/2008/08/sql-server-on-a-san-dedicated-or-shared-drives/

Best Answer

One thing to consider is that the log files are sequential writes where as the data files are non sequential. That is one of the reasons for separate LUNs. Log files write faster if they are on their own LUN because the spindles don't have to skip around, just write sequential. If you add in a data file then the spindles have to skip around and you lose some performance. I'm hoping I got the right terminology there as I'm not all that familiar with SANs themselves. The idea behind it should be however.

Frequently vendor recommendations are wrong when it comes to SQL Server. Just because SQL Server has different needs than most applications that use a SAN.

Related Solutions

Sql-server – Optimising a Database Shrink (in terms of speed and reducing fragmentation)

A note first:

When you rebuild indexes, you need roughly free space of roughly 1.2 times the largest table. So a 15 GB table in that 20 GB used requires 18 GB free space. This sets a minimum database file size.

Personally, for OLTP databases, anything over 50%-60% used is a bit of red flag. So anything less then 35-40GB for for 20GB data would be worrying.

To decide whether you need downtime and filegroups and all the hassle, have you:

rebuilt all indexes (ALTER INDEX ALL .. REBUILD) to see where you are?
run DBCC SHRINKFILE with TRUNCATEONLY to free space only with no data movements?
checked file size after these steps?

One thing to look at would be NTFS fragmentation too after all this. YOu can stop SQL Server and defrag the volumes to get the MDFs and LDFs contiguous again. Of course, when they grow again you'll get fragmentation: which merits having generous file sizes to start with (50-60% used maximum).

Otherwise, your idea is sound to use different spindles to manage IO. Note the file size and NTFS stuff I mentioned above too.

Sql-server – I/O requests taking longer than 15 seconds

Note that all servers use the same NICs - Broadcom 5709Cs with up-to-date drivers. The servers themselves are Dell R610's.

Kyle Brandt has an opinion on Broadcom network cards which echoes my own (repeated) experience.

Broadcom, Die Mutha

My problems have always been associated with TCP Offload features and in 99% of cases disabling or switching to a-n-other network card has resolved the symptoms. One client that (as in your case) uses Dell servers, always orders separate Intel NICs and disables the on-board Broadcom cards on build.

As described in this MSDN blog post, I would start with disabling in the OS with:

netsh int ip set chimney DISABLED

IIRC it may be necessary to disable the features at the card driver level in some circumstances, it certainly won't hurt to do so.

Best Answer

Related Solutions

Sql-server – Optimising a Database Shrink (in terms of speed and reducing fragmentation)

Sql-server – I/O requests taking longer than 15 seconds

Related Question