posts - 219, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Presentation: Understanding the Procedure Cache: Writing Efficient, Reusable Queries

If you're in the Kansas City area tomorrow night I'm going to be doing a presentation on the procedure cache, parameterization and parameter sniffing.  Details are on the Kansas City SQL Server User Group web site.  I'm a last minute fill in but this is a presentation I've been working on for a while.  Just to get you started I'll post two queries that I use in the introduction:

SELECT sum(single_pages_kb) AS Single_Pages_KB
, sum(single_pages_kb) / 1024 AS Single_Pages_MB
FROM sys.dm_os_memory_cache_counters

SELECT
LEFT([name], 20) AS [Name],
LEFT([type], 20) as [Type],
single_pages_kb,
single_pages_kb / 1024 AS Single_Pages_MB,
entries_count
FROM
sys.dm_os_memory_cache_counters
WHERE
[type] in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP', 'CACHESTORE_PHDR', 'CACHESTORE_XPROC')
ORDER BY
single_pages_kb DESC

 

For the server that SQLTeam.com runs on these return the following:

 

     Single_Pages_KB      Single_Pages_MB
-------------------- --------------------
671848 656

(1 row(s) affected)

Name Type single_pages_kb Single_Pages_MB entries_count
-------------------- -------------------- --------------- --------------- -------------
SQL Plans CACHESTORE_SQLCP 657144 641 9520
Object Plans CACHESTORE_OBJCP 10128 9 104
Bound Trees CACHESTORE_PHDR 776 0 15
Extended Stored Proc CACHESTORE_XPROC 24 0 1

(4 row(s) affected)

Wow!  That's a lot of memory in the procedure cache!  Come by tomorrow night to find out why.

Print | posted on Wednesday, May 09, 2007 3:18 PM |

Feedback

Gravatar

# re: Presentation: Understanding the Procedure Cache: Writing Efficient, Reusable Queries

HI,

I Liked the query very much but ive treid at every db that SQL server 2000 has and it doesnt contains the table named :
sys.dm_os_memory_cache_counters .Plz can u help me if im making a mistake , would love to hear abotu this issue more .

5/10/2007 12:41 AM | Fahad
Gravatar

# re: Presentation: Understanding the Procedure Cache: Writing Efficient, Reusable Queries

The query is for SQL Server 2005.
5/10/2007 7:10 AM | Bill Graziano
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET