locked
SQLSERVR.exe taking up all memory?? RRS feed

  • Question

  • Hello SharePoint Fam,

    I have a dedicated SQL server 2008 R2 with 8GB of memory.  Currently when i login to this box it is moving extremely slow and whole site is moving very very slow.  Checked task manager and saw that sqlsvr.exe is taking up 7.81GB of the memory.  What is the solution to get this space issue down?  Do i reboot thebox, end the process and restart sqlsvr.exe?  It has been at around 7.81GB all day today and I have never experienced this at all.  Never thought this exe would take all memory to run. 

    This box has nothing but sql installed and that's its only purpose


    Thanks N Advance,


    • Edited by 41globalit Wednesday, July 10, 2013 8:16 PM misspelled title for sqlserver.exe
    Wednesday, July 10, 2013 8:15 PM

Answers

  • If you're not using high availability (clustering, database mirroring, log shipping) and do not require point-in-time recovery, set the content database to the Simple Recovery model (under Properties -> Options on the database), then shrink the LDF (log file).

    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by 41globalit Thursday, July 11, 2013 3:16 PM
    Wednesday, July 10, 2013 9:11 PM

All replies

  • This is normal for SQL Server to take up most of the physical memory.  Is this Standard or Enterprise edition of SQL?

    You can set SQL Server's Maximum Memory setting to limit the maximum amount of memory it can consume under the Properties of the server.


    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Wednesday, July 10, 2013 8:22 PM
  • It is the standard version.  What would you advise is a good max memory setting?? machine has 8gb?

    thanks,

    Wednesday, July 10, 2013 8:26 PM
  • I typically set it at 2GB less than the maximum physical memory.  If you used Enterprise, I would leave it as-is as Enterprise will allocate and release as needed.

    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Wednesday, July 10, 2013 8:29 PM
  • Was able to make that change and watched the memory drop down to max memory i set.  Is it normal for the sqlsver.exe to sit at that exact max number forever pretty much???  It has been about 5minutes now and it literally is now sitting right at the 6gb mark now and not moving up or down?

    Currently cached is 164

    available 2075

    free 1919

    Thanks trevor!


    • Edited by 41globalit Wednesday, July 10, 2013 8:44 PM adding memory numbers
    Wednesday, July 10, 2013 8:43 PM
  • Yep, this is normal.  SQL Server will take as much as you allocate to it in order to cache as much information in memory for faster access.

    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Wednesday, July 10, 2013 8:48 PM
  • The weird part is when i went into the max memory area there was number like 2145873....it is still running kind of slow...should I look into dropping max memory lower than 6gb??
    Wednesday, July 10, 2013 8:53 PM
  • That is just the default max mem.

    Is the performance impacting performance of SharePoint?  If not, I wouldn't worry about interactive sessions on the SQL Server.  You can also measure SQL Server performance counters, however providing direct guidance is fairly difficult as there are a lot of aspects to SQL Server performance (disk, memory, network, and CPU).


    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Wednesday, July 10, 2013 8:59 PM
  • Yes SharePoint is running extremely slow and dragging.  Sites/pages that normally take a second to come up are taking 10-15seconds to finally display.  Checked the web server and everything is fine there and nothing in event logs.  Also I am at 4gb out of 60gb left on db server.  Could that be a issue as well and make things slow?  I thought since i do have some space left that it shouldnt slow it down until there is none left.  i am working on freeing up space now.  

    Things are moving very slow in SharePoint designer.  Just bringing up small aspx page takes a minute to display before it took seconds to bring a file up check in/out

    • Edited by 41globalit Wednesday, July 10, 2013 9:08 PM more wording
    Wednesday, July 10, 2013 9:04 PM
  • Yep, free up some space and see if it improves.  Also, what kind of storage is underlying that 60GB of space (disk speed, # disks, RAID type, LUN type) and how large are your databases?

    If you have host-based antivirus on the SQL Server, have you excluded the SQL Server processes as well as file types (mdf, ndf, ldf)?


    Trevor Seward, MCC

      

    Follow or contact me at...


    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.


    Wednesday, July 10, 2013 9:06 PM
  • Basiclly 3 database files takes up the full 60gb right now

    Content Data = 32GB

    Content_Log = 12GB

    tempdb = 10GB

    symantec is installed and rnning on server and not sure what network team has setup but im sure they did not discard any of the databse files in symantec
    • Edited by 41globalit Wednesday, July 10, 2013 9:10 PM symantec info
    Wednesday, July 10, 2013 9:09 PM
  • If you're not using high availability (clustering, database mirroring, log shipping) and do not require point-in-time recovery, set the content database to the Simple Recovery model (under Properties -> Options on the database), then shrink the LDF (log file).

    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by 41globalit Thursday, July 11, 2013 3:16 PM
    Wednesday, July 10, 2013 9:11 PM
  • OK, not using any high availability options at all currently.  Using Idera backup software as backup solution which takes granular and full farm backups daily/weekly.  Will simple recovery take content database down at all or will changing to simple only stop logging as much from the point in time i make the change?

    Thanks so much trevor!

    Thursday, July 11, 2013 12:09 PM
  • Changing the recovery model does not take the database offline.

    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, July 11, 2013 2:28 PM
  • Im sorry trevor, i meant does it take down the content database in size at all or will I not see any different in size only moving forward it will not log as much???  thanks so much!
    Thursday, July 11, 2013 2:29 PM
  • It commits log entries directly to the MDF instead of storing them in the LDF, which allows you to shrink the LDF to ~1MB.  It does not change the size of the MDF.

    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, July 11, 2013 2:31 PM
  • ok gotcha.  So this bascially allows for the content database transaction log file not to grow as much but main primary database file will continue to grow as much as now?
    Thursday, July 11, 2013 2:38 PM
  • Yes, the MDF will continue to grow as data is added to it, but the LDF will remain small.

    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, July 11, 2013 3:04 PM
  • OK thanks so much sir!  I actually have a sql job that shrinks all transaction log files down each day after daily network backup as well.
    Thursday, July 11, 2013 3:05 PM
  • You should never shrink or mess with the main content database at all correct?  Currently content db is at 32GB and disk drive capacity is 60GB.  Network team keeps asking me to provide them a disk growth report for next 6 months but i cannot guess how 5k users will use SharePoint.  It's hard for them to understand that SharePoint is 100% database driven app and that everything pretty much resides in that one content db.
    Monday, July 15, 2013 12:26 PM
  • Shrinking databases should only be done infrequently. If you've scheduled it then you're generally doing it wrong ;)

    There are a few edge cases where you might want to shrink a content database but it is very rare, generally after you've just deleted a massive amount of data and don't expect it to be re-filled.

    Past behaviour is normally the easiest way to estimate growth, if you can find some of your old backups you can plot the size of those over time, if your rate of growth is stead then you should be able to see it and estimate the amount of space you need from that. It's not perfect but the more comprehensive approaches are generally a lot more trouble.

    Monday, July 15, 2013 12:32 PM