Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Server Eating my PCs Memory

Author  Topic 

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-17 : 10:51:59
Hello,

I have been having some quirks with SQL Server 2008 r2 on my machine... I am running a local server on my PC that only I have access to for testing purposes and I do a lot of data conversions from a client's system to our database structure so a lot of my queries are 1000+ lines that have a lot of joins, case statements, etc. When I run my first conversion script my PC goes from having around 25% memory used to about 35%, I was for that query to complete, then start another query. When this happens it goes from around 35% to about 45% and this continue until my computer maxes out at 99% memory used and I am only running one query at a time.

If I close out of SQL Server the memory is still being used and the only way to get it go back to normal levels is opening task killers, going to processes and finding the sql server process that is taking up 7.5 gb of memory. When I do this and try to reopen SQL Server, it won't connect to my local server unless I do a restart on the PC.

Any ideas what I can do to fix this or what is going on?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-01-17 : 11:02:06
This is normal behavior for SQL Server. It will use as much memory as it can as this is optimal for performance. You can set a maximum memory limit using the procedures listed here: http://msdn.microsoft.com/en-us/library/ms178067.aspx
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-17 : 11:15:46
Even when no queries are running? I don't mind it eating my memory while I am running a query... What I thought was odd is that it continue to eat it even when nothing is running in SQL Server...
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-17 : 11:28:05
If you kill it in your task manager - you are killing SQL server. Go to services.msc and start the SQL Server Instance. Normally , you do not have to restart the PC.
What is your current server memory. How much is your SQL Server Instance max memory set at?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-17 : 11:55:49
The server is on my computers hard drive... It is just a test instance that I can use to put data into and make sure it links up with the program it is linked to correctly. Then I import the clients database(s) on my machine and write scripts to put their data into our system.

The specs for my machine is an i5 processor with 8gb of memory and a 250gb hard drive.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-01-17 : 12:33:03
quote:
Even when no queries are running? I don't mind it eating my memory while I am running a query... What I thought was odd is that it continue to eat it even when nothing is running in SQL Server
It keeps any data it processed in RAM, that way if you run a query that needs the same data it doesn't have to read it from disk. If it kept releasing the memory after each query was completed it would be much slower.

For best results your server should be dedicated to SQL Server and nothing else. If that's not possible then set a max memory limit on SQL Server so that enough RAM is available for other processes to use.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-18 : 12:47:55
As well as suggestions mentioned by robvolk. Look at setting the max memory accordingly on the server, ensuring you leave enough memory for OS and other applications

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-21 : 15:50:25
Thanks, mates. I have set my max memory that SQL Server can go to as 6.5 GB and it isn't crashing my PC as bad.
Go to Top of Page
   

- Advertisement -