April 20, 2012 at 3:51 am
Hi
My Company IT manager Send to us the below : [ SQL server 2005]
Currently our SQL production server handling 809440 pages, which required 6.5GB memory.
8KB_Pages Pages_in_KB Pages_in_MB
809440 6475520.000000 6323.750000000
Here below the page count for each database
Clean master 115
Dirty master 3
Clean tempdb 3737
Dirty tempdb 1208
Clean model 18
Dirty model 3
Clean msdb 1888
Dirty msdb 196
Clean testdb1 14369
Dirty testdb1 55
Clean testdb2 18723
Dirty testdb2 1018
Clean testdb3 121
Dirty testdb3 192
Clean testdb4 10884
Dirty testdb4 3
Clean testdb5 22
Dirty testdb5 3
Clean testdb6 1699
Dirty testdb6 4258
Clean testdb7 10458
Dirty testdb7 3
Clean testdb8 3584
Dirty testdb8 3
Clean testdb9 1021
Dirty testdb9 3
Clean testdb10 7552
Dirty testdb10 788
Clean testdb11 16487
Dirty testdb11 391
Clean testdb12 158
Dirty testdb12 3
Clean testdb13 20187
Dirty testdb13 1949
Clean testdb14 5533
Dirty testdb14 3
Clean testdb15 3430
Dirty testdb15 3
Clean testdb16 45734
Dirty testdb16 5
Clean testdb17 8043
Dirty testdb17 1052
Clean testdb18 27
Dirty testdb18 3 and etc
and They informed "Summary of Condition: Memory Utilization High".
So i have used the below query to check which db + table wise highly uses the buffer pool
SELECT count(*)AS cached_pages_count ,CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS Database_name FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id) ,database_id ORDER BY cached_pages_count DESC
SELECT count(*)AS cached_pages_count ,name ,index_id FROM sys.dm_os_buffer_descriptors AS bd INNER JOIN ( SELECT object_name(object_id) AS name ,index_id ,allocation_unit_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(object_id) AS name ,index_id, allocation_unit_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 obj ON bd.allocation_unit_id = obj.allocation_unit_id WHERE database_id = db_id() GROUP BY name, index_id ORDER BY cached_pages_count DESC
after that i dont know what are things i have to do. So please guide for my scenario. Or if i am going wrong Please guide me what are things i have to check and do.
April 20, 2012 at 4:11 am
Unless you have some other symptoms of memory problems I would do nothing at all. SQL server is designed to use as much memory as is available. It only releases it if other processes require it.
On a server running other services as well there can be good reasons to limit the memory usage but unless you actually have other reported problems I would tend to leave it alone.
Mike
April 20, 2012 at 4:13 am
Mike John (4/20/2012)
Unless you have some other symptoms of memory problems I would do nothing at all. SQL server is designed to use as much memory as is available. It only releases it if other processes require it.
Exactly. SQL's designed to use huge amounts of memory. All you have to do is ensure that max server memory is low enough that SQL won't starve the OS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply