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 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 8:47 AM
Points: 9, Visits: 44
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
Post #1429766
Posted Tuesday, March 12, 2013 7:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
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
Post #1429768
Posted Tuesday, March 12, 2013 7:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1429776
Posted Tuesday, March 12, 2013 7:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
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

Post #1429793
Posted Tuesday, March 12, 2013 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
Where did you learn about SPA/MPA?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1429800
Posted Tuesday, March 12, 2013 7:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
opc.three (3/12/2013)
Where did you learn about SPA/MPA?


Web mostly
Post #1429809
Posted Tuesday, March 12, 2013 7:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
I could find nothing in my 2008 Internals book (Delaney) that would explain the difference. The differences are odd but are nothing that I expect will ever affect how I admin or develop for SQL Server...what about you?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1429826
Posted Tuesday, March 12, 2013 8:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
. The differences are odd but are nothing that I expect will ever affect how I admin or develop for SQL


I agree - More than anything else, I just want to make sure that my understanding is correct

At least with SQL 2012, AFAIK, MPA is gone

Thanks
Post #1429836
Posted Tuesday, March 12, 2013 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
That's my understanding as well. All memory allocation is now coming from the same allocator and the best known side-effect of that change is that the 'max server memory' option now governs all memory SQL Server allocates, not just the buffer pool.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1429841
Posted Tuesday, March 12, 2013 8:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
Thanks for your help
Post #1429844
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse