Keeping SQL out of the buffer or finding a way to improve page life expectancy?

Question

My SQL Server 2008 server gets flooded with dynamic SQL from one stored procedure. I am wondering whether I can tell SQL Server not to store this one stored procedure in the pool as it doesn't matter too much whether it executes quickly. Perhaps there is an equivalent of (nolock) for (nobuffer) or similar?

Any ideas?

Answer 1

Random thoughts...

• Can you change the code to use the schema? eg "dbo,". This should help by enabling plan reuse.

• Can you factor our the most common calls into child parameterised stored procs?

• Use sp_executesql ?

I don't know of any way to do this, and if there was you'd end up shifting the problem elsewhere.

Slighty old, but worth reading... Trouble Shooting Query Performance Issues Related to Plan Cache in SQL 2005 RTM and SP1. Also [""][2]