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?