SQL Cached Plans - MPA

Question

SQL Cached Plans - MPA

SQLSACT

SSC-Insane

Points: 21838
More actions
March 12, 2013 at 1:09 am

#290866

Hi All
I have a question regarding SQL cached plans and the multipage allocator
Based on what I understand, any request of more that 8KB of memory will go through the multipage allocator.
I have run the below select and the results are a bit confusing
SELECT COUNT (*)
FROM sys.dm_exec_cached_plans AS cp
where cp.size_in_bytes/1024 > 8
SELECT COUNT (*)
FROM sys.dm_exec_cached_plans AS cp
where cp.size_in_bytes/1024 < = 8
For the first select, I have 86 plans
For the second select, I have 8 plans
Does this mean that 86 of my cached plans have been allocated through the multipage allocator?
Thanks

Viewing 15 posts - 1 through 15 (of 19 total)

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

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 1

In SQL Server versions before Denali single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page.

Regards,
Kumar

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Thanks

So does that mean that those (86) plans were allocated though MPA ?

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 3

KumarVelayutham

SSCrazy

Points: 2204

March 12, 2013 at 4:06 am

#1595879

yeah sure..

Regards,
Kumar

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

This is why I'm confused because when I query sys.dm_os_memory_clerks - I see all the allocations happening at the single page allocator and nothing at MPA

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

SQLSACT (3/12/2013)
This is why I'm confused because when I query sys.dm_os_memory_clerks - I see all the allocations happening at the single page allocator and nothing at MPA

And you are concerned because...?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

opc.three (3/12/2013)
SQLSACT (3/12/2013)
This is why I'm confused because when I query sys.dm_os_memory_clerks - I see all the allocations happening at the single page allocator and nothing at MPA
And you are concerned because...?

Looking at the size of the plans (more than 8kb), shouldn't they be going through MPA? Unless my understanding of MPA is wrong

Thanks

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

Are you sure the queries you're using to check both the cached plans and the clerks are meant to tie out? i.e. that there are not other mitigating internal factors hidden from the view and undocumented that would prevent them from tying? What is your overarching concern?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 8

opc.three (3/12/2013)
Are you sure the queries you're using to check both the cached plans and the clerks are meant to tie out? i.e. that there are not other mitigating internal factors hidden from the view and undocumented that would prevent them from tying? What is your overarching concern?

I'm using the below to check clerks

SELECT TOP(20) [type], [name], SUM(single_pages_kb)/1024 AS [SPA Mem, Mb]

FROM sys.dm_os_memory_clerks

GROUP BY [type], [name]

ORDER BY SUM(single_pages_kb) DESC;

SELECT SUM(multi_pages_kb)/1024 AS [MPA Mem, Mb]

FROM sys.dm_os_memory_clerks

SELECT SUM(single_pages_kb)/1024 AS [SPA Mem, Mb]

FROM sys.dm_os_memory_clerks

What is your overarching concern?[/

No concern really, I'm just trying to understand the process

In a testing environment (with nothing else running on it), I set up an ad-hoc workload by setting up an agent job that executes 15000 ad-hoc queries against my database. (The purpose of this was to monitor the affect on the plan cache).

The size of each plan is 40960 bytes (40kb)

Out of interest I checked sys.dm_os_memory_clerks and I noticed SPA memory increasing and MPA memory stagnant. Because the plans are definately more than 8KB each, I would think that the memory for these plans would be allocated though MPA and not SPA

SQL 2008 Sp2 Ent

Thanks

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

Not sure. I cannot get the numbers to tie on my system so I am assuming there are other factors in play. Post back if you get it sorted, or start blogging 😉

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

¥♥ürz.§µññÿ Mr or Mrs. 500 Points: 563 More actions · Answer 10

if you want to display cached plans

SELECT cp.objtype AS PlanType,

OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,

st.text AS SQLBatch,qp.query_plan AS QueryPlan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

GO

To remove plans from cache memory

DBCC FREEPROCCACHE

SQLSACT SSC-Insane Points: 21838 More actions · Answer 11

opc.three (3/12/2013)
Not sure. I cannot get the numbers to tie on my system so I am assuming there are other factors in play. Post back if you get it sorted, or start blogging 😉

Thanks

I have also posted about a discrepancy between sys.dm_exec_cached_plans and sys.dm_os_memory_clerks

- Still stuck on that one as well..

http://www.sqlservercentral.com/Forums/Topic1424451-1550-1.aspx#bm1425530

Thanks

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 12

SQLSACT (3/12/2013)
opc.three (3/12/2013)
Not sure. I cannot get the numbers to tie on my system so I am assuming there are other factors in play. Post back if you get it sorted, or start blogging 😉
Thanks
I have also posted about a discrepancy between sys.dm_exec_cached_plans and sys.dm_os_memory_clerks
- Still stuck on that one as well..
http://www.sqlservercentral.com/Forums/Topic1424451-1550-1.aspx#bm1425530
Thanks

Yeah, I remember that one. See my earlier comment about whether they are meant to tie out, or if there is undocumented information being taken into account making up those numbers.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 13

Regarding this post, based on what I'm seeing - I am more concerned about whether my understanding of SPA/MPA is correct.

Maybe I'm missing something here..

Thanks

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 14

Where did you learn about SPA/MPA?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato