dbcc freeprocache/dropcleanbuffers - please help

sql server 2000 Enterprise sp3a
I am running a loop in Query Analyzer as follows (pseudo coded) for some
testing
that I'm doing.
declare @.var int, @.id int
set @.var = 1
while (@.var < 4)
begin
begin tran
dbcc freeproccache
dbcc dropcleanbuffers
print 'start delete at : ' + cast (getdate() as varchar)
set @.id = 10
delete from mytable where @.id = 10
print 'end delete at : ' + cast (getdate() as varchar)
rollback tran
set @.var = @.var + 1
end
I'm getting weird execution times for each delete. For instance,
the first pass shows me an execution time of around 500ms which
I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
3ms. I used set statistics io on to check the reads and the number
of physical reads drops. I don't know why the dbcc commands
aren't working. Is it the transaction? Any ideas? There is no
other activity on the server... it's my personal box. Please help.You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word "clean", hence adding a
checkpoint will result in all pages clean in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> sql server 2000 Enterprise sp3a
> I am running a loop in Query Analyzer as follows (pseudo coded) for some
> testing
> that I'm doing.
>
> declare @.var int, @.id int
> set @.var = 1
> while (@.var < 4)
> begin
> begin tran
> dbcc freeproccache
> dbcc dropcleanbuffers
> print 'start delete at : ' + cast (getdate() as varchar)
> set @.id = 10
> delete from mytable where @.id = 10
> print 'end delete at : ' + cast (getdate() as varchar)
> rollback tran
> set @.var = @.var + 1
> end
>
> I'm getting weird execution times for each delete. For instance,
> the first pass shows me an execution time of around 500ms which
> I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> 3ms. I used set statistics io on to check the reads and the number
> of physical reads drops. I don't know why the dbcc commands
> aren't working. Is it the transaction? Any ideas? There is no
> other activity on the server... it's my personal box. Please help.
>|||Hi Tibor
Thank you
I ended up taking the transaction out (the begin transaction and rollback).
What I did instead
- inserted the rows into a temp table
- performed my delete
- re-inserted the rows
I repeated the delete/re-insert within the loop
When I did this, my problem went away. What do you think the transaction
was doing or not
doing? Would it be a log cache issue?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6ZDWR72GHA.4632@.TK2MSFTNGP03.phx.gbl...
> You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word
"clean", hence adding a
> checkpoint will result in all pages clean in the database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> > sql server 2000 Enterprise sp3a
> >
> > I am running a loop in Query Analyzer as follows (pseudo coded) for some
> > testing
> > that I'm doing.
> >
> >
> > declare @.var int, @.id int
> >
> > set @.var = 1
> > while (@.var < 4)
> > begin
> >
> > begin tran
> >
> > dbcc freeproccache
> > dbcc dropcleanbuffers
> >
> > print 'start delete at : ' + cast (getdate() as varchar)
> > set @.id = 10
> > delete from mytable where @.id = 10
> > print 'end delete at : ' + cast (getdate() as varchar)
> >
> > rollback tran
> >
> > set @.var = @.var + 1
> > end
> >
> >
> > I'm getting weird execution times for each delete. For instance,
> > the first pass shows me an execution time of around 500ms which
> > I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> > 3ms. I used set statistics io on to check the reads and the number
> > of physical reads drops. I don't know why the dbcc commands
> > aren't working. Is it the transaction? Any ideas? There is no
> > other activity on the server... it's my personal box. Please help.
> >
> >
>

Dbcc Checkdb

How often do you run it in your shop?

I'm seeing (from the new SQL Best Practices Analyzer) that MS recommends that it be done once every two weeks on SQL 2005.

I have never been in the habit of running it in production. I have never experienced database corruption in any form.

Just curious.

Regards,

hmscotthere i run it every night on every database, but our databases are relatively small. In larger environments, I typically make it part of the Sunday night maintenance.

corruption does happen. i had checkdb failing on one database once and the table it was failing on had more rows in it than was being returned by a SELECT * FROM. It was a small lookup table on a development server, so I just rebuilt it, but it was weird. You could see the rows in the EM but not by querying from the QA.|||Nightly, on ALL databases, ALL environments, including system DB's. The sproc also shoots out a nice big red-letter email if it finds anything. (I can post it here, or PM it to you, if you want to see it. I'm a horrible coder, but feel free to bend, fold, or mutilate it how you see fit.)

If you don't want to run it in production, I'd recommend restoring some prod db's to another server, and running it there. I do that a lot, too.

If you haven't seen it already, Paul Randal has a nice presentation online all about DB corruption best practices. Great stuff.

http://www.microsoft.com/emea/itsshowtime/sessionh.aspx?videoid=549

Good luck.

-D.|||If you don't want to run it in production, I'd recommend restoring some prod db's to another server, and running it there. I do that a lot, too.I believe this scores very highly on Best Practice as it tests your DR too.
We don't do it here mind :rolleyes:|||If you haven't seen it already, Paul Randal has a nice presentation online all about DB corruption best practices. Great stuff.

http://www.microsoft.com/emea/itsshowtime/sessionh.aspx?videoid=549

-D.

Now THAT was a great presentation.

Thank you for the link.

Regards,

hmscott