Deferred drop

9 Sep 2006 7:49 PM

Dean

Hi,

I need some clarification.

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.mspx - 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,

Dean

10 Sep 2006 2:15 AM

Stu

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

Dean wrote:
Show quote

> Hi,
>
> I need some clarification.
>
> 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.mspx - 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,
>
> Dean
> ------=_NextPart_000_00BA_01C6D459.E1D41BB0
> Content-Type: text/html; charset=iso-8859-2
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2109
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-2">
> <META content="MSHTML 6.00.2900.2963" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=#ffffff>
> <DIV><FONT face=Arial size=2>Hi,</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>I need some clarification. </FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>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 <A
> onclick="return top.js.OpenExtLink(window,event,this)"
> href="http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx"
> target=_blank>http://www.microsoft.com<WBR>/technet/prodtechnol/sql/2005<WBR>/workingwithtempdb.mspx</A>
> - 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.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>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?</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Thanks,</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Dean</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_00BA_01C6D459.E1D41BB0--

10 Sep 2006 1:14 PM

Andrew J. Kelly

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.

--
Andrew J. Kelly SQL MVP

"Dean" <dvitner@nospam.gmail.com> wrote in message news:u6hYViE1GHA.4816@TK2MSFTNGP06.phx.gbl...
Hi,

I need some clarification.

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.mspx - 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,

Dean

11 Sep 2006 5:46 PM

Dean

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 = 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 = current_timestamp

return

go

declare @exit_time_expl datetime, @exit_time_impl datetime

exec explicit_drop @pages=10000, @exit_time=@exit_time_expl output
select datediff (ms, @exit_time_expl, current_timestamp)

exec implicit_drop @pages=10000, @exit_time=@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

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:%23CEPUsN1GHA.4976@TK2MSFTNGP02.phx.gbl...

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.

--
Andrew J. Kelly SQL MVP

"Dean" <dvitner@nospam.gmail.com> wrote in message news:u6hYViE1GHA.4816@TK2MSFTNGP06.phx.gbl...

    Hi,

    I need some clarification.

    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.mspx - 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,

    Dean