7

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. :)

Improve this question
2
  • 2
    Have you tried setting max server memory? SQL, by default, will consume all the memory if you don't limit it.
    – Kris Gruttemeyer
    Commented Jun 29, 2015 at 15:56
  • @KrisGruttemeyer Thanks for the tip, I'll Google that and see if I can figure out how to set a reasonable memory limit.
    – Giffyguy
    Commented Jun 29, 2015 at 15:59

2 Answers 2

Reset to default
6

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.

Improve this answer
7
  • 2
    This worked like a charm. I didn't even have to restart the service at all. I just changed the max memory to 512MB in SSMS, then watched in my Task Manager window as the memory usage dropped almost immediately to 380MB. No doubt SQL Server was just keeping the memory on hand with anticipation of future needs etc.
    – Giffyguy
    Commented Jun 29, 2015 at 16:12
  • 1
    @Giffyguy don't ever use Task Manager, it's a big liar. See this answer.
    – Aaron Bertrand
    Commented Jun 29, 2015 at 16:13
  • 1
    Don't forget if you put your max memory too low you may run into problems running test queries. You might also set the startup to "manual" in SQL Server Configuration Manager and only start up the instance when you need it.
    – Kenneth Fisher
    Commented Jun 29, 2015 at 16:14
  • @AaronBertrand and Kenneth, thanks for the great info. I'll keep these things in mind. You guys have been a huge help.
    – Giffyguy
    Commented Jun 29, 2015 at 16:17
  • 1
    @Giffguy:FYI SQL Server can use more that the limit set in max server memory so don't get surprised if SQL server starts using more than 512 MB. To be on safer side I would suggest to change max server memory to at least 3-4 G. Or you can change it when you are doing testing 512 MB is really a low value
    – Shanky
    Commented Jun 29, 2015 at 16:19
4

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).

Improve this answer
1
  • This was exactly what was going on. SQL was holding on to memory with anticipation of future needs. I changed the max memory to 512MB in SSMS, and watched the memory usage immediately drop to 380MB in Task Manager. I didn't even need to restart the service.
    – Giffyguy
    Commented Jun 29, 2015 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.