March 19, 2009 at 11:19 pm
Hello,
I would like to understand the internal michanism of the sql server for Datafile growth.
If datafile set to autogrow with unristricted file growth then SQL Server internally increase the size of the datafile till the disk is not full. Now my question is what is the internal threshold SQL Server is using to check the datafile size and how it grows? i.e. SQL Server will check the internally unallocated space for 95% or 98% or 90%; before increasing the datafile? How SQL Server internally calculating unallocated space?
I am more concern about the what is the threshold value SQL Server will use?
Hope somebody may know the internal things of SQL Server and may help.
Thank you in advance.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 20, 2009 at 1:09 am
When you create the database, you can decide how much the file should be increased. If you use the GUI the default is 10%. I have to admit that I don’t know how much the default is when you use create database statement and omit the part that tells how to increment each file. In any case this can also be modified and in my opinion it should be modified. If I have a database that is 500GB I wouldn’t want it to get 50GB in the middle of transaction. Most of the time I like to set my file growth to 200MB and monitor it so it wouldn’t get to the situation that it has to use automatic grow. In order to modify it you can run alter database statement (see more details in BOL) or use the GUI (right click on the database, select options from the popup menu and then use the files tab).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 20, 2009 at 3:28 am
I do not think the information you are asking for is public domain but experience suggests there is not such a threshold.
The most likely scenario is that SQL Server would extend a datafile during the process of extending a segment meaning, if at the time SQL Server needs to allocate space the operation fails because of no more available space then SQL Server appears to check the autoextent option, if set to true then SQL Server extends the datafile as specified then retries allocating the space needed to extend whatever segment was working on.
Please correct me if I'm wrong.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 20, 2009 at 11:36 am
Hello Paul,
I guess operation may not fail in most of the case but yes it might fail due to unavailability of the space. I guess mostly it get delayed and put on hold unless and untill sql server get the datafile extends.
But what I am looking for is more in depth and internal michanism. Like when insert is happening and page split happen and sql server do not found any space it will extends the datafile. But how sql server make decission to grow the datafile? there might be something like when datafile reaches 95% or 99% it should grow?
Still any indepth knowledge are welcome 😀
Cheers!!!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 20, 2009 at 11:42 am
I'm sorry for not being clear.
Let me translate my previous post: 100% 😀
SQL Server would extend a datafile when space is needed and no space is availabe in any available datafile. That's why you don't want to set a small extent parameter 'cause overhead would kill performance.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 20, 2009 at 11:57 am
Hi Paul,
I guess I am failed to express what I want.
I do understand that in addition to the performance; if small extent is set to autogrow i.e. 250 mb or 500 mb than as autogrow expand the file fregmentation may also increase.
But anyway I am looking some different answer. Like how sql server internally manage the autogrow? I am curious to know what internally happening under the hood(SQL Server) step by stape? e.g. there are lots of in-depth article available with diagram regarding to know how page split happen or how reindxing is happening internally in sql server.
Have a Great weekend!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply