May 18, 2020 at 4:11 am
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.
May 18, 2020 at 2:46 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 18, 2020 at 5:24 pm
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