I have a mystery on my hands. My database server is not caching stored procedures and queries. I have had performance problems for a while, but I just discovered this issue.
select * from sys.dm_exec_query_stats, I usually get no rows, or I may get a few rows with an execution count of 1.
I did a server trace for a few minutes, and then I searched for any FREEPROCCACHE or DROPCLEANBUFFERS statements. There were none. In this trace, every query registers a cachemiss event followed by a cacheinsert event. It acts like the query cache is disabled, but I didn't think one could disable the query cache.
I am running SQL Server 2005 X64 with 32 GB ram. Right now it is only using 14 GB of the available ram, but it usually uses around 30GB.
As I recall, SQL Server will not cache plans for queries that are "too simple". If you have tables with single indexes, and all the queries are hitting those indexes, then you may see this as a result.
if you monitor the server with Perfmon, what do you see for SQL Server Memory Manager: SQL Cache Memory (KB)?