Question

Flush the query plan from the RAM.

caches, RAM, Query plan, 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?

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.