Re: Emptying the buffers

From: Jonathan Derbyshire (jonathan.derbyshire_at_student.shu.ac.uk)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 03:51:06 -0800

Hi,

Thanks for the loop, I like it. However.....is it possible to run the Stored Proc without actually returning the results in query analyser?

All I would like to see is the 2 select statemens from the @tb table.

Thanks

JD

oj wrote:

Here is an example for you:

e.g.
use Northwind
go
declare @tb table (run int identity primary key, duration int)
declare @dt datetime, @i int,@db int

set @i=10
set @db=db_id()

while @i>0
begin
checkpoint
dbcc dropcleanbuffers
dbcc flushprocindb(@db)
set @dt=getdate()
exec CustOrderHist 'VINET'
insert @tb
select datediff(ms,@dt,getdate())
set @i=@i-1
end

select * from @tb
select 'Average (ms)'=avg(duration),'Total (ms)'=sum(duration)
from @tb
go 

-- 
-oj
http://www.rac4sql.net
"Jonathan Derbyshire" <jonathan.derbyshire@student.shu.ac.uk> wrote in
message news:70070B23-72A4-4642-820C-CBA3D6FDEC8C@microsoft.com...
> Hi,
>> I'm working on a project to optimise the retrieval of data via Stored
Procedures.
>> I intend to run a particalur SP 10 times in a row to calculate the mean
running time.
>> However, i am aware that the SP will make use of the buffers after the
initial execution.
>> I need to test the average time of the query being run for the first
time....
>> Will the command 'DBCC DROPCLEANBUFFERS' achieve what I need?
>> What does the command 'WITH RECOMPILE' do??  Will I need this also???
>> Thanks
>> JD

Relevant Pages

  • Stored Proc results vs. Query Analyzer
    ... I've got a stored proc that isn't giving the results it ... copy/pasted it into query anlayzer (commenting out only ... as the ones used in the copy/paste of the stored proc ... @src as int, ...
    (microsoft.public.sqlserver.programming)
  • TableAdapter, INNER JOINs, stored procs, and problems with Update
    ... I have a stored procedure that uses JOINs to return columns from multiple ... I also have another stored proc that that takes a series of params ... @ac2 int, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Calling Stored Proc from a Function
    ... DECLARE @rc AS INT, @seqid AS INT; ... UPDATE sequences ... > need to do an update statement, I have to use a stored proc as you can ...
    (microsoft.public.sqlserver.programming)
  • Re: TableAdapter, INNER JOINs, stored procs, and problems with Update
    ... I also have another stored proc that that takes a series of params and updates multiple tables. ... by default a FormView attached to the table adapter did not show Edit/Insert/Delete buttons. ... LEFT OUTER JOIN C c ON a.c1 = c.c1 ... @ac2 int, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem inserting data to SQL server 2K
    ... @Request_ID int OUTPUT ... > With CMD ... > '-- Take this varIDValue to insert the table tbl_review as the ... Each stored proc just ran ...
    (microsoft.public.inetserver.iis)