Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Cached Plans - MPA


SQL Cached Plans - MPA

Author
Message
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
KumarVelayutham
KumarVelayutham
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 1281
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
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
Thanks

So does that mean that those (86) plans were allocated though MPA ?
KumarVelayutham
KumarVelayutham
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 1281
yeah sure..

Regards,
Kumar
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8259 Visits: 14368
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
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8259 Visits: 14368
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
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8259 Visits: 14368
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search