November 4, 2009 at 7:44 pm
Hello,
We have a SQL 2005 64-bit (SP2) SQL Server where the Minimum server memory (in MB) is set to 10240 MB.
But in Task Manager, when I just checked, the sqlservr.exe process was using just over 8 GB (8,000,000 KB). Now it's up to about 9,200,000 KB. But that is still below 10240 MB.
I thought the minimum setting was a floor below which the memory usage could not drop. Am I wrong?
The only thing I can think of is that somehow the sql process was restarted. But I don't see a new log started. Although I do see what looks like a set of errors (I've reversed the order of the log records, but I hope the listing is familiar to someone):
1. 11/4/2009 9:21:46 PM ***Unable to get thread context - no pss
2. 11/4/2009 9:21:46 PM * BEGIN STACK DUMP:
3. 11/4/2009 9:21:46 PM * Non-yielding Scheduler
and so on.
Does anyone know what might cause this issue? At this link there is mention of possible disk I/O issues, which has me concerned.
Thanks in advance for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 4, 2009 at 10:05 pm
webrunner (11/4/2009)
Hello,We have a SQL 2005 64-bit (SP2) SQL Server where the Minimum server memory (in MB) is set to 10240 MB.
But in Task Manager, when I just checked, the sqlservr.exe process was using just over 8 GB (8,000,000 KB). Now it's up to about 9,200,000 KB. But that is still below 10240 MB.
I thought the minimum setting was a floor below which the memory usage could not drop. Am I wrong?
My answer is yes.
The buffer pool does not immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only the memory required to initialize. As the Database Engine workload increases, it keeps acquiring the memory required to support the workload. The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory
Please check the URL : http://msdn.microsoft.com/en-us/library/ms180797(SQL.90).aspx
November 5, 2009 at 7:30 am
jagadeesanpv (11/4/2009)
webrunner (11/4/2009)
Hello,We have a SQL 2005 64-bit (SP2) SQL Server where the Minimum server memory (in MB) is set to 10240 MB.
But in Task Manager, when I just checked, the sqlservr.exe process was using just over 8 GB (8,000,000 KB). Now it's up to about 9,200,000 KB. But that is still below 10240 MB.
I thought the minimum setting was a floor below which the memory usage could not drop. Am I wrong?
My answer is yes.
The buffer pool does not immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only the memory required to initialize. As the Database Engine workload increases, it keeps acquiring the memory required to support the workload. The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory
Please check the URL : http://msdn.microsoft.com/en-us/library/ms180797(SQL.90).aspx
Thanks - I read through that URL, which led me to this one:
http://msdn.microsoft.com/en-us/library/ms178067(SQL.90).aspx
That page states, "However, after memory usage has reached this value due to client load, SQL Server cannot free memory from the allocated buffer pool unless the value of min server memory is reduced."
Right below that is this note:
"SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory."
What I still don't understand is how the memory usage could have dropped below the minimum specified if the SQL Server had not been restarted.*** (see note below) Also, given what I thought was the load on the SQL Server, it seems amazing to me that the load would not have caused the memory usage to exceed the minimum within a fairly short amount of time (a few days at most).
But I think I should confirm the load to make sure. Can anyone suggest a way to do so? Should I just use Performance Monitor to track memory while also using SQL Profiler to check queries? I've read at sql-server-performance.com that one can make that correlation.
Thanks again for any further advice,
webrunner
***(note)
It seems that SQL Server had not been restarted. I checked this query
select name,create_date
from sys.databases
where database_id = '2'
which I found at this URL
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79783
and got this result:
namecreate_date
tempdb2009-10-26 17:03:29.857
So it seems to me that, if the load explanation is correct, that the SQL Server had not had yet reached the minimum memory usage of 10 GB within 10 days after a restart. Again, it seems unlikely to me, but I suppose it's possible.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 5, 2009 at 8:02 am
run your profiler, under buffer manager use total and target pages......you can watch it here set for a about every 5 min and save to csv file...sometime depending on your load it very well take days to reach the target which what sql would like to have and total is what is currently has....also if you have not done so already set you max setting so that sql does not take all the memory from the OS
November 5, 2009 at 8:05 am
also one other note it very possible that the memory dropped, if you do not have a max memory setting set sql will and can page memory out.. as SQL steal all the memory at some point the os will become starved and notify sql that it needs it....then you get a memory dump.
November 5, 2009 at 8:31 am
465789psw (11/5/2009)
also one other note it very possible that the memory dropped, if you do not have a max memory setting set sql will and can page memory out.. as SQL steal all the memory at some point the os will become starved and notify sql that it needs it....then you get a memory dump.
Thanks for the information. We do have a max memory setting. Here are the relevant settings:
Physical Ram: 16 GB (per node - it's an active/passive cluster)
SQL Min Server Memory (MB): 10240
SQL Max Server Memory (MB): 12288
Minimum memory per query (in KB): 1024
I did see entries in the SQL log about memory being paged out, though, and I did see the entry about the dump. For example, here's an entry from 10/31/2009 (about 5 days after the last restart):
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1847 seconds. Working set (KB): 5032404, committed (KB): 10802896, memory utilization: 46%.
Can this happen even with a max memory amount set? Are there any other settings I've missed?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 5, 2009 at 8:43 am
Sorry I didnt word it right, but your note is a memory dump, the os ran out of memory. on our cluster environment is caused our primary server to restart each time
Are you on standard or enterprise edition. Since you have max server set and it still occurring you may need to back both of them down min and max . or add my memory. just looking;the setting look good.
you can leave it and then lock pages in memory, this is new to standard edition...since sp2.
here is few good links
http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
--this one as well is good
November 5, 2009 at 8:53 am
465789psw (11/5/2009)
Sorry I didnt word it right, but your note is a memory dump, the os ran out of memory. on our cluster environment is caused our primary server to restart each timeAre you on standard or enterprise edition. Since you have max server set and it still occurring you may need to back both of them down min and max . or add my memory. just looking;the setting look good.
you can leave it and then lock pages in memory, this is new to standard edition...since sp2.
here is few good links
http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
--this one as well is good
Thanks for the additional info and links. I will read them.
We are running SQL 2005 Standard Edition. Does that affect whether the max/min memory settings are ignored or enforced?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 5, 2009 at 9:00 am
no the will not be ignored...but in prior version of standard lock pages was not an option...
my best guess is the OS or another application if you are running any are out of memory....
run you profiler and save it, getting data every 5 min or so...and leave it on...you will be able to watch when it pages out of memory....the total will meet the target then likely stay there for a bit then drop significantly.
just remember to restart SQL after you have made any changes
November 5, 2009 at 9:18 am
465789psw (11/5/2009)
no the will not be ignored...but in prior version of standard lock pages was not an option...my best guess is the OS or another application if you are running any are out of memory....
run you profiler and save it, getting data every 5 min or so...and leave it on...you will be able to watch when it pages out of memory....the total will meet the target then likely stay there for a bit then drop significantly.
just remember to restart SQL after you have made any changes
OK, I'll do that and see what turns up. Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 5, 2009 at 9:32 pm
To correct some inaccuracies in previous posts on this thread:
Support for locked pages on 64-bit Standard Edition was added in 2005 SP3 CU4, and 2008 SP1 CU2. You need to assign the 'lock pages in memory' right to the SQL Server service account, and specify trace flag 845 in the start-up options.
The relevant MS KBs are:
http://support.microsoft.com/kb/970070/ and
http://support.microsoft.com/kb/918483
You should investigate what concurrent server processes caused Windows to page out the SQL Server buffer pool memory in the first place. One possibility would be another instance failing over from another node.
Paul
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply