QUESTION
We have an off the shelf application that uses a Microsoft SQL database. Within this application we pick and choose various selection criteria for each report. This application then runs these reports.
I believe we have a query plan issue. The first report we run each day, runs very fast 7 minutes. Any report we run after the first report takes over an hour.
Each night we run a scheduled task that stops and starts SQL Server Agent and SQL Server. There are approximately 25 other databases within this one instance of SQL Server. No other databases have performance issues, only the one off the shelf product I mentioned earlier.
Is there a way to clear out all query plans that SQL Server currently has in memory?
How can I do this without impacting 30 or so users that rely on other databases on the same server?
ANSWER
You asked two questions here. First, you want to know if you can remove all plans stored in memory for an instance of SQL. That is done with DBCC FREEPROCCACHE as Matt M suggested.
The second question you asked is "How can I do this without impacting 30 or so users that rely on other databases on the same server?". The short answer is "you can't". If you remove all plans than the other users that are relying on plans being in memory will likely suffer a performance hit.
The workaround for this requires some manual intervention. You can use DBCC FREEPROCCACHE to remove specific plans providing you have the plan_handle.
From what you are describing above it does sound like a plan issue, but I am not certain that removing plans is the answer. I would point you in the direction of parameter sniffing before thinking about removing plans:
You should be able to optimize the queries rather than fooling around with DBCC FREEPROCCACHE on a scheduled basis. I would also advise that you spend time analyzing the wait events for your instance.
Tweet