Friday, March 30, 2012

how to free memory used by prior query statement within a batch by TSQL?

Just Like these:

-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)

/*
can I do something here to free res used by prior statement?
*/

select * from someOtherTable
--batch end

The Sqls above are written in a procedure to automating test for some select querys.What about this:

select ...
DBCC DROPCLEANBUFFERS
select ...|||Won't that force a recompile of everything?

Gotta look that up...

btw...SQL Server will grab as much memory is available, and will only release it if it's not using it and something else needs it...

It's not very sociable...|||thanks for help, but it seemed not work as I hoped.

Sqlserver used 20M memory before I run the select query;
Sqlserver used 123M memory after I run the select query;
Sqlserver still used 123M memory after I run 'DBCC DROPCLEANBUFFERS', but I want memory used by Sqlserver not larger than 20M;

I don't know exactly how memory useage affect the performance of next query's execution, so I write down my primal Intention:

select... -- query A

/* do something here to make query B to be executed just as query A was not executed before( or minish query A's affection). */

select... -- query B

could I make it?|||here is my test plan:

there are query ABC..., and insert all querys into a table called querytbl;
open a cursor for all records from querytbl;
fetch next query from cursor;
while @.@.fetchstatus = 0
begin
exec query for 3 times and calculate average time spending;
fetch next query from cursor;
end
...

Is there any better test plan?(just test time spending)

No comments:

Post a Comment