Solved

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

Posted on 2009-07-01
6
388 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:rares_dumitrescu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 2

Expert Comment

by:Sguzek
ID: 24753950
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
 

Author Comment

by:rares_dumitrescu
ID: 24754159
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
 
LVL 2

Expert Comment

by:Sguzek
ID: 24755126
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rares_dumitrescu
ID: 24799005
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
 
LVL 2

Accepted Solution

by:
Sguzek earned 500 total points
ID: 24813548
Hi,

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

Regards,
Slawek
0
 

Author Comment

by:rares_dumitrescu
ID: 24817534
thanks
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Each year, investment in cloud platforms grows more than 20% (https://www.immun.io/hubfs/Immunio_2016/Content/Marketing/Cloud-Security-Report-2016.pdf?submissionGuid=a8d80a00-6fee-4b85-81db-a4e28f681762) as an increasing number of companies begin to…
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question