Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Cached Plans - MPA Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 1:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
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
Post #1429602
Posted Tuesday, March 12, 2013 3:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:28 AM
Points: 102, Visits: 1,050
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
Post #1429629
Posted Tuesday, March 12, 2013 3:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
Thanks

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

Post #1429630
Posted Tuesday, March 12, 2013 4:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:28 AM
Points: 102, Visits: 1,050
yeah sure..

Regards,
Kumar
Post #1429654
Posted Tuesday, March 12, 2013 4:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
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

Post #1429658
Posted Tuesday, March 12, 2013 5:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1429702
Posted Tuesday, March 12, 2013 5:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
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
Post #1429705
Posted Tuesday, March 12, 2013 6:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1429721
Posted Tuesday, March 12, 2013 6:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
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
Post #1429732
Posted Tuesday, March 12, 2013 6:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1429754
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse