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?
Best Answer
My apologies for my previous answer.
1) Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure. The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.
2) You have to create a plan guide that uses a USE PLAN query hint for every type of query(every stored procedure request type) to force execution plan.
Here is article about execution plan that can help.