InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 23:41:21 GMT20http://www.sqlservercentral.com/Forums/Topic680135-146-1.aspxHi 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!Fri, 20 Mar 2009 11:57:50 GMTfree_mascothttp://www.sqlservercentral.com/Forums/Topic680135-146-1.aspxI'm sorry for not being clear.Let me translate my previous post: 100% :-D 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.Fri, 20 Mar 2009 11:42:26 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic680135-146-1.aspxHello 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 :-DCheers!!!Fri, 20 Mar 2009 11:36:04 GMTfree_mascothttp://www.sqlservercentral.com/Forums/Topic680135-146-1.aspxI 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.Fri, 20 Mar 2009 03:28:31 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic680135-146-1.aspxWhen 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).AdiFri, 20 Mar 2009 01:09:55 GMTAdi Cohn-120898http://www.sqlservercentral.com/Forums/Topic680135-146-1.aspxHello,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.Thu, 19 Mar 2009 23:19:07 GMTfree_mascot