SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Server Eating my PCs Memory
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cstokes91
Yak Posting Veteran

USA
60 Posts

Posted - 01/17/2013 :  10:51:59  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 01/17/2013 :  11:02:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
60 Posts

Posted - 01/17/2013 :  11:15:46  Show Profile  Reply with 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...
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 01/17/2013 :  11:28:05  Show Profile  Visit jackv's Homepage  Reply with Quote
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

USA
60 Posts

Posted - 01/17/2013 :  11:55:49  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 01/17/2013 :  12:33:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 01/18/2013 :  12:47:55  Show Profile  Visit jackv's Homepage  Reply with Quote
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

USA
60 Posts

Posted - 01/21/2013 :  15:50:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000