SQL Server Performance Forum – Threads Archive
Using Files and Filegroups in SAN environmentI have a question regarding to how to manage files and filegroups in SAN environment. From what I learned in books and articles like the following link, I know one should separate data files from log file, separate sys db files from application db files, etc. http://www.sql-server-performance.com/ac_filegroup_performance.asp However, one of my client insists that because they have the fancy super SAN, it is not a problem just throw one huge database file and one log file into one logical disk. The SAN will manage the IO easily without the headache of trying to separate them. This article seems suggest the same thing: http://www.netapp.com/ftp/sql-server-mag-article-1-2006.pdf Since I have little knowledge about SAN, I wonder if this is true? Are there any resources that I can look up for more info.
what log write rate do you need to support
most apps only need 100 tx/sec, so a log write latency of 5ms is ok, and the data+log is ok
other apps require 1000-5000 tx/sec, so log write latency needs to be 1ms to 0.2ms
in which case it is not ok set up a while loop to insert a single row into each of several tables,
see what the insert rate is, ie, log writes/sec most of the time, when someone has a fancy san, the disk performance sucks
My thinking it that even though SAN can load-balacing the I/O among disks, having a specific write-efficient LUN (logical unit number) for log writing is probably better than none. Anyone else have any comment?
Just use common sence and split your filelocations ! With Windows2003 you can define "Mounted drives", so you only need one driveletter, but physicaly the mounted drive is on a different location (LUN).
This way your mounted drives can have different characteristics, altough available through one driveletter.