Solved

sqlservr.exe high memory bug?

Posted on 2014-03-05
13
4,005 Views
Last Modified: 2014-04-18
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.
0
Comment
Question by:Pancake_Effect
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39906990
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
 
LVL 19

Expert Comment

by:helpfinder
ID: 39907031
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39908285
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
 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 200 total points
ID: 39908655
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 39911270
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
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 39945495
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39946654
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
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 39950373
@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
 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 200 total points
ID: 39950422
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 39950854
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 39950919
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
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 39960232
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 39960709
Yes.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now