1:00 AM
Paul B
First of all, I would like to stat this article out saying that I am not a DBA (Database Administrator). I am sure there are plenty of DBA’s out there that know there way around SQL way better than me. Seeing as how my company doesn’t have a dedicated DBA, I am stuck messing with it in order to keep things working.
How many of you have deployed a Microsoft SQL 2005 or 2008 server and after you are done with the initial install and setups you notice that SQL is hogging every little bit of memory you have? How many of you have tried running SQL on VMware? You probably noticed that too right? There is a reason for this, by default Microsoft SQL is designed to take as much memory as you give it up to 2147483647 MB (That is 2 Petabytes of memory!) WTF is that all about Microsoft?
I have one SQL server that only has 8GB of physical memory in it, and it is constantly pegged because of this, so what I did was the following:
- Went into SQL Server Management Studio
- Right Clicked on the SQL Instance Name and selected properties
- Under Memory, I changed the value from 2147483647 MB to 6144 MB (6GB)
- Clicked OK, then rebooted
Once the server came back up, SQL was limited to only 6GB of RAM, leaving 2GB for the operating system. After that, everything ran a little smoother.
Are any of you DBA’s? Any idea on why Microsoft sets the default memory max so high? Let us know in the comments.
