Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    161

    Unanswered: Query Cache is not working

    Hello all,

    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.

    Any ideas? I am not sure where to look next.

    Bill

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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)?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •