July 8, 2011 at 4:20 am
In my sql server 2005...its showing memory utlization as high(20,494,480k)in task manager.Any way to find out which process is utilizing more memory.?
In SQl server memory properties its showing max memory setting as( 2147483647).AWE is enabled.
July 8, 2011 at 4:26 am
Don't use task manager. Use perfmon
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 8, 2011 at 4:44 am
In perfmon..memory status (Using histogram) is varying...its not showing as high...Is perfmon is used to check the performance of the entire system or just sql server.
In task manager only SQL SERVER.exe is showing more memoryy.why it is different in both
July 8, 2011 at 5:07 am
First question: is your instance a 32-bit instance ??
2) The first thing you need to do before enabling AWE is to set "max server memory" for your instance or it will eat up all your ram.
3) if this is a 64-bit instance, don't enable AWE and set max server memory !
Solution,
exec sp_configure 'max server memory (MB)', yournumberofMBforRAM ;
RECONFIGURE
go
checkpoint
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 8, 2011 at 5:12 am
its 64 bit
July 8, 2011 at 5:15 am
shanila_minnu (7/8/2011)
its 64 bit
ALZDBA (7/8/2011)
...3) if this is a 64-bit instance, don't enable AWE and set max server memory !
Solution,
exec sp_configure 'max server memory (MB)', yournumberofMBforRAM ;RECONFIGURE
go
checkpoint
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 8, 2011 at 11:33 am
first try to see, which database had took more memory on the sql server using the below query:
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
then go to that database and run the below query to obtain which objects were residing in memory:
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
Hope this helps...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply