October 13, 2010 at 8:37 am
We have a server that is Windows 2007 64 bit 6gig memory running SQL 2005. SQL 2005 is hogging all the available memory and not using it all the time. I see I can go in and place a static ceiling on the memory usage and limit it to 2 gig memory and save the rest for the other processes running on that server.
What i don't understand other than this could affect the performance SQL but what else should I be concerned about? I have it also using all 4 processors. Should I limit the processors or the memory? Or is there another recommendation to helping this problem. I know I could also add more memory but my fear is it would just hog more if I put more out there. I don't believe it need more than 2 gig. I don't know SQL 2005 well enough to know what other performance tuning I could do. Any help or advice is welcomed and appreciated.
October 13, 2010 at 8:54 am
I would suggest trying running the below queries at time when the SQL server is very used. Which should help us in understanding the total memory consumed by SQL Server.
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
May be once you know how is required then you can think of putting a memory cap on SQL.
October 13, 2010 at 9:06 am
NSzczepanski-234589 (10/13/2010)
We have a server that is Windows 2007 64 bit 6gig memory running SQL 2005. SQL 2005 is hogging all the available memory and not using it all the time.
I'm guessing you mean Window Server 2003 64bit? 2007 is not an OS version.
What are you using to determine that SQL Server is "hogging all the available memory"?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 13, 2010 at 11:29 am
I'm not using any tool as I am assuming that SQL is allocating all the memory.
October 13, 2010 at 11:43 am
NSzczepanski-234589 (10/13/2010)
I'm not using any tool as I am assuming that SQL is allocating all the memory.
Don't assume. Measure.
Perfmon is the tool of choice here, don't use Task Manager.
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
October 13, 2010 at 2:54 pm
With 64 bit it is advised you set the max server memory for sqlserver !
Set the sqlserver configs "max server memory" to the value you allow it to take !
That number will be the number of ram it uses for buffer memory.
Howerver, other parts of sqlsever also need memory, as do other services on your box.
exec sp_configure 'max server memory", yourvalueinMB
reconfigure
go
checkpoint
go
-- keep in mind, depending on the pressure you server has, it may take some time before it starts freeing up ram
Don't choke your sqlserver ! So, trim ram down, don't restrict it to much !
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
October 14, 2010 at 6:36 am
GilaMonster (10/13/2010)
NSzczepanski-234589 (10/13/2010)
I'm not using any tool as I am assuming that SQL is allocating all the memory.Don't assume. Measure.
Perfmon is the tool of choice here, don't use Task Manager.
That would be the way I would start.
Knowing what is consuming resources - CPU and Memory - is your first step.
You don't want to guess or assume, especially if this is production.
If you are running SSAS along with SQL server on the same box as one of the other services, it's very likely might not be SQL server giving you the problem.
You might want to look at Books On Line for memory settings on SQL server, and google for LockPagesInMemory for x64 bit SQL.
Greg E
October 14, 2010 at 11:49 am
Thanks all for the responses. I located the problem with a rogue service running using Perfmon. I appreciate all the help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply