High mem usage sqlservr.exe - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

High mem usage sqlservr.exe

Author

Topic

jungptic
Starting Member

7 Posts

Posted - 2008-06-11 : 11:01:59

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.

Thanks
Doan Jung

satishvsh
Starting Member

41 Posts

Posted - 2008-06-11 : 11:11:29

Hai

We can do the following things.

1. Reboot the box.
2. Stop process and restart it.
3. Check any body accessing this box simuntenously.

If you have any event logs send us so that it is easy to troubleshoot like this type of issues.

Regards
Satish

jungptic
Starting Member

7 Posts

Posted - 2008-06-11 : 11:39:14

Here is my log file:

2008-06-11 07:20:25.04 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2008-06-11 07:20:25.04 Server (c) 2005 Microsoft Corporation.
2008-06-11 07:20:25.04 Server All rights reserved.
2008-06-11 07:20:25.04 Server Server process ID is 1208.
2008-06-11 07:20:25.04 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-06-11 07:20:25.04 Server This instance of SQL Server last reported using a process ID of 3656 at 6/11/2008 7:17:04 AM (local) 6/11/2008 2:17:04 PM (UTC). This is an informational message only; no user action is required.
2008-06-11 07:20:25.04 Server Registry startup parameters:
2008-06-11 07:20:25.04 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-06-11 07:20:25.04 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-06-11 07:20:25.04 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-06-11 07:20:25.09 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2008-06-11 07:20:25.09 Server Detected 2 CPUs. This is an informational message; no user action is required.
2008-06-11 07:20:25.32 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2008-06-11 07:20:25.41 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2008-06-11 07:20:25.54 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2008-06-11 07:20:27.66 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2008-06-11 07:20:27.66 Server Database Mirroring Transport is disabled in the endpoint configuration.
2008-06-11 07:20:27.68 spid5s Starting up database 'master'.
2008-06-11 07:20:27.73 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2008-06-11 07:20:27.91 spid5s SQL Trace ID 1 was started by login "sa".
2008-06-11 07:20:27.95 spid5s Starting up database 'mssqlsystemresource'.
2008-06-11 07:20:28.13 spid8s Starting up database 'model'.
2008-06-11 07:20:28.13 spid5s Server name is 'Data2'. This is an informational message only. No user action is required.
2008-06-11 07:20:28.27 spid8s Clearing tempdb database.
2008-06-11 07:20:28.68 Server A self-generated certificate was successfully loaded for encryption.
2008-06-11 07:20:28.71 Server Server is listening on [ 'any' <ipv4> 1433].
2008-06-11 07:20:28.71 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2008-06-11 07:20:28.71 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2008-06-11 07:20:28.73 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2008-06-11 07:20:28.73 Server Dedicated admin connection support was established for listening locally on port 1434.
2008-06-11 07:20:28.74 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2008-06-11 07:20:28.74 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2008-06-11 07:20:29.01 spid8s Starting up database 'tempdb'.
2008-06-11 07:20:29.04 spid13s Starting up database 'msdb'.
2008-06-11 07:20:29.04 spid14s Starting up database 'ReportServerTempDB'.
2008-06-11 07:20:29.04 spid12s Starting up database 'ReportServer'.
2008-06-11 07:20:29.04 spid15s Starting up database 'data1_link'.
2008-06-11 07:20:29.04 spid16s Starting up database 'data2_0106'.
2008-06-11 07:20:29.46 spid16s Recovery is writing a checkpoint in database 'data2_0106' (9). This is an informational message only. No user action is required.
2008-06-11 07:20:30.24 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2008-06-11 07:20:30.26 spid5s Recovery is complete. This is an informational message only. No user action is required.
2008-06-11 07:20:30.26 spid12s The Service Broker protocol transport is disabled or not configured.
2008-06-11 07:20:30.26 spid12s The Database Mirroring protocol transport is disabled or not configured.
2008-06-11 07:20:30.66 spid12s Service Broker manager has started.
2008-06-11 08:21:39.34 spid59 Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-06-11 : 11:59:26

First of all. SQL Server is a memory intensive application. Just like Exchange. "Fixing" this problem is going to require that you balance two separate problems.

1) Memory usage
2) Server performance.

Memory usage is only a problem, if it is accompanied by the pages/sec memory counter in constantly high (over 20 pages/sec). High memory usage in and of itself is not a problem.

Reducing SQL Server's memory can result in SQL Server needing to read the physical disk for more pages, since it can not store all of these pages in memory. This can lead to longer query times, and angrier users.

Above all, test what changes you make.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-11 : 13:13:22

Thats the default. If you have enabled AWE , try to limit with Max and min memory.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 13:42:38

He cross-posted, so I've locked his other thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104590

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-06-11 : 15:30:17

Are you running SQL 64 BIT if so do lock in pages in windows
http://msdn.microsoft.com/en-us/library/ms190730.aspx

Then go to MIN and put in a value and MAX Memory.

Had the same thing until i did the above.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-11 : 23:18:23

Don't have to do any of above since server has only 2gb memory, level it if it's dedicated sql server.

jungptic
Starting Member

7 Posts

Posted - 2008-06-12 : 02:11:54

I use dedicated Server, Memory 2G RAM. Windows Server 2003 32bit. I was limited max Memory (set 2000). But It has still problem.

CTDBA
Starting Member

9 Posts

Posted - 2008-06-12 : 09:29:18

As mentioned by mcrowley, high mem utilization is not, in and of itself, a problem. Is there evidence that SQL needs more memory to run efficiently, such as a lot of page faults, or a very low PAGE LIVE EXPECTANCY?

jungptic
Starting Member

7 Posts

Posted - 2008-08-02 : 10:46:52

I see it has a lot of reason for usage high memory. But I need to know How can I check it. Or if you has a tool for check it please tell me.

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-02 : 14:10:25

>> I was limited max Memory (set 2000).

Is 2000mb = 2gb? What do you want to check? How does sql use memory? Just run memory consumption report in ssms.

jungptic
Starting Member

7 Posts

Posted - 2008-08-02 : 14:53:01

quote:
Originally posted by rmiao

>> I was limited max Memory (set 2000).

Is 2000mb = 2gb? What do you want to check? How does sql use memory? Just run memory consumption report in ssms.

Yes, 2GB Ram, in SQL Server I set default memory. "ssms" I don't know it. In taskbar manager I saw SQL usaged 20~40% CPU. Now I want to limit memory with CPU it's only used 10~20%.

Please tell me detail it.
Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-02 : 15:01:29

You can't limit memory with cpu, and should have someone knows ssms to take care the server.

jungptic
Starting Member

7 Posts

Posted - 2008-09-03 : 03:39:56

quote:
Originally posted by rmiao

You can't limit memory with cpu, and should have someone knows ssms to take care the server.

What is SSMB? How can I use it ?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources