Get performance CACHE from SQL Server


——————————————————————————-

/* SQL Server stores the procedure cache in 8KB data pages.

You can use the dynamic management view sys.dm_os_memory_cache_counters to provide a summary of how

the cache is allocated using this query:*/

/* CACHESTORE_OBJCP. These are compiled plans for stored procedures, functions and triggers.

CACHESTORE_SQLCP. These are cached SQL statements or batches that aren’t in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server.

CACHESTORE_PHDR. These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

*/

SELECT

TOP 6LEFT([name], 20)as [name],

LEFT([type], 20)as [type],

[single_pages_kb]

+ [multi_pages_kb] AS cache_kb,

[entries_count]

FROM

sys.dm_os_memory_cache_counters

order

by single_pages_kb + multi_pages_kb

DESC

 

 

 

———————————————————————————-

/*We can see individual cache entries by using the sys.dm_exec_cached_plans dynamic management view.*/

 

SELECT

usecounts, cacheobjtype, objtype, plan_handle

FROM

sys.

dm_exec_cached_plans

 

 

————————————————————————————-

 

/* This query lists the most used query plans.

 

In includes plans for stored procedures, adhoc or dynamic SQL, triggers, prepared SQL and views.

 

*/

 

 

select

TOP 100

objtype

,

p

.size_in_bytes,LEFT([sql].[text], 100)as [text]

from

sys.dm_exec_cached_plans p

outer

applysys.dm_exec_sql_text(p.plan_handle)

sql

ORDER

 

 

BY usecounts

DESC

 

—————————————————————————————-

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: