July 18, 2011 at 3:57 pm
My customer has the following:
Windows Server 2008 R2 64-bit VM with 24GB of memory allocated. (The other VM is the test system with only 8GB of memory allocated)
Running SQL 2008, SP2 with Cumulative updates 4.
The system actually consists of 5 databases, most of them being fairly small, however one of them is just under 40GB.
Whenever they run a fairly data intense query from the software, we aren't getting the following error in the event log:
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:701
Date:7/13/2011
Time:7:13:41 PM
User:FS-H2O-SR-AMR\watersql
Computer:FS-H2O-SR-AMR.fresno.gov
Description:
There is insufficient system memory in resource pool 'internal' to run this query.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: bd 02 00 00 11 00 00 00 ½.......
0008: 0e 00 00 00 46 00 53 00 ....F.S.
0010: 2d 00 48 00 32 00 4f 00 -.H.2.O.
0018: 2d 00 53 00 52 00 2d 00 -.S.R.-.
0020: 41 00 4d 00 52 00 00 00 A.M.R...
0028: 0d 00 00 00 52 00 65 00 ....R.e.
0030: 70 00 6f 00 72 00 74 00 p.o.r.t.
0038: 53 00 65 00 72 00 76 00 S.e.r.v.
0040: 65 00 72 00 00 00 e.r...
The system also has problems at that same time trying to input data, giving us the same error in our log files.
I am looking for any additional TS we can try to determine what is causing the problem. This has only started to become a problem more recently, and one thing we have done is reallocated the amount of memory from 16GB/16GB to 24GB/8GB. The query is accessing 24 reads a day for 1 year worth of data, while another independent service is trying to input new values.
Any assistance is appreciated.
LD
July 18, 2011 at 5:11 pm
I don't know if any of these are relevant to your situation.
http://support.microsoft.com/kb/982854
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152533
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 9:50 am
So we know it is not the bug issue you referenced, since we have all of the updated installed.
However, we haven't tried to lock the page in memory yet, mainly because this is a 64-bit OS, and 64-bit version of SQL.
Any other suggestions before we give this a try? it is a system dedicated to this database and one service so I don't think this should be a problem.
LD
July 19, 2011 at 10:03 am
Sorry, I double checked and it appears that locking pages in memory is not required on 64-bit operating systems.
I'm not sure but I will try and find something.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 10:28 am
According to Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
In reference to a Microsoft Article, says "Ignore what it says about 64-bit. It is absolutely needed on 64-bit. There is plenty of documentation out there to show this, so feel free to goggle it. The link I posted was to show you the procedure how to do it"
How to: Enable the Lock Pages in Memory Option (Windows)
http://msdn.microsoft.com/en-us/library/ms190730.aspx
I don't know what else to tell you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 10:50 am
lostdisk (7/18/2011)
This has only started to become a problem more recently, and one thing we have done is reallocated the amount of memory from 16GB/16GB to 24GB/8GB. The query is accessing 24 reads a day for 1 year worth of data, while another independent service is trying to input new values.
What led to the decision to reallocate memory?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 11:43 am
The other admin did it to try to prevent the problem (since the other VM is just a test version of the production server). This has worked so far, but we are only about halfway to where the production environment is going to be (so we figure this will become a problem again). In addition, my customer is trying to use this as an excuse for me to supply him with more RAM (which I don't have a problem with, if that is the real answer to the problem).
I have found information about lock the page on the 64-bit systems, and even though it is not necessary, several people have recommended it or said it should be done anyway.
LD
July 19, 2011 at 11:52 am
I did not want to say it but I would get some memory. It is not that expensive and you spend more money in hours than it would cost to buy the memory. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 11:56 am
Just to clarify this is happening on the test system.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 12:04 pm
This is only happening on the production system, which is why the memory allocation was changed. Typically happens when a user runs a report for a years worth of data on a specific customer (Yes, I know part of the problem could be database design, but I don't get much say in that. I have to support what they give me.)
As for the memory, to upgrade that system's memory we are possibly looking at $1200 to $3000 (since it has to be purchased in lots of 6). Their is a plan to have it replaced in about a year with updated software that is being developed, on updated hardware, but until that happens, I would prefer to not have to spend the money. (If the memory option was cheaper, I would consider that).
I am working on obtaining more information now in hopes this will give me more direction.
LD
July 19, 2011 at 1:08 pm
If you look at look how much the cost you are going to cost the customer for your "man Hours" and the cost of memory and the fact that you did not experience this problem before you reallocated memory than I would seriously consider upgrading the memory.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 1:29 pm
Do you have a max server memory set?
I have seen that issue before myself with a system that no matter how much memory you gave it it wanted more. The resolution for me was to set the max server memory setting.
If I remember from the details the "There is insufficient system memory in resource pool 'internal' to run this query" has to do with SQL Server 2008 has an internal resource pull by default and with not setting a max server memory value and leaving it default it causes a conflict. By controlling the Buffer Cache with the Max Server Memory setting you don't reach the conflict.
Tomas LaRock has a blog posting that gives his recommendations and talks about the subject:
http://thomaslarock.com/2011/03/misremembering-memory-settings/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply