August 11, 2010 at 12:57 am
i have 8 GB ram in my production server, but it always cosumes around 7.8 or 7.9 means 95 % memory of total, i checked the sql server take around 5.5 GB.
and rest memory taken by other things, how wud i knw that sql server is taking worth memory?? should i set max memory server for sql server. or should i require more ram to add in that server???
my organization is asking for suggetion
pls help its urgent
i am sending some important counter report, please have a look on it and let me knw what this report is saying and what kind of action shud i take,
this is the report of one minute counter
Physical disk
Disk Transfer/sec 8.412
Process
Page Fault/sec 744.79
Page file Bytes 9059104164
Virtual bytes 30,765,241,269.797
Working set 8,328,829,927
Sql Serv Buffer Manager
Buffer Cache Hit Ratio 99.908
Checkpoint pages/sec 0.00
Lazy Write/sec 0.00
Sql Server Memory Manager
Target Server memory (KB) 5,247,366.377
Total Server momory (KB) 5038865.778
August 11, 2010 at 4:06 am
Well, with the report it seems the memory usage by sql server seems to high..
Run the below queries to identify how much memory is consumed by SQL Server, each databases & objects occupied.
Buffer pages & corresponding RAM memory usage by SQL Server
======================================================
select count(*) AS Buffered_Page_Count
,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors
How much memory is your each database consuming
===============================================
SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC
Objects which were consuming memory Inside the database:
======================================================
SELECT TOP 25
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, 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(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC
Note: Having a clustered index the the objects is nothing but having a table in the memory
Refer link :- http://sqlserverplanet.com/troubleshooting/sql-server-slowness/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply