Does large SQL Server Memory Usage cause errors?

Question

Just started getting a bunch of errors on our C# .Net app that seemed to be happening for no reason. Things like System.IndexOutOfRangeException on a SqlDataReader object for an index that should be returned and has been returning for a while now.

Anyways, I looked at the Task Manager and saw that sqlservr.exe was running at around 1,500,000 K Mem Usage. I am by no means a DBA, but that large usage of memory looked wrong to me on a Win Server 2003 R2 Enterprise with Intel Xeon 3.33Ghz with 4GB ram. So I restarted the SQL Server instance. After the restart, everything went back to normal. Errors suddenly stopped occurring. So does this large main memory usage eventually cause errors?

Additionally, I did a quick Google for high memory usage mssql. I found that if left to default settings; SQL Server can grow to be that large. Also, found a link to MS about How to adjust memory usage by using configuration options in SQL Server.

Question now is...how much main memory should SQL Server should be limited to?

Answer 1

There are a lot of different factors that can come into play as to what limit to set. Typically you want to limit it in a manner that will prevent it from using up too much of the ram on the system.

If the box is a dedicated SQL box, it isn't uncommon to set it to use 90% or so of the RAM on the box....

However, if it is a shared box that has other purposes, there might be other considerations.

Answer 2

how much main memory should MSSQL should be limited to?

As much as you can give it, while ensuring that other system services can function properly. Yes, it's a vague answer, but on a dedicated DB box, MSSQL will be quite happy with 90% of the RAM or such. By design it will take as much RAM as it can.

Answer 3

SQL needs the ram that it is taking. If it was using 1.5 gigs, its using that for data cache, procedure cache, etc. Its generally better left alone - if you set a cap too low, you'll end up hurting performance. If its using 1.5 gigs on a 4 gig web box, i wouldn't call that abnormal at all.

Your errors could very likely have been caused by locking - i'd have a hard time saying that the SQL memory usage that you defined in the question was causing the errors you were getting.

Answer 4

I'd certainly be very surprised if it's the database itself, SQLServer is an extremely solid product - far better than anything in Office or Windows itself, and can generally be relied on absolutely and completely.

1.5Gb is nothing for a rdbms - and and all of them will just keep filling up their available buffers with cached data. Reads in core are typically 1000x or more faster than disk access, so using every scrap of memory available to it is optimal design. In fact if you look at any RDBMS design theory you'll see that the algorithms used to decide what to throw away from core are given considerable prominence as it makes a major impact on performance.

Most dedicated DB servers will be running with 4Gb memory (assuming 32bit) with 90% dedicated to SQL Server, so you are certainly not looking at any sort of edge condition here.

Your most likely problem by far is a coding error or structural issue (such as locking)

I do have one caveat though. Very (very, very - like twice in 10 years) occasionally I have seen SQL Server return page tear errors due to corruption in its database files, both times caused by an underlying intermittent hardware failure. As luck would have it on both occasions these were in pages holding the indexes and by dropping the index, repairing the database, backing up and restoring to a new disk I was able to recover without falling back to backups. I am uncertain as to how a page tear error would feed through to the C# API, but conceivably if you have a disk error which only manifests itself after core is full (i.e. it's somewhere on some swap space) then an index out of bounds error does seem like the sort of manifestation I would expect as a call could be returning junk - hence falling outside an array range.

Answer 5

1.5GB of 4.0GB is hardly taxing... One of our servers typically runs at 1.6GB of 2.5GB with no problems. I think I'd be more concerned if it wasn't using that much.

I don't mean to sound harsh but I wouldn't be so quick to blame the SQL Server for application errors. From my experience, every time I've tried to pass the buck on to SQL Server, it's bit me in the ass. It's usually sys admins or rogue queries that have brought our server to its knees.

There were several times where the solution to a slow running query was to restart the server instead of inspecting the query, which were almost always at fault. I know I personally rewrote about a dozen queries where the cost was well above 100.

This really sounds like a case of "'select' is broken" so I'm curious if you could find any improvements in your code.