SQL server query is very slow because cache is too small

  • My server have 2 instance: 1 version 2012 and 1 version 2017. Sometimes, instance sql 2012 (or sql 2017) execute query slowly.

    Cause: cache have problem but i don't know what is it. I use "select * FROM sys.dm_exec_cached_plans" but result have 4 rows or 5 rows every time.

    Then, I restart my server, query is fast.

    Solution ??? Help me.

    • This topic was modified 3 years, 10 months ago by  thaitinh77.
    • This topic was modified 3 years, 10 months ago by  thaitinh77.
  • First try "SELECT COUNT(*) FROM sys.dm_exec_cached_plans", to see how many cached plans you actually have.  Then go from there.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I don't see anything in what you posted to suggest that it's a cache problem. If you have two instances on the server, make sure each one has a max memory setting or they'll be fighting each other for memory. If you really think that just the cache is your problem, instead of restarting the server, which is extremely severe as a troubleshooting step, why not simply run DBCC FREEPROCCACHE to reset the cache?

    If you can post more information about the problem you're seeing, others here may be able to suggest superior solutions.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply