Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql memory usage issue Expand / Collapse
Author
Message
Posted Wednesday, December 9, 2009 2:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 30, 2012 6:00 AM
Points: 140, Visits: 1,010
Hi all,

One of my production sqlserver is taking more memory usage

in win task manager ----> Process it is showing sqlservr.exe :1,337,400k
server is of 2gb RAM

when ever this issue comes we r restarting the server for time being its ok .

But every time restarting the production server is not the good criteria


all users are complaining the server is slow () it is taking too much time to insert data from front end application.

we are using

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: Service Pack 2, v.4354)

can pls send the some valuble solution to fix this issue (w/o restarting the server).


Thanks in advance
Post #831215
Posted Wednesday, December 9, 2009 2:57 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:32 AM
Points: 644, Visits: 1,299
Set up a trace and identify any query that hurts performance

-- Roshan Joe
*******************************************
There are two types of DBAs. Those who has skills and those who have permissions
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #831238
Posted Wednesday, December 9, 2009 2:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:21 AM
Points: 1,159, Visits: 4,658
Hi,

in win task manager ----> Process it is showing sqlservr.exe :1,337,400k
server is of 2gb RAM


First check the memory using perfmon.msc

See the discussion

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: Service Pack 2, v.4354)


Personal Edition maximum support 2GB.So u need to upgrade ur sql edition.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #831239
Posted Wednesday, December 9, 2009 3:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
With help of perfmon you can check following memory related counters:

SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\Total Server Memory (KB)
Memory\Pages/sec

Also you can add few more counters and check memory as well as any other hardware bottlenack if any.


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #831261
Posted Wednesday, December 9, 2009 8:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
The numbers you posted sound normal to me for SQL Server. It sounds to me like you don't understand how SQL Server uses memory. Take a look through my blog post:

Troubleshooting the SQL Server Memory Leak (or Understanding SQL Server Memory Usage)

If you don't want SQL Server to use all of the available memory, then you need to configure the max server memory setting to control how much it can use. Slow inserts won't be associated with SQL Server using memory, in fact reducing memory to SQL can actually cause further problems with performance of inserts since you are going to force SQL Server to have to do more hard IO to disk which impacts write performance as a side effect.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #831437
Posted Wednesday, December 9, 2009 8:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
What are the top 10 wait types for the SQL Server?

SELECT TOP 10 *
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TRANSMITTER',
'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE',
'ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC



Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #831441
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse