Memory estimate

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

mxy (3/3/2014)
how do we estimate/calculate procedure cache usage

Use this query ..

SELECT name,

[MB used] = SUM(single_pages_kb + multi_pages_kb)/1024.0

FROM sys.dm_os_memory_clerks

WHERE name IN (

N'Object Plans', -- Procedures, triggers, etc

N'SQL Plans' -- Ad-hoc stuff

)

GROUP BY

name

ORDER BY

[MB used] DESC;

By the way, is the system 32 bit or 64 bit ?

--

SQLBuddy

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 2

thanks

its 64 bit will it matter

how to calculate Data Cache usage

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

mxy (3/3/2014)
thanks
its 64 bit will it matter
how to calculate Data Cache usage

Procedure Cache Bloating is an issue with 32 bit systems. 64 bit systems should be fine.

select db_name(database_id) as dbName, count(*)*8/1024

as BufferPoolMB from sys.dm_os_buffer_descriptors

group by db_name(database_id)

order by 2 desc

--

SQLBuddy

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 4

thanks SQL buddy can we calculate procedure cache @db level

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

One Orange Chip

Points: 28294

March 3, 2014 at 2:31 pm

Unfortunately,no..:(

--

SQLBuddy