Memory goes to 97% and almost freezes entire system when querying in SQL Server

Bodhi108
Bodhi108 used Ask the Experts™
on
I'm having a memory issue when running SQL Server queries.

I have a 64 bit Windows 7 operating system with 4G of Ram with SQL Server 2008 R1.

I'm running a SP on my machine that runs on production every night that has 8G of Ram with the same SQL Server version.

Within minutes of starting the SP on my machine, the memory goes from 1.98G to 3.92G (97% usage).  I am not able to do anything else on my PC.  The SP takes about 15-20 minutes on production and if I let it run it will take over 3 or 4 hours or I get the BSOD...  which seems to create a dump with has to do with memory.

I've tested the memory cards and they seem to be fine.

Doesn't SQL Server swap out pages when the OS needs it for other processes?

Any suggestions on what I may try to do?  

Could it be as simple as needing 8G of RAM?  But why would my whole system come to a halt?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent DyerIT Security Analyst Senior

Commented:
From what you wrote, it seems to be only SPROC..  I would fire up SQL Tracing to find where your system is hanging.   Once, you have your trace file, then look for what your query is doing..  Consider looking at locking hints, breaking the query down to different components, etc.

Kind of hard to postulate what the problem is without the code here.

HTH,


Kent

Author

Commented:
Thanks kyder... although I don't believe it is the SP for 2 reasons:
1)  It runs on production every night and there are no problems.  And the database was pulled from production so we can't blame the input.

2)  It also happens in a simple select... well not too simple such as select... except select... in comparing 2 large tables.  The select statement can take 1 hour on my PC versus 4 minutes on production!  

Anyway, should it freeze up my entire PC while it is doing the query?  
Doesn't SQL server swap when the OS needs some of the memory?
IT Security Analyst Senior
Commented:
Being that you are running this on 64-bit, you may need to re-visit how you installed this..

http://www.eraofdata.com/blog/sql-server-memory-configuration/

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160955

I like SQLTeam as they are pretty helpful..

This maybe what you are looking for..
http://sqlserver-training.com/awe-for-32-bit-sql-server-on-windows-64-bit/-

I would also see about getting your system to R2, if possible.

HTH,

Kent

Author

Commented:
Kent,

The last article is in regards to AWE...  I believe using AWE is only if you need to use more than 4G of RAM.  I only have 4G of RAM so I don't need to use AWE.  The other 2 articles I did read previously to posting this and I don't think that it gave me any extra info...

Although, I think I may upgrade to R2.  Have you upgraded to R2?  Do you know if there are any issues with R2?  I don't think it solves any memory problems.

Thanks!
Abbi
Distinguished Expert 2017

Commented:
Check the configuration of your system properties of computer\advanced\performance settings\advanced.

How much space have you allocated to the page file?
Check whether your have boosted sql priority.

Use perfmon to see whether the issue is that it becomes I/O bound.

If you get a BSOD it would suggest that you have a faulty memory module in the higher range.

On the production system when the sproc is run is there a performance hit or is it scheduled in an off-peak period?

This could mean that the sproc is memory intensive.

Author

Commented:
Arnold...
I'm a DB designer and programmer, not a DBA so I'm just learning all of this...

Configuration of computer/advanced/perfomrance... was originally set to Automatically manage... then I had changed to to a custom size of 4086 a few days ago and didn't change anything.  And then earlier today I changed it to System managed size.  They all have the same problems...

How do I check for boosted sql priority?
Haven't used perfmon but I can see that CPU is low on Task Manager and Memory is high...

BSOD... Ran memory diagnostics using the diagnostic tool on the setup (F8)... How do I check for faulty memory module in the higher range?

Will  check production later to see if there is a performance hit.  Yes, it is scheduled off-peak but so is my machine with nothing else running.

SPROC is memory intensive but why would my entire machine come to a halt... can't run any other processes run?  Doesn't SQL Server let go of memory when the OS needs it?  Isn't there a swapping that is done?
Distinguished Expert 2017

