Data File Growth operations

Are any operations suspended during a data file growth? Or is it just a
performance hit?
It appears from a trace of mine that [during a data file growth] some (the
majority), but not all of my inserts are timing out. However, all updates
succeed, no updates time out. So, it seems I can partially answer my
question above, I think? Just looking for some clarification.
Message posted via http://www.sqlmonster.com
Any operation that depends on a new extent allocation will suspend. If you
are inserting on a non-full page, it will work. In-place updates will work.
New rows that trigger an extent allocation will suspend.
You get the picture.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:6bedf6ba67e1467f933788e8e0e9fad3@.SQLMonster.c om...
> Are any operations suspended during a data file growth? Or is it just a
> performance hit?
> It appears from a trace of mine that [during a data file growth] some (the
> majority), but not all of my inserts are timing out. However, all updates
> succeed, no updates time out. So, it seems I can partially answer my
> question above, I think? Just looking for some clarification.
> --
> Message posted via http://www.sqlmonster.com
|||Thanks Geoff. That is all clear except for when a 10% automatic growth
takes place.
My inserts all succeed in milliseconds outside of a data file growth.
During data file growth the applications initiating the inserts time out at
30 seconds. While some inserts are timing out at 30 seconds, during this
same time, the ones that are succeeding complete in milliseconds.
To try to clarify, if I have an approximate ratio of 10:1, where 10 equals
inserts that timeout for every 1 insert that succeeds, during a data file
growth ( the data file is set to automatically grow by 10% ) what is
happening is:
1. New extent allocations are taking place due to the 10% automatic growth.
2. The inserts that are succeeding are the ones going on a non-full page.
3. The inserts that are failing (timing out) are either triggering an
extent allocation, or are waiting for the extent allocation due to the 10%
automatic growth.
Am I clear in my understanding?
Message posted via http://www.sqlmonster.com
|||You have a good grasp of the concepts. The solution is to not rely on
automatic growth. I use it as an emergency safety valve only. I
pro-actively manage the data space used in each database/filegroup so my
production systems never have to pause while they grow. Also, 10% is OK for
about 60-70% of databases. Many databases are too small or too large for
percentage growth to be effective (Think about a 2MB database. Now think
about a 300GB database.) I prefer to set an absolute amount to grow by,
just in case. That way, I know exactly how long it should take for every
auto-grow even though I hope not to use it.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e5a3374e183c4893a73356c7666d88b3@.SQLMonster.c om...
> Thanks Geoff. That is all clear except for when a 10% automatic growth
> takes place.
> My inserts all succeed in milliseconds outside of a data file growth.
> During data file growth the applications initiating the inserts time out
at
> 30 seconds. While some inserts are timing out at 30 seconds, during this
> same time, the ones that are succeeding complete in milliseconds.
> To try to clarify, if I have an approximate ratio of 10:1, where 10 equals
> inserts that timeout for every 1 insert that succeeds, during a data file
> growth ( the data file is set to automatically grow by 10% ) what is
> happening is:
> 1. New extent allocations are taking place due to the 10% automatic
growth.
> 2. The inserts that are succeeding are the ones going on a non-full page.
> 3. The inserts that are failing (timing out) are either triggering an
> extent allocation, or are waiting for the extent allocation due to the 10%
> automatic growth.
> Am I clear in my understanding?
> --
> Message posted via http://www.sqlmonster.com
|||In light of this, I need further understanding.
When I manually expand the data file 1 GB, the expansion takes
approximately 2 minutes. When it automatically grows, and inserts are
timing out at the same time, the expansion takes approimately 20 minutes,
for 1 GB (the 10% equivalent).
How does all this we have been discussing equate to the increased expansion
time?
Message posted via http://www.sqlmonster.com
|||It may be that the client operation that triggered the specific expansion
has timed out. This may result in the expansion being rolled back. It
probably repeats until the expansio happens for a transaction that doesn't
time out. Whatever causes it, you now have an even stronger reason to avoid
auto-grow.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:bb7f723949324ec5886d55b0e29ea65a@.SQLMonster.c om...
> In light of this, I need further understanding.
> When I manually expand the data file 1 GB, the expansion takes
> approximately 2 minutes. When it automatically grows, and inserts are
> timing out at the same time, the expansion takes approimately 20 minutes,
> for 1 GB (the 10% equivalent).
> How does all this we have been discussing equate to the increased
expansion
> time?
> --
> Message posted via http://www.sqlmonster.com