SQL 2000 Chewing Memory
- Hi All,I'm new to this forum and to SQL in General, but years of IT experience and general SQL knowledge tell me that a SQL Server shouldnt be chewing up 1.3gb of memory in 24 hours.. considering the staff here switch off their PC's everynight.Server specs are as follows.P4 3.2 HT Processor2Gb MemorySingle SATA 160Gb Hard DriveSQL 2000 with latest Service PackWindows 2000 with latest service pack and patches from MS.3rd party SQL Application (which I suspect is suspect)1Gb Network card running at 1GbDatabase sitting at 3Gb.I suspect that the 3rd party application isnt closing the thread after each query run on the database. Could this be the problem?Please help!!!AC
Jack of all Trades - Master of None. -
Check in TaskManager to see how much memory your SQLServer process is actually using. If it is using 1.3 Gb itself, check the server properties through Enterprise Manager (right-click on the server name and select Properties), by selecting the Memory tab. This will show how much memory SQLServer has been configured to use.
SQLServer will take as much memory as it needs, up to a maximumm limit imposed either by a config setting (memory_max), or by the operating system - whichever happens first. If you've configured SQLServer to be able to take 1.3 Gb then don't be too surprised if it does exactly that. As long as this leaves adequate memory for the OS and any other software on the machine then this shouldn't be a problem.
-
As Philip said, that is perfectly normal under the following conditions:
1. Memory allocation is not constrained by the SQL Server Configuration Option: Max Server Memory
2. Applications, if running on the SQL Server, are not requesting memory that SQL Server has allocated. SQL Server will relinquish allocated memory if other apps require it.
The majority of the memory is used for the buffer cache. If SQL Server can grab memory, it will (that's a good thing!). If another app or the OS needs additional memory, SQL Server will relinquish it (another good thing!).
You can limit the amount of memory that SQL Server will use by changing the Max Server Memory value via SEM or command-line. It is a dynamic property that takes effect immediately. However, normally, unless you are experiencing issues with the application's performance or excessive page swapping, the min/max memory values do not need to be adjusted.
That being said, I usually configure the max limit to approximately 75% of physical memory on SQL-only systems with 1 - 3GB.
For those systems that MUST run other apps & SQL Server on the same box, I try to monitor the app's memory consumption and adjust SQL Server from the above figure to allow a little wiggle room for the app. Time is then spent monitoring page swapping to fine tune the memory allocation.
- Hi Philip,Thanks for the reply.. The SQL Server process is actually taking up 1.3Gb, and was actually as high at 1.7Gb. The problem is , the users complain about slowness of the system and errors that they get once it gets this high..RegardsAlanJack of all Trades - Master of None
-
Odds are, it is not due to the memory consumption. Most of that memory, as R2ro said, will be used for buffer cache. This is where your recent data pages will be stored. The higher memory consumption is most likely due to increased volume on the system. The more system activity, the more the buffer and procedure caches will be used; hence, memory usage will go up. SQL Server is designed to respond in this manner. I would guess that your resource bottleneck is your single hard disk but I can't say for sure w/o more info.
You said that your users are getting errors. What errors are they getting?
- Hi Guys,Users are getting runtime error, timeout errors etc etc..Alan
-
Not sure you're specific errors, but I too have seen sqlsrv.exe process taking up to 2 GB (which is the max that SQL 2000 Standard can use (not sure what edition you have - only Enterprise Edition can use more)).
But when I have 2 GB total RAM on the machine and SQL is using all of it then that leaves nothing for the OS and then I could experience slow downs. I always wanted SQL to use it's max memory since that should technically help SQL performance if it doesn't have to go all the way to disk. I sometimes add another 1 GB that will then be available just for the OS and other non-SQL processes.
As far as the runtime errors and timeout errors - are these SQL timeouts, IIS timeouts, something else? If you're getting SQL timeouts then you may have SQL blocking or even deadlocking happening. You can monitor blocking and you can even enable some trace flags (like trace flag 1204) for monitoring any possible deadlocking. For IIS timeouts you could check IIS logs, etc.. so depending on what these errors specifically are you should have some logical next steps..
Good luck.
-
Alright a good question but this might have nothing to do with sql server directly.when the machine is slowing down to the user run sp_who you will see at least a couple of programs which have about close to 100 thread saying sleeping !!!!
By default a good locked up app with say a few hundred thousand users will run with 40 threads but due to the bad code their are memory leaks in the app , which are spawning multiple threads which hit the sql server default max of 100 per app.
solutions
Immediate
1)Turn pooling off on this app this make the sql open and close connections and slower speed[faster than before though] and will the boat from sinking for now.
2) Open every app and find the leaks and fix it !!!! takes time i know but their is no fix for bad coding.
3) Deploy the new app and opening pooling you will see some major resources free and more importantly happy users.
satz
btw, another jack of all trades!!!
-
I'd suggest you add some more disks, running a sql server on one disk will not be good, it doesn't matter how much data cache you have if the disk is i/o bound for log writes. If you're getting timeouts then that's an app thing caused by slow response on sql server, I suggest you get perfmon working or download a demo of Diagnostic Manager from Quest Software - whatever you do don't attach spotlight .
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply