Be seen. Boost your question’s priority for more expert views and faster solutions
Solved
SQL Server - data files - How is write operation performed on data files
Posted on 2009-07-01
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
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
3-
3
6 Comments
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
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
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
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
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
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
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
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
Featured Post
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
The question appears often enough, how do I transfer my data from my old server to the new server while preserving file shares, share permissions, and NTFS permisions. Here are my tips for handling such a transfer.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting.
This w…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses
Course of the Month9 days, 1 hour left to enroll
595 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.