SQL Server Performance

Memory Utilization High in Production Server

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by visa, Apr 20, 2012.

  1. visa New Member


    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.

Share This Page