Question :
I’m running SQL Server 2014 on my laptop along with a ton of other installed applications, from Visual Studio to video games.
This is obviously not a production server, but for development and debugging only.
I’ve scripted out a fairly small and simple database, with a few dozen tables and views – and I’ve inserted less than 100 rows of dummy data for testing.
At one point I accidentally created a 100GB+ index, using some badly formed cross joins – the query creating this index was consuming 100% of the RAM in my laptop (16GB), which slowed everything to a crawl, so I aborted the query rather than waiting for it to finish, then promptly dropped and re-created the entire database with this index removed from the creation scripts.
However, SQL Server 2014 kept consuming all 16GB of my RAM from that point forward, even with the query aborted and the database dropped.
So I rebooted my laptop next, and the RAM usage for SQL Server dropped down to 7GB – where it has remained ever since, regardless whether or not I drop all of the 5MB of existing databases etc.
I’d like to get SQL Server back to a reasonable memory consumption level, but I don’t know where to begin looking for possible causes.
I know this is a pretty open-ended question, but I’m willing to do the legwork with a little guidance.
I just don’t know where to begin troubleshooting, since I’m not a DBA myself – and apparently Google doesn’t know either. 🙂
Answer :
First thing is to change the max memory setting on your instance using either the GUI or sp_configure.
EXEC sp_configure 'max server memory (MB)', 1024
Then go into SQL Server Configuration Manager and stop and re-start the instance. You don’t actually have to restart your laptop.
That will fix it so that your instance won’t use much more than 1gb. I’m fairly sure it uses memory that isn’t included in this setting but not a huge amount.
SQL Server likes to take memory for what it needs. It isn’t so good at giving it back so it’s a good idea to keep the max memory setting on your personal laptop fairly low unless you happen to need more. I use the 1gb setting for random small queries etc and if I need more I update it. Of course my laptop only has 6gb of ram total.
SQL Server is configured to work this way. It assumes that if you need the memory once, you’ll need it again. It’s expensive to constantly give this memory back to the operating system and ask for it back again, so why give any of it back to the operating system?
The easiest solution for now is to just restart the SQL Server service – this will return you to minimal RAM usage that won’t increase until you build data/indexes again.
But you should also set max server memory, as Kris pointed out above. Something less than 16GB (sorry, there is no “right answer” for that).