Timeline for SQL Server with small Cache plan size
Current License: CC BY-SA 4.0
19 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 6, 2023 at 5:27 | vote | accept | KIM | ||
| Oct 6, 2023 at 5:26 | answer | added | KIM | timeline score: 1 | |
| Sep 8, 2023 at 7:37 | history | edited | KIM | CC BY-SA 4.0 |
deleted 2 characters in body
|
| Sep 8, 2023 at 1:50 | history | edited | KIM | CC BY-SA 4.0 |
edited body
|
| Sep 8, 2023 at 1:49 | comment | added | KIM | @ AlwaysLearning My bet, Pyhysical memory is 48GB. | |
| Sep 8, 2023 at 1:42 | history | edited | KIM | CC BY-SA 4.0 |
deleted 2 characters in body
|
| Sep 8, 2023 at 1:37 | comment | added | KIM |
@ Martin Smith Thank you Ssooooooooo much! I already check. There is nothing to flush plan cache And it happens so many times. sys.dm_os_memory_clerks is edited in the question.
|
|
| Sep 8, 2023 at 1:36 | history | edited | KIM | CC BY-SA 4.0 |
added 148 characters in body
|
| Sep 8, 2023 at 1:29 | comment | added | KIM | The server is dedicated. There is nothing else in the host. I edited the question. | |
| Sep 8, 2023 at 1:28 | history | edited | KIM | CC BY-SA 4.0 |
added 47 characters in body
|
| Sep 7, 2023 at 9:11 | history | migrated | from stackoverflow.com (revisions) | ||
| Sep 7, 2023 at 9:08 | comment | added | Mitch Wheat | @alwaysLearning the general rule of thumb is 10% or 4GB (depends what else is on the server), leaving 25% is too high IMO | |
| Sep 7, 2023 at 9:07 | comment | added | AlwaysLearning | Aside... your max server memory setting seems way too high. Accepted practice is to start with about 75% of a system's available memory (assuming there's only a single SQL Server instance on the machine). The setting only limits the size of the SQL Server buffer pool and more memory will be required for other allocations such as backup buffers, extended stored procedures, sp_OA methods (you aren't using those, right?), linked server connections, etc.. | |
| Sep 7, 2023 at 8:59 | comment | added | Mitch Wheat | SELECT TOP(10) mc.type, CAST((SUM(mc.pages_kb)/1024.) AS DECIMAL(9,2)) AS MemUsageMB FROM sys.dm_os_memory_clerks AS mc WITH(NOLOCK) GROUP BY mc.type ORDER BY SUM(mc.pages_kb) DESC OPTION(RECOMPILE); | |
| Sep 7, 2023 at 8:53 | comment | added | Martin Smith |
From the info in the question it sounds like the high CPU usage is being caused by the high number of recompiles and so I think you are on the right track with investigating why this happening. And the cache size does seem much lower than typical. Do you have anything regularly scheduled that would flush the plan cache? What does sys.dm_os_memory_clerks show?
|
|
| Sep 7, 2023 at 8:00 | comment | added | KIM | I got your point. thanks. Is there any quick ways to find tables with large scan? | |
| Sep 7, 2023 at 7:58 | comment | added | Mitch Wheat | No, I mean high CPU is very often a symptom of large table scans in turn caused by not having the right indexes | |
| Sep 7, 2023 at 7:57 | comment | added | KIM | I appreciate your advice, I'll check it. Do you mean that data buffer cache has the first priority and it pulls out plan cache when it need? | |
| Sep 7, 2023 at 7:44 | history | asked | KIM | CC BY-SA 4.0 |