SQL Server - data files - How is write operation performed on data files

Hi,

   I would need some clarifications on how the write operations are performed on SQL Server data files.
   
   Say that we have only one file group (PRIMARY) with 10 data files inside. What I would need to know (I would needk if possiblek a valid Microsoft link next to the explanation) is, in case SQL Server needs to write 100 MB of data inside these data files how is the write operation done? (also considering that the 10 data files are not equal in size and that the free space inside each of the data files is also not equal in size).


Thanks in advance
rares_dumitrescuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SguzekCommented:
Hi,

The topic is quite big. Better start from reading "Physical Database Storage Design" http://technet.microsoft.com/en-gb/library/cc966414.aspx, you will find further references there.

Regardsm
Slawek
0
rares_dumitrescuAuthor Commented:
Hi,

    I read this one but it does not say how exactly data is written when you have more data files in the same file group.

    I have a very good understanding about the overall SQL Server storage picture but I need a more to the point explanation.

     As I know SQL Server should stripe the extents across the data files and theoretically what happens is that write operations are load balanced between the data files. Now I need to know the formula or the thinking behind this load balancing operation- how much weight a data file size and the free space inside inside has when SQL Server decides how much to write to one data file and how much to write to the other.

Thanks
0
SguzekCommented:
Hi,

No, extents are not stripped across data files. I mean single extent is never stripped across data files.

Load balancing works only when new extents are allocated. If SQL storage engine has to allocate one new extent  then it is allocated from the file with most free storage space. When it needs to allocate more than one extent then the first new extent goes to the data file with most free space, and second can go to the same file if after allocating first extent still the most space is in the same file.

As long as the free space will not be balanced between files all new extents will be allocated from the file with the biggest free storage space - so it is crucial to create database with data files equal in size.

FILEGROUP has nothing to do with physical data allocation as far as I now - it is used only for logical management, like backups etc.

Regards,
Slawek





0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rares_dumitrescuAuthor Commented:
Hi Slawek,

     This is exactly what I also suspect judging by the SQL2k5 I/O DMVs, do you also have some web-links for this info?

Thanks
0
SguzekCommented:
Hi,

I'm not aware of any web source where this was stated explicitely, I'm afraid.

Regards,
Slawek
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rares_dumitrescuAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage

From novice to tech pro — start learning today.