Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
add comment

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
I agree using WITH RECOMPILE, I've done this with systems I've built. However, I do not have access to the sql source... it runs from within an application. –  Cape Cod Gunny Jan 12 '11 at 17:34
    
@Cape Cod Gunny in this case try DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server. You may want to use this command before testing to ensure that previous stored procedure plans won't negatively affect testing results. Example: DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'database_name') DBCC FLUSHPROCINDB (@intDBID) –  garik Jan 13 '11 at 8:24
    
the issue has been resolved. It turned out that a temp table used to store search criteria had been perpetually accumulating data. The process is supposed to truncate the data from this table before collecting the data. Thanks for the nice sql snippet. –  Cape Cod Gunny Jan 31 '11 at 13:36
add comment

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:

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/08/11/conor-vs-misbehaving-parameterized-queries-optimize-for-hints.aspx

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.

share|improve this answer
    
The DBCC FREEPROCCACHE did not resolve the issue. I do monitory the activity and the events. There are 0 physical I/O's. It seems to be hanging on this application: .Net Sql Client Data Provider. The wait type is CXPACKET. –  Cape Cod Gunny Jan 12 '11 at 17:32
    
CXPACKET implies your query is going parallel. Can you ascertain how many threads are running for the query, and examine their waits? You may want to use Adam Machanic's free tool WhoIsActive sqlblog.com/files/folders/release/entry29675.aspx. –  SQLRockstar Jan 12 '11 at 21:53
add comment

DBCC FREEPROCCACHE

Using this command, you can clear the entire procedure cache down to a single command. Definitely read the documentation before using this command. Read the Remarks section a few times.

Clearing the procedure cache will cause stored procedure caches to be recompiled upon next use. This could impact performance. Use carefully!

Matt

share|improve this answer
    
I tried using DBCC FREEPROCCACHE and it did not resolve the issue. I wound up killing the process after 1 hour. –  Cape Cod Gunny Jan 12 '11 at 17:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.