Is this a memory leak?
-
When looking at memory usage on our DB server sqlservr.exe is using 1,382,465K. This seems excessive to me and I think its a memory leak.
How do I determine what is causing this?
What is your sqlservr.exe memory usage?
What is considered a nominal range of memory usage for sqlservr.exe?
We are on MSSQL 2000 SP 4a
We have 35 users accessing the DB via our ERP system.
tia,
Todd
-
Probably not. How much memory do you have on the server? SQL Server is designed to 'claim' almost every bit of memory. At its default, I believe it will 'claim' all but 128 MB which is needed by the OS.
Using Enterprise Manager, go to the instance and right click on it, select Properties, then go to the Memory tab. That is where you can adjust how much memory SQL Server will use.
FYI-this is one reason why Microsoft advises that you don't have any other application on the server running SQL Server.
-SQLBill
-
There are just so many posts on this for as long as I can rmember. Please read up BOL for admin of sql server or at least buy a good book , inside sql server say.
By default sql server will use as much memory as it can , how you have your server set up and the editions and o/s will make the usage. By default out of the box and no boot.ini switches sql server will use around 2gb ram, usually shows up to 1.7gb in usage via task manager. You can make some changes using max and min memory settings.
With the 3gb switch default memory is up to 2.7gb indicated ( usually ) doesn't apply to std edition and I'm asuming you're on a server.
with awe you cannot use task manger or most memory counters to see memory usage, this is covered in various kb articles.
the number of users has little impact on memory, one very alrge table scan can wipe all memory on the server. You need to list edition and o/s , switches and memory on server. I figure your server isn't working too hard as all I have that run on minimum grab 1.7gb ram straight out.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Collin,
I understand your frustration in regard to 'why doesn't this guy read the BOL?'. The problem is if you don't know the right question, reading/searching will/may be interesting but not very fruitful.
Both posts from you and SQLBill were very helpful and I thank you for your time and effort.
In summary:
Its not a memory leak and I need to see how throttling the memory used impacts performance.
Again, thank you for your help :>
Todd Canedy
-
Also, I would recommend leaving it alone unless you've got a good reason to decrease the memory available to SQL Server. If this is a stand-alone server, give SQL Server as much as you can. If this is not a stand-alone server, be very carefull when taking memory from SQL Server.
-
Here is an addition..
When SQL Server performs some operation, it loads as many related pages as it requires into the memory. For example, suppose you are running a stored proceudre which forces full scan of a table, then all the data pages (and index pages) related to the referred table will be loaded into the memory. Also, the execution plan of the stored proc will also be loaded in the memory. All the sorting, comaring, temporary storage locations will be created in teh memory only. When the stored procedure is completed (and your operation is finished), the already loaded data pages remain there in the meory until they are replaced by the pages belonging to some other database object being used by some other process. However, if all the connections ceased, or SQL Server engine is restarted, the memory is cleared. Also, there are certain DBCC commands that can clear buffer cache. Read books online for details on these commands.
-
Sorry Todd, it's just this gets posted so much I'm surprised a search didn't pull something up. I have to agree that finding the correct question is always difficult, what with the ever growing numbers of bloggs too it sometimes gets tricky to find what you need. Unless you have memory issues on the server then the last thing you want to do is throttle memory. RDBMS thrive on memory, the more you throw at them the better they perform ( in general terms )
Out of the box sql server manages memory for you, relative to editions and how much you have on your box. Unless your server has under 3gb of ram I wouldn't worry. If you have only 2gb then yes you might want to set an upper memory limit, especially if you have out of proocess apps / code running on the box.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Yep, SQL Server will grab as much memory as it can. That is why SQL Server (unless this is a very small departmental type app) should be a standalone database server.
On our boxes I stress this ! If possible since Standard Edition will take up to 2 gig have your server have 3 gig in it. That gives plenty of memory room for other things and SQL Server wont have to do a balancing act of consuming and releasing memory.
On our older servers we have two gig servers. I throddle SQL Server back to consume about 1.2 gig max. That way other services have room too.
-
Yep, SQL Server will grab as much memory as it can. That is why SQL Server (unless this is a very small departmental type app) should be a standalone database server.
On our boxes I stress this ! If possible since Standard Edition will take up to 2 gig have your server have 3 gig in it. That gives plenty of memory room for other things and SQL Server wont have to do a balancing act of consuming and releasing memory.
On our older servers we have two gig servers. I throddle SQL Server back to consume about 1.2 gig max. That way other services have room too.
-
Gentleman,
Your feedback on this issue is sincerely appreciated.
Being a generalist with responsibilities from resolving printer problems to developing business strategy and vision, I could not survive without you, and others like you willing to share insight and experience.
Many thanks to all.
Todd Canedy
-
You never said how much memory your server had in it. Gotta ask the dumb question, but what is telling you that sql server is using 1.2gb of memory?
How many times have you checked this figure?
Does it move up and down or has it slowly been increasing?
A memory leak will slowly allocate more and more memory until it hoses the system(in my experience with Visual foxpro). If the server isn't locking up and you are not experiencing any proformance problems, I wouldn't think it would be a memory leak. Also, I have never heard of a memory leak in SQL server 2000 or 2005. I would figure that would be a big issue in any database platform that would never make it out of development.(that was a shot at Visual Foxpro if you didn't get it)
Live to Throw
Throw to Live
Will Summers -
sadly there are some well documented memory issues with sql server, they can be pretty obscure and if you really want to read through the kb articles that relate to the "what we fixed" list in each sp then you'll maybe wonder how your server ever did run!! oledb and cursors are two that spring to mind straight off.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Will,
To answer your questions:
We have 2GB of memory on our DB server and are purchasing another 2GB today.
You can see how much memory sqlservr.exe is using by looking at the task manager processes tab. I sort by descending memory usage and sqlservr.exe is right at the top.
Collin is correct about the documented memory leak issues. I read most of those KBs which is what led me to believe we may have a memory leak.
We have custom applications using oledb and cursors. Also our BOM is six levels deep and exploding it or running costing utilities on 13000 parts takes a few minutes. Some people (including me) are not patient enough to wait so they stop the process. We don't do this often but often enough to possibly cause a cursor problem.
Todd
-
cool! it's an interesting old world!! Yup I'd say 4gb would be good.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
If you are running SQL Server 2000 Standard Edition, SQL Server can only use 2 GB of memory.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply