Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » SQL Cached Plans - MPA
Add to briefcase
20 posts, Page 2 of 2
««12

SQL Cached Plans - MPA Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
¥?ürz.§µññÿ
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, March 13, 2013 1:01 PM
Points: 7, Visits: 39
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
SQLSACT
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: Today @ 3:47 AM
Points: 1,185, Visits: 2,102
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
opc.three
Posted Tuesday, March 12, 2013 7:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 6,696, Visits: 11,716
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

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 #1429776
SQLSACT
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: Today @ 3:47 AM
Points: 1,185, Visits: 2,102
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
opc.three
Posted Tuesday, March 12, 2013 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 6,696, Visits: 11,716
Where did you learn about SPA/MPA?

__________________________________________________________________________________________________
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 #1429800
SQLSACT
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: Today @ 3:47 AM
Points: 1,185, Visits: 2,102
opc.three (3/12/2013)
Where did you learn about SPA/MPA?


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 6,696, Visits: 11,716
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

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 #1429826
SQLSACT
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: Today @ 3:47 AM
Points: 1,185, Visits: 2,102
. 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
opc.three
Posted Tuesday, March 12, 2013 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 6,696, Visits: 11,716
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

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 #1429841
SQLSACT
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: Today @ 3:47 AM
Points: 1,185, Visits: 2,102
Thanks for your help
Post #1429844
« Prev Topic | Next Topic »

Add to briefcase
20 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.