Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Unanswered: avoid reusing query plan..

    Hi,

    I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
    Any thoughts...?

    Thanks,

    S.
    Steve

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about...

    DBCC FREEPROCCACHE
    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.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Does it also clear the Query cache ??
    Get yourself a copy of the The Holy Book

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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Enigma
    Does it also clear the Query cache ??
    Not sure, never had the need..

    Remarks
    Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.
    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.

  5. #5
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: avoid reusing query plan..

    Thanks guys,

    Well, I hoped to find a way to clear the procedure cache and the buffer pages as well, but DBCC FREEPROCCACHE does only the first part of it.
    Actually I was trying to optimize a stored proc. and examine the impact directly using SQL Analyser. When I execute the SP for the first time, it takes around 3 sec. to complete. Once the data pages buffered and execution plan reused, it takes around 1 sec. (FREEPROCCACHE doesn't alter it much)
    So it seems that FREEPROCCACHE has the same effect as CREATE PROCEDURE WITH RECOMPILE but nothing more.
    Thanks anyway,

    S.
    Steve

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Look up Buffer manage object in BOL...

    Haven't seen a way to do what you're looking for...

    but hey ...1 second ain't bad.....

    1 mississippi...

    done
    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.

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28

    Re: avoid reusing query plan..

    Originally posted by dbadelphes
    Thanks guys,

    Well, I hoped to find a way to clear the procedure cache and the buffer pages as well, but DBCC FREEPROCCACHE does only the first part of it.
    Actually I was trying to optimize a stored proc. and examine the impact directly using SQL Analyser. When I execute the SP for the first time, it takes around 3 sec. to complete. Once the data pages buffered and execution plan reused, it takes around 1 sec. (FREEPROCCACHE doesn't alter it much)
    So it seems that FREEPROCCACHE has the same effect as CREATE PROCEDURE WITH RECOMPILE but nothing more.
    Thanks anyway,

    S.
    Try DBCC DROPCLEANBUFFERS. This removes any cached data. I use this and dbcc freeproccache when doing any comparison testing.

  8. #8
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: avoid reusing query plan..

    That's what I was looking for.
    Thanks a lot...(go Leafs go.. )

    S.


    Originally posted by homer37
    Try DBCC DROPCLEANBUFFERS. This removes any cached data. I use this and dbcc freeproccache when doing any comparison testing.
    Steve

Posting Permissions

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