Flush the query plan from the RAM.

20 pts.
Tags:
Query plan
RAM
SQL Server 2005
SQL Server performance
Our clients are reporting that the sql server is slowing down over a period of time. Once the SQL Server is restarted everything is fine.SQL server slowing down over a period of time though has technical reasons. SQL server caches (in memory) the queries that are sent it to, expecting to reuse it if the same query is sent again. Over a period of time this cache builds up (and the memory) to a point where it reaches the RAM limit. After this it starts to use the hard disk for memory (virtual memory) which is slower. Restarting the sql server will clear the cache thereby speeding up the server. This problem is partly to do with how SQL server is designed and mostly with application design. SQL Server 2005 SP2 is supposed to address this cache bloating problem. A poorly designed SQL query will never get reused but they just bloat the cache. Rewriting these queries to be reused will make caching more effective. I have a set of queries which I believe that are causing the RAM to bloat.Most of the queries are Select statements, How can I effectively rewrite these queries so that they are flushed out of the RAM each time after they are used?

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you put the queries within stored procedures and setup the stored procedures with the WITH RECOMPILE option the execution plan will be regenerated each time it’s run. This isn’t recommend as it will increase your CPU load as the execution plan needs to be recreated each time the query is run.

How much RAM does the SQL Server have? How much cache is being used for the buffer cache, and how much for the procedure cache?

If you want to flush the execution plans you can use the DBCC FreeBufferCache command.

Normally SQL will flush the commands with the oldest last run time when the procedure cache begins to fill.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following