sqlservr.exe high memory bug?

Out of the blue my server started to have the sqlservr.exe process take a huge chunk of my memory. It's taking 31 out of 32 gb. We only have a couple very small databases, why in the world out of the blue would it start taking so much memory? It's causing our server to freeze up.

After stopping the process it goes back down to normal. But I need to fix it so we can run it again.
LVL 4
Pancake_EffectAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Simply put SQL Server and Exchange are competing for all the memory available.  This is why typically they should never be installed on the same box.  Since you have done that with SBS you are going to have to find a way to limit the memory used by SQL Server.  This will hurt performance (it will not have as much memory to use) but at least your other apps can run.   Since you obviously have never used SQL Server, I suggest you use the GUI in SSMS to set the maximum memory.  Let me know if you need help with that.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
That's how a database server should, i am assuming you dont run any apps on that server ? Also , if you want to setup a limit, you can configure that for SQL Server ,, from ssms , right click on the server -> properties -> memory., there you can set the max memory to somewhere around 28GB.
Keep in mind, it is not a good practice to run the other apps on the database server.
0
 
helpfinderIT ConsultantCommented:
Could be. I am affraid it´s Microsoft´s feature not a bug ;)
Try this commands:

    from admin CMD run osql -E -S .\msfw
       in case of error appears, change locality in Regional settings to English
    USE master
        GO
    EXEC sp_configure 'show advanced options', 1
        RECONFIGURE WITH OVERRIDE
            GO
    now is goot to use command to show current max alocated RAM for this SQL instance
        EXEC sp_configure 'max server memory (MB)'
            GO
    following by command to change max memory based on your desicion, e.g. 2048MB
        EXEC sp_configure 'max server memory (MB)', 2048
            GO
    and last command

        EXEC sp_configure 'show advanced options', 0

        RECONFIGURE WITH OVERRIDE

        GO

give it a time to start reducing the memory
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Anthony PerkinsCommented:
You are looking at the symptom rather than the disease!  You have a query or queries that are causing your "server to freeze up".  There are any number of reasons this could be happening: badly optimized query, blocking, etc.  It is your job to identify the queries that are causing you grief and fix them.  There are a number of third party products that do this or you can use a SQL script such as Adam Machanic;'s sp_whoisactive.

And yes, for performance reasons SQL Server will attempt to use all the memory you allow.  If you have never noticed this before, than you have not being paying attention.  :)
0
 
ste5anSenior DeveloperCommented:
In addition to the answers of Aneesh and helpfinder:

SQL Server uses this memory especially to buffer your data in memory. This means, even if you have only small databases, their added size in GB is greater than 31GB.

So using a fixed, smaller max memory value means, that data must be read much more often from disk. This can mean a performance degradation.

Also important: Once the memory is acquired by SQL Server, it is kept by it even when it is not used.

So finding a correct value for max memory isn't that easy.
0
 
Anthony PerkinsCommented:
So finding a correct value for max memory isn't that easy.
Then I suggest you read Glen's excellent article on the subject: Suggested Max Memory Settings for SQL Server 2005/2008
0
 
Pancake_EffectAuthor Commented:
Okay guys I have an update. I've found out it's a combination of a few services causing this.

sqlservr.exe, dns.exe, w3wp.exe, store.exe causing high memory
0
 
Anthony PerkinsCommented:
You may want to read this thread, I suspect you will find all the answers here.  If not provide some relevant feedback to the suggestions offered.
0
 
Pancake_EffectAuthor Commented:
@helpfinder

I tried those commands, but it states the below after the very first one:



@Anthony Perkins

I read the posted link, however where do you enter those command?


While SQL is taking a lot of memory, it now also looks like the STORE.exe is taking more.

We only run one popular third party vendor program  for two users in SQL, other than the default ones that come and run with server 2011 SBS. And it's a very popular third party vendor program.

Usually our server idled at about 40%. Now it's about 98%

I have no idea why these services all decided start hogging all of our memory. We have 32GB on it.

Usage

This is also a legacy server, we only have about 5 computers on the domain still using this server.
0
 
ste5anSenior DeveloperCommented:
0
 
Pancake_EffectAuthor Commented:
Ive heard a lot thay it's normal from this site and others that I've googled, but it happening out of the  blue should not be I imagine . Would there be a reason ot started happening out of the blue?  In fact it should have gone down being that it's a legacy server. I hate treating the symptoms by limiing memory if its just a incorrect setting or something that needs to be patched
0
 
Pancake_EffectAuthor Commented:
Thanks for the feedback everyone, it's not what I was wanting to hear haha (that it's normal). I used the guides mentioned above and Google searched some to limit the store.exe and SQL services. It's now running at a somewhat manageable level 90%.

One last thing I wanted to ask. I mean this is SBS 2011. These services come native with the OS. I thought it's normal to run these services given it's a small business server, I thought it was normal to not have multiple servers for a small environment like this. Store.exe is taking 3/4 of the memory just to run, and we have like 5 users on it. With the SQL the service taking the rest of it or so just to run the SQL database for WSUS apparently. We only use one other SQL database and that's only taking 2GB.

Just to clarify one last time, is that still normal?

Thanks!
0
 
Anthony PerkinsCommented:
Yes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.