Cairbre (7/9/2009)
I've been told by our architect/infrastructure guys that adding more LUNs will help.
Hmmm, yes. Pushing more I\O queues down to the disks will likely just saturate them, then your performance will really suck and SQL Server will start throwing regular "Slow I\O taking longer 15 seconds" messages.
Also, adding all those files will now increase the management complexity. You suddenly have 4 times the amount of files to look after.
I've worked with many systems that have 1 data file and 1 log file some databases as large as 400Gb or more.
Cairbre (7/9/2009)
Ideally we want the 4 data files to be the same size to facilitate disk management.
No, they must be the same size and growth for proportionate fill to work properly.
Are you planning multiple filegroups for the multiple files or are they going to reside in the primary filegroup?
You have databases already deployed so let's take one database as an example. Say it has a 15GB data file in the primary filegroup with 4GB free space. You add 3 new files 15GB each all with default growth sizes. Will the proportionate fill work efficiently?
No, one file is already 60-70% full so more I\O will be directed to the emptier files, even with the growth rates set the same you still have more I\O directed to certain files, that's not what i call evenly spreading I\O 😉
Cairbre (7/9/2009)
Also, a quick question on proportional fill. Would I need to grow all 4 data files at the same time to maintain proportional fill in the event they fill up? My understanding is that SQL only grows 1 file at a time, thus proportional fill is lost as soon as all 4 files fill up and SQL only grows the one.
Yes the fils grow one at a time, as theyre written to. One file will grow before the others but the rest will grow at the rate specified (which should be the dsame for all of them) as they're written to. There is a trace flag you can use to force the filegroups in unison.
george sibbald (7/10/2009)
sounds to me like the basic problem is the fact the sql server i/o is shared with i/o from other types of servers. the i/o patterns for SQL are radically different to say a file server and the two should not go through the same i/o channels.you would get the biggest bang for your buck by seperating them out. that would probably give you improvements in SQL performance as well. I don't think adding more luns if the i/o is still mixed is the best option.
+1
i dont think more LUNs are the answer.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