en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
דצמ12

Written by: ronen ariely
12/12/2012 19:10 RssIcon

Execution Plan Caching Clean

בבלוג קצר זה אני אציג 2 שאילתות לתחזוקה.

בשאילתה הראשונה נמצא את כל תוכניות ההרצה השמורות כרגע בזכרון ה CACH של השרת ואשר הן לא רצו עד עתה יותר מפעם אחת. ההנחה הסיסית שניתן לבצע היא שאם הן לא רצו הרבה פעמים אולי אין בהן צורך בזכרון...

בשאילתה השנייה פשוט נעזר בפקודת DBCC על מנת לנקות תוכנית הרצה מסויימת שמצאנו בחלק הקודם

use AdventureWorks2012
GO
 
-- cache plans which called once in a life time.
-- You can see how much memory is already used.
SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;
 
-- remove Specific execution plan cach:
-- plan_handle : get the value from the query above
-- DBCC FREEPROCCACHE(plan_handle) -- replace the "plan_handle" with the plan_handle you got. like this
DBCC FREEPROCCACHE(0x060005002CB24D05106D8E720100010001000000000000000000000000000000000000000000000000000000)

חומר נוסף בנושא זה:

-- Clearing all cache entries associated with a pool_name
DBCC FREEPROCCACHE ('default');
 
-- Clearing all plans from the plan cache
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
 
 
-- Execution Plan Caching and Reuse
 
 
-- DMV that can help monitor:
-----------------------------
-- plan_handle can obtained from the following dynamic management objects:
select * from sys.dm_exec_cached_plans
select * from sys.dm_exec_requests
select * from sys.dm_exec_query_memory_grants
select * from sys.dm_exec_query_stats
 
-- sql_handle is the SQL handle of the batch to be cleared and can be obtained from the following dynamic management objects:
select * from sys.dm_exec_query_stats
select * from sys.dm_exec_requests
-- getting curent session_id
declare @session_id int
select @session_id = @@SPID
-- Returns information about the cursors that are open in various databases.
select * from sys.dm_exec_cursors (@session_id | 0 )
-- Returns information about active handles that have been opened by sp_xml_preparedocument.
select * from sys.dm_exec_xml_handles (@session_id | 0 )
select * from sys.dm_exec_query_memory_grants
 
-- pool_name is the name of a Resource Governor resource pool and can be obtained by querying the
-- sys.dm_resource_governor_resource_pools
 
 
-- This example returns information about cursors that have been open on the server longer than the specified time of 36 hours
SELECT creation_time, cursor_id, name, c.session_id, login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 36;
GO