Adjusting SQL embedded instances

On December 26, 2007, in Allocated Memory, by

My 1 gig SBS box at home is wheezing  a bit because it’s got WSUS 3 on here and 1 gig is just not making it anymore.  So I looked under the hood to see what (if anything) I could stomp on and I could see that in task manager sqlserver.exe with PID of 1832 was bumping up a bit.  So let’s see if we can put a limit on that.



At a command prompt on the server I typed in tasklist /svc to see what PID 1832 was. 



See the 1832 there?


See the PID of 1832 there in the task manager above?


Remember how we fix this?  This time we’re doing this on a server that has WSUS 3 on it with SQL embedded.


http://go.microsoft.com/fwlink/?LinkId=65110  I downloaded that GUI tool to help me manage SQL 2005 embedded.  I then used \\.\pipe\mssql$microsoft##ssee\sql\query in the computer query field.



 


I then right mouse clicked on the properties like so…



And as you can see, you can now easily put a memory max limit to it:



Now if my box had 2 gigs, I’d limite WSUS to 256 as per the “this is what has worked for us” table says:


MSFW 128
WSUS 256  (per Dr. J’s blog)
SBSMonitoring  70
SharePoint  (I didn’t tweak this)
If CRM is on the box, then limit SharePoint to 384
 


But I’m going to pull mine down just a little bit more and then when I get an additional gig of RAM on order I’ll bump it back up again.


I’ll restart the SQL instance for good measure (just right mouse click like you did the first time and click on restart.


Now monitor and see what happens.

 

5 Responses to Adjusting SQL embedded instances

  1. Dean says:

    For SQL make sure you turn off Hyperthreading in the BIOS. Hyperthreading slows SQL down.

  2. Chris Knight says:

    No need for tasklist if you’re using Sysinternals’ Process Explorer – hovering over the entry gets you the SQL instance information.

    Also, if you don’t want to download SQL Management Studio, you can follow the steps in MSKB 909636.

  3. Chris Knight says:

    Dean – got an authoritative reference for this? Benchmarks?

  4. You save the day. Now how can i do it to 212 server in one shot.

  5. John says:

    Thanks for the tip. Most helpful!