sql server programming: Deferred drop -- sql server programming

Re: Deferred drop

Stu
9/9/2006 7:15:47 PM

sql server programming: Hey Dean,

I'm new to this topic myself, so you may already know much more than
me, but it appears that deferred drops occur on tables and indexes that
require more than 128 extants.

"Deferred drop operations do not release allocated space immediately,
and they introduce additional overhead costs in the Database Engine.
Therefore, tables and indexes that use 128 or fewer extents are
dropped, truncated, and rebuilt just like in SQL Server 2000. This
means both the logical and physical phases occur before the transaction
commits."

Not sure if that helps or not, but it at least bumps this post up a
notch :)

Stu

[quoted text, click to view]

Deferred drop

Dean
9/9/2006 9:49:53 PM

Hi,

I need some clarification.=20

A few days ago, at our UG meeting, we had a discussion about some =
less-known new features in SQL Server 2005. Among these, deferred drop =
of large temporary objects - as described in =
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.m=
spx - was mentioned. An MVP, who made the presentation, stated that this =
deferred drop happens only when the object goes out of scope, and if =
it's not explicitly dropped. It somehow didn't sound right (being told, =
in a way, that cleaning up after myself is not only unnecessary, but =
actually a bad thing performace-wise), so I did some testing, and it =
seems that the drop is deferred regardless of how the object was dropped =
- explicitly (with DROP TABLE) or implicitly (when going out of scope). =
Of course, I might be wrong, and my testing method could be incorrect.

Could someone please give the definitive answer to this - is it a =
prerequisite for the deferred drop to happen that the object is not =
dropped explicitly?

Thanks,

Re: Deferred drop

Andrew J. Kelly
9/10/2006 9:14:26 AM

I think that it is always up to the engine when and how it does the =
drop. I can't say I have seen the logic publicly posted anywhere and as =
with most things of that nature it is subject to change with editions or =
even service packs.=20

--=20
Andrew J. Kelly SQL MVP
=20
[quoted text, click to view]
Hi,

I need some clarification.=20

A few days ago, at our UG meeting, we had a discussion about some =
less-known new features in SQL Server 2005. Among these, deferred drop =
of large temporary objects - as described in =
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.m=
spx - was mentioned. An MVP, who made the presentation, stated that this =
deferred drop happens only when the object goes out of scope, and if =
it's not explicitly dropped. It somehow didn't sound right (being told, =
in a way, that cleaning up after myself is not only unnecessary, but =
actually a bad thing performace-wise), so I did some testing, and it =
seems that the drop is deferred regardless of how the object was dropped =
- explicitly (with DROP TABLE) or implicitly (when going out of scope). =
Of course, I might be wrong, and my testing method could be incorrect.

Could someone please give the definitive answer to this - is it a =
prerequisite for the deferred drop to happen that the object is not =
dropped explicitly?

Thanks,

Re: Deferred drop

Dean
9/11/2006 7:46:24 PM

Thanks for your answers, Stu and Andrew. Not exactly the answers I was =
looking for, but looks like that's all there is to it :)

BTW, here's the code I used to check the effect of explicit drop on =
deffered drop:

create procedure explicit_drop
@pages int,
@exit_time datetime output
as

select replicate(convert(varchar(36), newid()), 200) as fat_col
into #temp
from numbers
where number < @pages

set @exit_time =3D current_timestamp

drop table #temp

return

go

create procedure implicit_drop
@pages int,
@exit_time datetime output
as

select replicate(convert(varchar(36), newid()), 200) as fat_col
into #temp
from numbers
where number < @pages

set @exit_time =3D current_timestamp

return

go

declare @exit_time_expl datetime, @exit_time_impl datetime

exec explicit_drop @pages=3D10000, @exit_time=3D@exit_time_expl output
select datediff (ms, @exit_time_expl, current_timestamp)

exec implicit_drop @pages=3D10000, @exit_time=3D@exit_time_impl output
select datediff (ms, @exit_time_impl, current_timestamp)

I would be grateful if someone else could take a look at this and =
comment.

Dean

[quoted text, click to view]
I think that it is always up to the engine when and how it does the =
drop. I can't say I have seen the logic publicly posted anywhere and as =
with most things of that nature it is subject to change with editions or =
even service packs.=20

--=20
Andrew J. Kelly SQL MVP
=20
[quoted text, click to view]
Hi,

I need some clarification.=20

A few days ago, at our UG meeting, we had a discussion about some =
less-known new features in SQL Server 2005. Among these, deferred drop =
of large temporary objects - as described in =
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.m=
spx - was mentioned. An MVP, who made the presentation, stated that this =
deferred drop happens only when the object goes out of scope, and if =
it's not explicitly dropped. It somehow didn't sound right (being told, =
in a way, that cleaning up after myself is not only unnecessary, but =
actually a bad thing performace-wise), so I did some testing, and it =
seems that the drop is deferred regardless of how the object was dropped =
- explicitly (with DROP TABLE) or implicitly (when going out of scope). =
Of course, I might be wrong, and my testing method could be incorrect.

Could someone please give the definitive answer to this - is it a =
prerequisite for the deferred drop to happen that the object is not =
dropped explicitly?

Thanks,

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.