Commented:
Is your system prioritized for programs processor and memory?

You would need to use SSMS and properties of the database server.  Processor is where you would have the option to boost sql priority.

What does the sproc do? does it write out data??

Author

Commented:
SSMS - processor properties ... Boost SQL Server priority is not checked.  Is that what where I would check to see if my system is prioritized for programs processor and memory?

Yes, the SPROC creates a temp table, and updates several tables...
Distinguished Expert 2017
Commented:
The Boost SQL will likely make things worse since you are already experiencing hits on the system's performance.

The program prioritization for processor and memory are under the properties of computer\advanced system settings link advanced\performance settings\advanced\.

How much data is being pumped into the temp table?

You do not want to let the system to manage it. Depending on how and what makes up your storage, within the same section where you adjust whether the system is prioritized for applications, when you click on the virtual memory, it should indicate what the system is recommending.


Top Expert 2012

Commented:
SQL Server is intended to be run standalone (the keyword being "server").  By design it will use as much memory as allowed by hardware/license.  Since you are not running SQL Server standalone on you box and it would appear that memory is a problem for you (as contrary to recommended best practices you are running other apps), you can limit the amount of memory used on your workstation by setting the max memory in the SQL Server properties.  Just understand that by so doing so, you are in a sense crippling SQL Server.
Aaron ShiloChief Database Architect

Commented:
hi

start with limiting the amount os RAM sql server can take.

http://msdn.microsoft.com/en-us/library/ms178067.aspx

then you should Tune your query.
q. does is open a very large transaction
a. then split the transaction into smaller ones.

q. does the query use sort options such as : join ,ditinct , union , group by, order by
a. add indexes , tune query , use temp stage tables to minimize impact

Author

Commented:
What's confusing is that the SP runs in less than 10 minutes in production on a server with 8G of RAM.  On my machine it takes over 4 hours with 4G of RAM and nothing else running...

I just ordered 4G of RAM that should be arriving today to see if that changes anything...

Author

Commented:
By the way...
The SP does use temp storage tables, joins and group by...  I've added extra indexes but still the same problem...
Top Expert 2012
Commented:
>>What's confusing is that the SP runs in less than 10 minutes in production on a server with 8G of RAM<<
What is so confusing?  One is a server where SQL Server is (hopefully) running standalone and the other is a workstation.  Very different animals.

Then again we may be talking apples and oranges.  In the sense that the databases may be vastly different in that one may have the stats updated and the other does not.  Without knowing the details it is difficult to know for sure.

>>I just ordered 4G of RAM that should be arriving today to see if that changes anything... <<
Don't hold your breath, I would be surprised if it makes any significant difference.

Author

Commented:
The database was restored from production so that the database is identical...  My personal PC has nothing else running it when I am running the SP...
Top Expert 2012
Commented:
>>The database was restored from production so that the database is identical...  <<
And you updated the stats after restoring?

>>My personal PC has nothing else running it when I am running the SP... <<
Trust me there is a lot more going than you think and again you are comparing a server to a workstation.  Very different animals.

Author

Commented:
No, did not update the stats.  I will.

Perhaps my restore wasn't done correctly.

Yes, I trust your knowledge more than mine since I don't consider myself a DBA but more of a programmer.  OK, so I can't really compare a server to a workstation, even though the SQL Server engine is identical...
Distinguished Expert 2017
Commented:
The issue might be with access to storage.

Do you have a single drive with partitions where you store the OS, datafiles while on the server you have one set of drives for the OS another set of drives for the data and transaction?

Change the database options from full to simple recovery model and see if that speeds up your SProc.


Use perfmon to monitor I/O on the disk and see whether it spikes in correlation to you running the sproc.

Author

Commented:
Unfortunately, this is a bigger problem that I thought.  My computer now gets the Blue Screen of Death so I believe this is no longer an issue with SQL Server.  It may be a hardware problem or .dll problem.  Our IT dept has been trying to debug the issue for over a week now.  I'm closing this question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial