sys.dm_db_exec_cached

sys.dm_db_exec_cached_plans

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pardhiveswar - 28 Jun 2008 09:19 GMT

SELECT top 2 b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts],
b.[usecounts]
, a.[dbid], a.[objectid], b.[size_in_bytes], a.[text]
FROM sys.dm_exec_cached_plans as b
CROSS APPLY sys.dm_exec_sql_text(b.[plan_handle]) AS a
ORDER BY [size_in_bytes] DESC
go

quick question

Based on the above query here are the results.

size_in_bytes: 15228928
bucketid=303
objecttype: adhoc

How can i clear the adhoc query from the cache even after i stopped the query

if i failed to clear the cache the same size is going to show everytime..

please help out in this issue

Reply to this Message

Erland Sommarskog - 28 Jun 2008 12:53 GMT

> SELECT top 2 b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts],
> b.[usecounts]
[quoted text clipped - 16 lines]
>
> if i failed to clear the cache the same size is going to show everytime..

And the actual problem is?

You can clear the entire cache DBCC FREEPROCCAHE. You can also run
sp_recompile on any of the tables involved in the query. I'm not 100% sure
that this will remove the plan directly.

In any case, if there is sufficient activity enough on the server, the
plan will age out by time.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

pardhiveswar - 30 Jun 2008 17:17 GMT

Erland,

SELECT b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts],
b.[usecounts]
, a.[dbid], a.[objectid], b.[size_in_bytes], a.[text]
FROM sys.dm_exec_cached_plans as b
CROSS APPLY sys.dm_exec_sql_text(b.[plan_handle]) AS a
ORDER BY [size_in_bytes] DESC

my apolzise if you confused to my question as per the above DMV i told to
the developer to stop the query he did that ,after 2 minutes cpu come back to
normal
later i again checked the DMV it is still showing the same query with same
cpu utilization when i looked into SP_who2 active i didn't see that query is
running
if the DMV is showing the same results again and again then how can i detect
the new query which is taking lot of cpu

Reply to this Message

Erland Sommarskog - 01 Jul 2008 23:16 GMT

> SELECT b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts],
> b.[usecounts]
[quoted text clipped - 10 lines]
> again and again then how can i detect the new query which is taking lot
> of cpu

I don't think that dm_exec_cached_plan is the best place to look for
queries that hogs the CPU. Andrew suggested Profiler, but Profiler is
not going to show a long-running query that was already running when
you started Profiler.

I often use beta_lockinfo for this. That is a stored procedure that I've
written myself, and which digs a few things about what the processes are
up to on your system. You find it on
http://www.sommarskog.se/sqlutil/beta_lockinfo.html.

Signature

Reply to this Message

Andrew J. Kelly - 28 Jun 2008 16:31 GMT

In addition to what Erland stated you can use the undocumented DBCC command
flushprocindb (dbid) to remove all procs for that db.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> SELECT top 2 b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts],
> b.[usecounts]
[quoted text clipped - 18 lines]
>
> please help out in this issue

Reply to this Message

pardhiveswar - 30 Jun 2008 22:15 GMT

Andrew,
SELECT b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts],
b.[usecounts]
, a.[dbid], a.[objectid], b.[size_in_bytes], a.[text]
FROM sys.dm_exec_cached_plans as b
CROSS APPLY sys.dm_exec_sql_text(b.[plan_handle]) AS a
ORDER BY [size_in_bytes] DESC

my apolzise if you confused to my question as per the above DMV i told to
the developer to stop the query he did that ,after 2 minutes cpu come back to
normal
later i again checked the DMV it is still showing the same query with same
cpu utilization when i looked into SP_who2 active i didn't see that query is
running
if the DMV is showing the same results again and again then how can i detect
the new query which is taking lot of cpu

Reply to this Message

Andrew J. Kelly - 01 Jul 2008 01:02 GMT

I am not 100% sure what you are asking but it sounds like you need to run
profiler to see which queries are using lots of CPU.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Andrew,
> SELECT b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts],
[quoted text clipped - 16 lines]
> detect
> the new query which is taking lot of cpu

Reply to this Message