February 25, 2015 at 6:41 am
Hi All
Hoping someone can shed some light on one server I'm having an issue with and it having such a small procedure cache.
Server has 60GB of RAM assigned to its min and max server memory settings, optimise for ad hoc workloads is disabled.
Procedure cache at the moment on the server is 2.41MB with only 6 objects in side all related to mssqlsystemresource database, I can see stuff dropping in for user databases, but as soon as the proc has finished the plan is removed from the cache.
Buffer cache is in the 17GB mark, free pages around the 42GB mark so around 60GB used with a bit in stolen pages, but no proc cache.
All other servers in the environment are reporting over 8GB of proc cache in use which is more healthy.
Using Spotlight to monitor all of this.
Can't for the life of me see whats wrong with this one server and it not keeping the plans in cache.
Thanks
Ant
February 25, 2015 at 6:46 am
anthony.green (2/25/2015)
Server has 60GB of RAM assigned to its min and max server memory settings
Probably unrelated, but I don't like that for starters. Setting min to max means that SQL cannot respond to server-level memory pressure by trimming the buffer pool. If WIndows comes under memory pressure, it may result in SQL's memory being paged out instead of SQL just trimming it's memory.
When you say the plan is removed from cache, how are you seeing that? What counter/DMV is being used to determine that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2015 at 6:48 am
Possibly bug in Spotlight. Your your own DMVs to see what is in/happening in the procedure cache. Glenn Berry's SQL Diagnostics scripts are a good resource.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2015 at 7:18 am
Yeah, I'd validate it by querying the cache directly using DMVs.
Nothing using WITH RECOMPILE? No one running DBCC FREEPROCCACHE? Nothing like that?
"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
February 25, 2015 at 7:29 am
Grant Fritchey (2/25/2015)
Nothing using WITH RECOMPILE? No one running DBCC FREEPROCCACHE? Nothing like that?
No sp_recompile, sp_configure, ALTER DATABASE?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2015 at 8:02 am
Using sys.dm_exec_cached_plans to see what's going on also outside of spotlight.
There are a few procs with recompile in the system as they have a wide range of parameters which a cached plan will cause problems with parametrisation.
Think Gail hit the nail on the head with min/max memory being the same. Its highly strange as every other server has min/max the same and has stuff in the proc cache.
Reduced this to 40GB/60GB and now there are over 800 objects in sys.dm_exec_cached_plans
February 25, 2015 at 8:13 am
Just curious...
What OS?
I have seen various OS's cause this. Most easily reproduced with a desktop OS.
How much total memory?
If you have only 64GB memory on the system, then setting max mem to 60GB can be causing a memory issue with the OS.
Is this a VM or physical?
VMs with memory ballooning enabled, or without a memory reservation set can also cause a memory contention causing plan flush like this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 25, 2015 at 8:21 am
The OS is Server 2012 R2 Standard,
Total server memory is 64GB, but the OS is showing 61.9GB used,
It's a Hyper-V VM set with a static memory allowance of 65536MB
February 25, 2015 at 8:28 am
anthony.green (2/25/2015)
Total server memory is 64GB, but the OS is showing 61.9GB used,
60GB max server memory on a 64GB server is a tad on the high side. OS needs memory to manage memory, I'd dial it back to around 54-56 and monitor available MB (perfmon counter)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2015 at 10:01 am
GilaMonster (2/25/2015)
anthony.green (2/25/2015)
Total server memory is 64GB, but the OS is showing 61.9GB used,60GB max server memory on a 64GB server is a tad on the high side. OS needs memory to manage memory, I'd dial it back to around 54-56 and monitor available MB (perfmon counter)
Agreed. There is a common practice to only allow 2-4gb for the OS and that practice should be discarded. The more memory you have, the more memory the OS needs to help manage itself (and memory like Gail said).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply