October 3, 2018 at 8:52 am
Hi experts,
When I run:
select * from sys.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Manager'
and counter_name in ('Database pages')
I get 'Cntr_value' around 10105000. Is this a good number? I believe 10 million value is 10 million*8 K pages = 80 million KB (76 GB) space occupied in Buffer cache. I don't see any memory pressure the Page life expectancy = 642093
but just like to know if this a good number? Just for my knowledge.
Other settings:
Total memory = 390 GB
Minimum server memory= 30 GB
Max server memory = 178
Processors = 12
October 3, 2018 at 1:33 pm
I think its depends on usage of the database, i.e. how and how much application is using database, I use queries below to find out how much buffer pool memory is used for each database, 76 out of 390 is not too much however there may be other databases that are more active and have data in buffer/pages.
With CTE_BP(DatabaseName, BufferSizeInMB )
as
(
SELECT
case when DB_NAME(b.database_id) is null then 'Resource DB' else DB_NAME(b.database_id) end AS database_name
,(cast(COUNT(*) as decimal(20,2)) * 8192.00) / (1024.00 * 1024) AS buffer_count_MB
FROM sys.dm_os_buffer_descriptors AS b
GROUP BY b.database_id
)
select sum(BufferSizeInMB) Total_Buffer_Size from CTE_BP ;
With CTE_BP(DatabaseName, BufferSizeInMB )
as
(
SELECT
case when DB_NAME(b.database_id) is null then 'Resource DB' else DB_NAME(b.database_id) end AS database_name
,(cast(COUNT(*) as decimal(20,2)) * 8192.00) / (1024.00 * 1024) AS buffer_count_MB
FROM sys.dm_os_buffer_descriptors AS b
GROUP BY b.database_id
)
select * from CTE_BP order by BufferSizeInMB desc
October 3, 2018 at 1:52 pm
Tac11 - Wednesday, October 3, 2018 8:52 AMHi experts,
When I run:
select * from sys.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Manager'
and counter_name in ('Database pages')I get 'Cntr_value' around 10105000. Is this a good number? I believe 10 million value is 10 million*8 K pages = 80 million KB (76 GB) space occupied in Buffer cache. I don't see any memory pressure the Page life expectancy = 642093
but just like to know if this a good number? Just for my knowledge.
Other settings:
Total memory = 390 GB
Minimum server memory= 30 GB
Max server memory = 178
Processors = 12
There is no set "good" number as goher2000 indicates. It's another one of the metrics that you would want to get a baseline.
This description of that counter explains it a bit more:
SQL Server: buffer manager: database pages
Sue
October 3, 2018 at 1:53 pm
want to jump in deep?
Use DATABASE_NAME
GO
SELECT
name AS TableName,
IndexName,
IndexTypeDesc,
(cast(COUNT(*) as decimal(20,2)) *8.0)/1024 AS 'cached_page_Size(mb)',
COUNT(*) AS 'cached_pages_count'
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT
Allocation.name,
Allocation.index_id,
Allocation.allocation_unit_id,
Allocation.OBJECT_ID,
ind.name IndexName,
ind.type_desc IndexTypeDesc
FROM
(
SELECT
OBJECT_NAME(p.OBJECT_ID) AS name,
p.index_id ,
au.allocation_unit_id,
p.OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT
OBJECT_NAME(p.OBJECT_ID) AS name,
p.index_id,
allocation_unit_id,
p.OBJECT_ID
FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id
AND au.type = 2
) AS Allocation
LEFT JOIN sys.indexes ind ON ind.index_id = Allocation.index_id AND ind.OBJECT_ID = Allocation.OBJECT_ID
) AS sysobj ON bd.allocation_unit_id = sysobj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY TableName
October 3, 2018 at 1:56 pm
there use to be command to pin table in memory not any more..
October 4, 2018 at 8:25 am
Thanks goher2000 and Sue_H for taking your time to reply my question. One more thing, what if the 'Cntr_Value' was 300GB? What actions could be taken to make Buffer pool optimal?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply