Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Unanswered: 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.
    ......

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this:

    select ....
    DBCC DROPCLEANBUFFERS
    select ....

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2004
    Posts
    26
    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?
    ......

  5. #5
    Join Date
    Jan 2004
    Posts
    26
    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)
    ......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •