Post reply

how to remove single query plan from cache - SQL 2000

  • August 13, 2015 at 4:47 am

    #148760

    Hi,

    Still have a number of servers running 2000 and have one particular poorly performing stored procedure.

    I have recreated the environment elsewhere and with some reindexing I achieve better performance, however reindexing on production server does not seem to have the same effect, which leads me to believe SQL Server may be using an old plan or the plan has become corrupt.

    I'm aware in newer versions of SQL we can provide a "Plan Handle" parameter to DBCC FREEPROCCACHE.

    However, with replication running on production machine I do not wish to empty entire cache or even cache of the database concerned - just one query plan.

    I can see an ObjID in syscacheobjects but I cannot use this apparently.

    Is it possible to remove one plan?

    Thanks.

  • August 13, 2015 at 4:51 am

    #1819600

    exec sp_recompile 'ProcedureName'

    That said, rebuilding indexes invalidates all plans using the rebuilt indexes and will force a procedure to recreate it's plan. Hence it's likely that recompiling won't fix things either and that you'll have to investigate why the procedure is slow.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 13, 2015 at 5:04 am

    #1819604

    Thanks. By "rebuilding indexes" do you mean clustered or non-clustered, or would new plan be created regardless of number or type of index?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply