Saturday, 2 July 2011

SBS 2011 – WSUS SQL Memory Usage Is Very High – How To Reduce It

Our destination SBS 2011 has an SQL (S-Q-L) process that was taking up a whopping 5GB of RAM:

image

Since this was a relatively fresh install of SBS 2011 we needed to find out what SQL setup was using so much memory.

  1. Open Task Manager –> Processes tab.
  2. View –> Choose Columns –> PID, Memory, etc. (Our SBS 2011 Setup Guide has the customizations)
  3. Start –> CMD –> Right click and Run As Admin.
  4. tasklist /svc [Enter]
  5. Scroll through the resulting list looking for the PID.
    •  image
  6. Start –> All Programs –> Microsoft SQL Server 2008 R2 –> Right click and Run as Admin on SQL Server Management Studio.
    • Server type: Database Engine.
    • Server name: \\.\pipe\mssql$microsoft##ssee\sql\query
      • Copy and paste the above.
    • Authentication: Windows Authentication.
  7. Click Connect.
    1. image
  8. Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and left click on Properties.
  9. Click Memory.
  10. Change the amount of memory that WSUS can use. On this particular SBS 2011 VM we have 16GB of RAM allocated. So, we will set the amount of RAM to 1GB.
    1. Before:
      • image
    2. After:
      • image

Our WSUS SQL memory usage was now a lot more reasonable:

image

image

UPDATE 2011-07-16: Tweaked the steps for a typo as per Arcon’s comment.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists
Co-Author: SBS 2008 Blueprint Book

*Our original iMac was stolen (previous blog post). We now have a new MacBook Pro courtesy of Vlad Mazek, owner of OWN.

Windows Live Writer

at

15 comments:

  1. Seanpt07 July, 2011 20:50

    I've added this into my setup guide (that borrows heavily from your awesome setup guide).

    ReplyDelete
  2. Arcon16 July, 2011 13:28

    You got a typo.
    Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and \\.\pipe\mssql$microsoft##ssee\sql\query.
    Should be I think
    Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and PROPERTIES

    Anyway - great great job you're doing - by sharing all this knowledge with us - what else could we please for ?:) You helped me with many many things:) Keep up the good job guys and inspire us to search for more and more:)

    ReplyDelete
  3. Philip Elder Cluster MVP16 July, 2011 13:47

    Arcon,

    Good catch! :)

    My eyes must have been crossed when I was putting that together.

    Philip

    ReplyDelete
  4. Anonymous09 February, 2012 23:48

    Following your steps, trying to right click on SUSDB to get to properties, I get the following error:

    Property Owner is not available for Database '[SUSDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

    Any ideas?

    ReplyDelete
  5. Unknown26 March, 2012 21:13

    I am having the same problem


    Property Owner is not available for Database '[SUSDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)


    Any ideas?

    ReplyDelete
  6. Anonymous06 April, 2012 03:58

    Philip, thank you for this info.
    my server is a vm and limited to 6 GB of memory so if sql server is using like 3 to 4 GB its noticeable wen working with the server.
    Limiting the memory worked immediately and my server is a lot more responsive.
    @anonymous users, Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and PROPERTIES not SUSDB!!!!

    ReplyDelete
  7. Anonymous26 February, 2013 09:59

    I am getting the same error even running as ADMIN.

    Property Owner is not available for Database '[SUSDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

    ReplyDelete
  8. Anonymous18 March, 2013 06:31

    I'm sure you figured it out by now, but you don't have to call up the properties for the SUSDB node but for the root, i.e. the properties of \\.\pipe\mssql$microsoft##ssee\sql\query

    The screenshot that has SUSDB selected is a bit confusing, I give you that.

    ReplyDelete
  9. Anonymous24 April, 2013 13:16

    What impact will this have on the sql server?

    ReplyDelete
  10. Anonymous05 June, 2013 09:13

    Yes!! What a tip, thank you! The SBS is useable again. After a year it feels like the SBS 2011 machine is finally being configured as it should be. The big question is why Microsoft didn't fix this in the standard SBS installation.

    ReplyDelete
  11. Todd H.11 September, 2013 16:31

    Just a note to say thanks. Easy to understand post that solved my issue. Keep posting!

    ReplyDelete
  12. Anonymous15 October, 2013 12:11

    Used your steps so sql would only use 1024mb of ram. It helped.

    Thanks!

    ReplyDelete
  13. Anonymous18 July, 2014 10:41

    Thank you, we still rely on our SBS 2011 server (running on Dell server with 24G RAM). Your post has made our little part of the world better.

    ReplyDelete
  14. Mark12 September, 2014 07:32

    Worked like a champ! I had 2 SQL non-critical databases eating up the majority of my RAM. I used these instructions and my server is back to its old self again.

    Many thanks!!

    ReplyDelete
  15. Chuck12 May, 2015 11:48

    Hi --

    I had this same SBS database chewing up a ton of physical RAM on an SBS 2011 box. I followed these instructions to reduce the maximum memory to 2 GB (the machine has 24 GB of RAM). I also reduced the "minimum memory per query" from 1 GB to 0. After a lot of "Executing" time, this instance's physical RAM consumed rose from a little over 5 GB to close to 7 GB. Any ideas?

    ReplyDelete

NOTE: All comments are moderated.