January 27, 2009 at 11:31 pm
When adhoc plans are cached how long do they stay in the cache?
Will they be eventually flushed or do you have to flush the cache manually by running DBCC FreeProcCache?
What happens if you max out the cache with adhoc plans?
I ask these questions because I was just told that adhoc plans won't be released from cache until you manually flush the cache. I did not think that was the case. The person also said when adhoc plans max out the procedure cache and any newly executed stored procedures won't be cached. Is this correct?
Thanks, Dave
January 27, 2009 at 11:47 pm
DBADave (1/27/2009)
When adhoc plans are cached how long do they stay in the cache?
Depends on how expensive they were to compile and how often they're used.
Will they be eventually flushed or do you have to flush the cache manually by running DBCC FreeProcCache?
They'll be aged out if they're never reused. There's a clock-like mechanism in the proc cache, every cycle the cost of a plan is decreased. It's increased again if the plan is used. If the cost reaches 0, the plan will be discarded. (on the assumption that it's cheaper to recompile it than keep it around)
What happens if you max out the cache with adhoc plans?
Maxing out the cache is an issue in itself. There's no hard limit to the size of the procedure cache in SLQ 2005. It's just a portion of the buffer pool. There is a soft limit, that it's not supposed to exceed for long. If the contents of the cache reach around 80% of that, older (cheaper) plans get discarded. What the limit is depends on patch level. It was much higher on RTM and SP1 then in SP2
With SP2, the 'limit' is 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB. See http://blogs.msdn.com/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx
I have seen a server where the proc cache did reach this frequently (42GB memory, 6.5 to the proc cache) and there were no strange occurences, no poor performance, etc.
I ask these questions because I was just told that adhoc plans won't be released from cache until you manually flush the cache. I did not think that was the case. The person also said when adhoc plans max out the procedure cache and any newly executed stored procedures won't be cached. Is this correct?
Total garbage. Adhoc and stored proc plans are treated almost the same way. The only way to run a proc and not have its plan cached is to mark it WITH RECOMPILE
The same vendor as came up with the L2 cache 'problem'?
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
January 28, 2009 at 12:09 am
Bingo. You guessed it. He's now hitting us with claims about adhoc plans consuming 28-32GB of cache and that the cache will never be released because the plans are adhoc. That's a new one for me, but I wanted to doublecheck with someone before telling the him he's wrong.
Thanks
And one last question for the night, have you ever encountered a situation where SAN performance was slower then direct storage? Our EMC performance on a 64 bit cluster is showing IO bottlenecks on Avg. Disk Sec/Read (.077) vs 64 bit attached storage (same RAID 10 config) where there is a bottleneck (Avg. Disk Sec/read = .055), but performance is much better. Copy time from one drive to another on the SAN is twice as slow as copy time on attached storage. All other hardware and OS are identical.
January 28, 2009 at 12:32 am
DBADave (1/28/2009)
Bingo. You guessed it. He's now hitting us with claims about adhoc plans consuming 28-32GB of cache and that the cache will never be released because the plans are adhoc. That's a new one for me, but I wanted to doublecheck with someone before telling the him he's wrong.
That size should be impossible, unless you've got well over 128 GB of memory in that server or you're still on RTM or SP1. Is that the case?
Use this to see the size of the plans in cache:
select sum(single_pages_kb + multi_pages_kb)/1024 AS SizeOfPlanCacheInMB from sys.dm_os_memory_cache_counters
where type in ('CACHESTORE_SQLCP','CACHESTORE_OBJCP')
And one last question for the night, have you ever encountered a situation where SAN performance was slower then direct storage?
Sure. As a friend is fond of saying, "There's no such thing as magic SAN dust". A SAN has to be configured and laid out with as much, if not more, care as direct attached storage. Just because it's a SAN doesn't make it fast.
Among the problems I've seen or heard of:
Shared drives - a single drive is shared between two or more LUNs. Not cool.
Shared fibre switch
Incorrect raid level - logs don't like raid 5
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
January 28, 2009 at 7:21 am
Thanks Gail. I'm going to work with our SAN people (notice the Star Wars reference) today. I've been telling them for a couple of months we are not getting good performance. Unfortunately I don't know enough about SAN technology to point them in a particular direction. I'm guessing EMC needs to come out and take a look...again.
Here's the results of your query. We are SP2
SizeOfPlanCacheInMB
--------------------
4634
Is the query below also considered useful? If I understand the DMV it only pertains to query execution plans residing in cache.
select objtype,
count(*) as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
group by objtype
objtype number_of_plans size_in_MBs avg_use_count
---------------- --------------- ------------------ -------------
UsrTab 2 0 12
Prepared 27433 3104 28
View 511 54 527
Adhoc 7721 866 683
Check 6 0 192
Trigger 14 1 60
Proc 1335 612 22815
January 28, 2009 at 7:48 am
Well, you can tell your vendor friend that, far from the 28-32 GB that he says the proc cache is using, it's actually using only 4.6 and most of that is NOT ad-hoc plans, but rather prepared SQL statements (probably the result of sp_executesql)
Not saying that prepared SQL statements are good, procs are far better.
As for the SANs, not my area of expertise, I'm afraid.
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
January 28, 2009 at 10:56 am
I'm sending him the formulas for plan cache and telling him we have to agree to disagree because I don't wish to discuss this further. He's now telling me that the maximum amount of plan cache Sql Server will hold is 30 gb and it’s not impossible that it can hit that size because Windows can page the hard drive.
SQL Server Version Maximum Limit on Plan Cache Size
SQL Server 2000 4GB upper cap on the plan cache
SQL Server 2005 RTM/SP1 75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB
SQL Server 2005 SP2 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB
For a SQL Server with 32Gb total SQL server memory, SQL Server 2005 RTM and SP1 cachestore limit will be 75% X 8 + 50% X (32 - 8) = 18GB
SQL Server 2005 SP2 cachestore limit will be 75% X 4 + 10% X (32-4) = 5.8GB
For my own understanding I reviewed the following BOL link http://msdn.microsoft.com/en-us/library/cc297252.aspx
If I understand plan cache (and I still may be missing some things), the formulas above pertain to how SQL Server determines at what level plan cache will be freed, not the maximum amount of plan cache. In my case (SP2 64-bit, 32GB memory), when 5.8GB of memory is used for plan cache (based upon the above formula) SQL Server will begin cleaning the cache. Is this correct? If so, is it possible for plan cache to be larger then 5.8GB because the plans are all considered active and cannot be purged? If so, does plan cache only pertain to physical memory or can (in theory) the page file come into play?
I also found a formula on the Internet that I believe is incorrect and perhaps the reason why the vendor feels I have a 30GB plan cache. http://blogs.msdn.com/sqlprogrammability/Default.aspx?p=2 has a similar forumla to yours, but multiplies by 8, which I don't see why that is necessary.
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
plan_cache_in_GB
---------------------------------------
33.883239746093
Thanks for the lessons.
Dave
January 28, 2009 at 1:03 pm
DBADave (1/28/2009)
If I understand plan cache (and I still may be missing some things), the formulas above pertain to how SQL Server determines at what level plan cache will be freed, not the maximum amount of plan cache. In my case (SP2 64-bit, 32GB memory), when 5.8GB of memory is used for plan cache (based upon the above formula) SQL Server will begin cleaning the cache. Is this correct?
SQL will start discarding plans when the cache reached 75-80% of that value. It is possible that the plan cache will exceed that for short periods.
What's your server's max memory set to? It's that, not the total memory that you use in the above formula.
I also found a formula on the Internet that I believe is incorrect and perhaps the reason why the vendor feels I have a 30GB plan cache. http://blogs.msdn.com/sqlprogrammability/Default.aspx?p=2 has a similar forumla to yours, but multiplies by 8, which I don't see why that is necessary.
That would be correct if the DMV was storing the number of pages (which are 8k). Its not. It's storing the size in kb. You can check BoL is you wish.
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply