July 12, 2011 at 1:39 am
Hi all,
I have a problem with sqlservr.exe (version 2005). It use alot of memory. I check on taskbar manager sqlservr.exe usage (CPU 10 - 20%, Mem usage - 1,493,688/2GB Ram). I dont know how can I fix it. Some body could help me please.
July 12, 2011 at 1:51 am
First of all check the pages life in memory with the help of mentioned query,if this value less than 300 then you have a memory pressure and you have to add memory ,if this value is greater than 300 then you have to check particular queries which those are eating resources
Select OBJECT_NAME,counter_name,cntr_value from sys.dm_os_performance_counters
where counter_name = 'Page Life Expectancy'
Total Server Memory ?
Total SQL Server Memory setting ?
OS 32bit or 64bit ?
is this Server for SQL Server Only ? or other services also running like webserver,dns etc ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 12, 2011 at 1:56 am
I got below output after executing the query sent :
OBJECT_NAMEcounter_namecntr_value
SQLServer:Buffer Manager Page life expectancy 172320
SQLServer:Buffer Node Page life expectancy 172320
July 12, 2011 at 5:42 am
You say SQL is consuming a lot of memory, but is that causing performance issues on that box. Remember, sql will keep taking memory (up to what you allow in max server memory) and only releases it when the OS requests it back (albeit not very quickly). If it's not causing slowness / performance issues, I wouldn't generally worry about it. Out of interest, are you running 32 or 64 bit
July 12, 2011 at 5:44 am
64 bit machine, do i need to enable awe on this..?? any suggestion on this?
July 12, 2011 at 5:54 am
No, you dont need to enable that on 64bit
July 12, 2011 at 5:57 am
then how i can overcome this problem?
July 12, 2011 at 6:33 am
Syed Jahanzaib Bin hassan (7/12/2011)
First of all check the pages life in memory with the help of mentioned query,if this value less than 300 then you have a memory pressure and you have to add memory ,if this value is greater than 300 then you have to check particular queries which those are eating resources
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
July 12, 2011 at 6:44 am
shilpa.shankar87 (7/12/2011)
then how i can overcome this problem?
Why is it a problem?
SQL uses a lot of memory. That's how it's designed. It uses memory to cache data to avoid the cost of going to disk, it caches plans to avoid the cost of recalculating them.
If you think it's using too much, you can reduce the max server memory setting (it's almost always a good idea to set that). With 2 GB on the server I'd probably set max memory to 1.5 GB. That is a fairly small amount of memory for a database server. My laptop has more that.
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
July 13, 2011 at 1:03 am
OBJECT_NAME counter_name cntr_value
SQLServer:Buffer Manager Page life expectancy 172320
SQLServer:Buffer Node Page life expectancy 172320
Its around 48 hours,so there is no need to add memory
select db_name(req.database_id) as DBName,con.num_reads as Num_of_reads_by_Connections
,con.num_writes as Num_of_writes_by_Connections,req.reads as ReadsByRequest,req.writes as WritesByRequest
,req.logical_reads as LogicalReadsByRequest
,ses.session_id,ses.memory_usage,ses.host_name
,ses.program_name,ses.login_name,ses.status,ses.cpu_time as sessCpuTime
,con.net_packet_size,con.net_transport,req.command,req.wait_time
,req.last_wait_type,req.cpu_time as reqCpuTime,st.text
from sys.dm_exec_sessions ses
inner join sys.dm_exec_connections con on con.session_id = ses.session_id
inner join sys.dm_exec_requests req on req.session_id = ses.session_id
cross apply sys.dm_exec_sql_text(con.most_recent_sql_handle) st
SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC
these queries will provide the information about the resource eater query,find the query and then provide execution plan of this query here
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 13, 2011 at 1:36 am
i have enabled awe and now memory consumed by sqlserver.exe is bit reduced, i dont know whether doing this is good idea or not
July 13, 2011 at 1:40 am
shilpa.shankar87 (7/13/2011)
i have enabled awe and now memory consumed by sqlserver.exe is bit reduced, i dont know whether doing this is good idea or not
On 64-bit SQL, the AWE setting is completely and totally ignored. It has no effect.
Now, again I ask. Is this high memory usage a problem? SQL uses a lot of memory. That's how it's designed. It uses memory to cache data to avoid the cost of going to disk, it caches plans to avoid the cost of recalculating them.
If you think it's using too much, you can reduce the max server memory setting (it's almost always a good idea to set that). With 2 GB on the server I'd probably set max memory to 1.5 GB. That is a fairly small amount of memory for a database server. My laptop has more that.
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
July 13, 2011 at 2:04 am
i reduced the max memory , need to check the functionality of server
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply