a proportional fill algorithm | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

a proportional fill algorithm

Hi We have 400 G db.
Vendor recomends RAID 5 or RAID 10 for mdf files and 4 evenly sized LUNs,
to keep mdf files on separate LUNs. Is that make sense for you? I thought that because LUN is logical drive of one physical array – that will not bring
me much benefits – vendor says that the way SQL talks to db will be different – more efficiant. Is there anything I can read about those benefits of putting 4 diff. mdf on evenly sized LUNs of the same RAID ? Thank you
I have searched the internet a bit and now understand the vendor ment "a proportional fill algorithm" Is that something new for sql 2005? My db has 4 mdf files but they are all different size ( autogrow disabled) Should I bring them all to the same size ? but they are already filled up differently For example :
1.mdf = 80 G(50g used + 30g free)
2.mdf= 30 G(10G used + 20 G free)
3.mdf = 3 G( 2.5g used + 0.5g free)
4.mdf = 15 g( 12 g used + 3 g free) Can I now fix the situation just by adding space to 2,3,4 mdf so they all are 80 G big? Thank you
when a SAN vendor talks about LUNs
they a storage unit that appears to your host OS (windows) as a single hard drive
it could actually be made up of 1,2 or almost any number of actual physical drive
you also have no idea how those drives are shared vendor rep are frequently seriously out of date with regard actual applications
at some point in time in the deep past (SQL 6.5?) SQL would performance better if the data were spread across multiple files, a threading issue the problem is, what ever you are told,
if there is not a detailed test report substaniating the statement,
be catious of it
it may be old, and nolonger relevent and/or the original correct statement with applicable conditions
may have become so twisted as to be stupid
assuming all your files are in the same file group
set the files to be the same
use a combination of shrink file and dbreindex to redistribute the data
I would love to agree with you …but I do find some articles regarding this proportional fill:
Please search for "proportional fill": http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx It talks about tempdb – I would make a conclusion that it means ONLY TEMPDB, but them I am finding this – please read #9: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx I guess my vendor is right??? My files are in the same filegroup.

try to actually read the two documents above
what is missing?
how about any meaningful performance data to support key assertions multiple files are not bad, but the rule of 0.25-1 per cpu is stupid
the number of files should be driven by the storage architecture all this is outlined in reasonable detail in my post on storage
hm – I see thank you for the clarification
the correct chain of logical /
the main points of the second article is:
1. to configure for performance this means:
2. multiple disk controllers and multiple racks of external storage due to the nature of raid controllers,
3. it necessary to have multiple LUNs in your storage system
meaning your data filegroup needs to be split into multiple files it so happens if you divide the number of CPUs by the number of files, the result falls in a certain range,
but this has no direct bearing on the original objective that your vendors jumps to a nonessential element should raise red flags
has he/she told you what level of performance you should be shooting for? Class, Seq Read, Random IOPS (<10ms / >20ms latency)
AA: 10GB/sec, 40K / 100K
A : 3GB/sec, 12K / 30K
B : 1000MB/sec, 4K / 10K
C : 300MB/sec, 1200 / 3K
D : 100MB/sec, 400 / 1,000
E : 30MB/sec, 200 / 300
F : <30MB/sec, <150 / <300
I guess in any case I will have to make all 4 mdf’s the same size. and then leave it and see if I can spread the data over 4 of them by shrinking and reindexing. I guess I am in trouble – that db is big and that will take a long time to do.
try increasing 3 & 4 to 30G then start a shrink file on 1 to 30G over the weekend (assuming you are a M-F business) this is why brute force capability is so important in the storage
lots of people complain when i tell them to get 4 racks of storage for a 100GB db
no we are 24/7 business. But Shrink is on line operation – It should be OK to run it in production?

dbcc shrinkfile does not lock the db
but i imagine it can be highly disruptive that is why it is very important to plan you data layout carefully you might get lucky on a shrink to 50G is all the free space is at the end
but a shrink beyond that would require data movement i always tell my customers for true worldwide user base
always split into 3 databases
1 for US, 1 for Europe, 1 for Asia
then you can do maintenance otherwise, you are screwed
why don’t you just increase 3&4 to 30G
shrink 1 to 70G initially,
then after every reindex, shrink another 5G
then atleast its not that disruptive
ok – I’ll do that – thank you very much for all your help
]]>