SQL 2012+ data and plan cache split

Question

question

abbi asked Jan 4, '16

SQL 2012+ data and plan cache split

I've allocated 40 GB memory to SQL 2014 instance. How much memory SQL can allocate for data cache (store data pages in buffer ) and plan cache size. Where can I find the information. Thanks in advance

sql-server-2012

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2016-01-05T08:02:22Z

vinaygr24 answered Jan 5, '16

Gives you space used by each database in the buffer pool. select count(*)*8/1024 AS 'Cached Size (MB)' ,case database_id when 32767 then 'ResourceDB' else db_name(database_id) end as 'Database' from sys.dm_os_buffer_descriptors group by db_name(database_id), database_id order by 'Cached Size (MB)' desc; This does the same, but a whole lot quicker SELECT DB_NAME(database_id) AS [DBName], COUNT(*) * 8/1024.0 AS [CacheUsed] FROM sys.dm_os_buffer_descriptors GROUP BY DB_NAME(database_id) ORDER BY DB_NAME(database_id) ASC

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

abbi commented · Jan 05, 2016 at 08:07 AM

Thanks Vinay. I'm aware of this. This only provides what hs already been consumed by SQL server. I'm after the max data cache limit.

0 ·

question