June 29, 2009 at 9:03 pm
Iam getting the following Error Message in one of our Cluster Servers which is running 3 instances
Total OS Memory : 8023 MB
Processors : 4
Dynamically configuer SQL server memory
Minimum : 2006
Maximum : 3072
All the Other 2 instance were running with same configuration as i mentioned above But iam getting this error message only in Once instace.
2009-06-30 06:51:05.74 spid152 Query Memory Manager: Grants=2 Waiting=0 Maximum=156373 Available=156091
2009-06-30 06:51:59.47 logon Login failed for user 'svcau_crmsprod'.
2009-06-30 06:56:19.96 spid152 WARNING: Failed to reserve contiguous memory of Size= 65536.
2009-06-30 06:56:20.00 spid152 Buffer Distribution: Stolen=4294913916 Free=48806 Procedures=197509
Inram=0 Dirty=57837 Kept=0
I/O=0, Latched=172, Other=626116
2009-06-30 06:56:20.00 spid152 Buffer Counts: Commited=877060 Target=877060 Hashed=684125
InternalReservation=546 ExternalReservation=0 Min Free=128 Visible= 275808
2009-06-30 06:56:20.00 spid152 Procedure Cache: TotalProcs=14071 TotalPages=197509 InUsePages=133582
2009-06-30 06:56:20.00 spid152 Dynamic Memory Manager: Stolen=144129 OS Reserved=69736
OS Committed=69704
OS In Use=66287
Query Plan=201052 Optimizer=0
General=8380
Utilities=350 Connection=473
2009-06-30 06:56:20.00 spid152 Global Memory Objects: Resource=2964 Locks=94
SQLCache=5221 Replication=2
LockBytes=2 ServerGlobal=26
Regards
Nag
June 29, 2009 at 9:09 pm
A guess? You are overcommitting your memory. You probably should allocate more than 2 GB to each instance as that will total 6 GB and leave 2 GB for the OS.
June 29, 2009 at 10:48 pm
This error has nothing to do with RAM .This is a virtual memory pressure .
Had you been on 64 bit server you would not have got this in your entire life.
on 32 bit the buffer pool has 1.66 GB and Mem2Leave has 384 MB by default.Any page greater than 8KB comes from M2L .
Now,in your case this seems to be a Mem2Leave memory pressure as some component is requesting 64KB of contiguous memory which is quite big.
Its very difficult to find who is asking for it.
From DBCC memorystatus
Dynamic Memory Manager:
Stolen=144129 <--1.09 GB
OS Reserved=69736 <--544 MB [total is 1.622 for Stolen + OS Reserved]
OS Committed=69704<-- same is committed i.e.544
OS In Use=66287 <-- in use are 517 MB
Query Plan=201052
Optimizer=0
General=8380 <-- only 65MB is uswd by general memory consumers in the server, including parsing or normalization, locks, transaction context, internal data structures describing the in-memory metadata for tables and indexes, and others.
Utilities=350
Connection=473
If SQL is using 65MB then who is using the remaining from 544 MB (also looks like you are using -g and have increased the Mem2Leave in the past)
Checklist :
------------
1) If there are any other errors like 17803 or 701 before this error
2) If you are using SQL LiteSpeed or any third party DLLs inside SQL Server memory
3) If you are using com objects using SP_OAcreate. If sp_OA stored procedures are being used, ensure that the COM objects are being loaded out of process by passing 4 to the optional third parameter for sp_OACreate (e.g. "EXEC sp_OACreate 'SQLDMO.SQLServer', @obj OUTPUT, 4").
4) If linked servers using third-party OLEDB providers or ODBC drivers are in use, these are also a possible cause of memory leaks.
Can you please send me the zipped copy of :
1) error log that has this error
2) sp_configure output of this instance.
Recommendations:
1) Apply SP4+2187 patch if you are on SQL Server 2000 and SP2 + CU8 or 9 if you are on SQL Server 2005
2) create this SP and run it every 30 mins as a job .collect the output and study it to find out who is using the cache heavily .Drill down further
create procedure Check_mem_status
As
declare @var int
set @var =0
while @var <48
begin
select objtype, sum (pagesused)as pagesused from syscacheobjects group by objtype
go
select * from syscacheobjects
set @var=@var+1
end
exec Check_mem_status
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 29, 2009 at 10:50 pm
my email is hi_abhay78@yahoo.co.in/abhay_c@hotmail.com
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 29, 2009 at 11:09 pm
As I said, a guess. I'd lower the max memory requirements for the three instances to a total of 6 GB (2 GB each) leaving 2 GB RAM for the OS and see if that helps. In addition, it makes sense to be sure that the OS has sufficient memory to manage the system resources.
June 29, 2009 at 11:26 pm
Its good to do that .But my friend, this error is related to Virtual memory pressure and not physical memory pressure.
Even if you add 100 GB of RAM it wont resolve this issue .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 29, 2009 at 11:37 pm
hi_abhay78 (6/29/2009)
Its good to do that .But my friend, this error is related to Virtual memory pressure and not physical memory pressure.Even if you add 100 GB of RAM it wont resolve this issue .
Regards
Okay, here's the deal. I'm not a Windows Server Admin, I am a DBA. Looking at what was posted, I have no idea how you come to your conclusion, nothing there jumps out at me saying virtual memory pressure. Plus, if you are having virtual memory pressure, it has to come from somewhere, such as physical memory being exhausted. Why would you have virtual memory issues if you had sufficient physical memory?
And though not a Windows Server Admin, I have built and supported several Windows Servers a few years ago, as well has being a Computer Operator and System Admin for mainframe and minicomputers, so I do have a quite a bit of experience in this field.
June 29, 2009 at 11:52 pm
Hi Abhay
Thanks for you Reply I have attached Errorlog and Sp_config Details
Yes Iam using -g512 option in startup Parameters
Regards
Nag
June 30, 2009 at 12:55 am
Using 'sqlmap70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_startmail'.
Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_fileexist'
Using 'odsole70.dll' version '2000.80.2039' to execute extended stored procedure 'sp_OACreate'.
Login failed for user 'CSAM\svcau_crmsprod'.
Suggestions :
1) can you stop xp_startmail and xp_fileexist for time being .
2) for sp_OACreate try to run it outside of SQL Server memory by ensuring that the COM objects are being loaded out of process by passing 4 to the optional third parameter for sp_OACreate (e.g. "EXEC sp_OACreate 'SQLDMO.SQLServer', @obj OUTPUT, 4").
3) Apply CU 2187 (call microsoft support and get it for free or check if its available publically on download.microsoft.com).
4) run the SP as job that I gave you and collect the output.Send it to me tommorow.
5) check out why this login fails so often and what it does : 'CSAM\svcau_crmsprod'.
Also , you have actually increased your M2L to 512+128=640 MB (clear from dbcc memorystatus as its using 544 and remainig 96 MB but fragmented).
By default M2L is 384MB(256 worker threads x .5 + 256).When you use -g512 its actually 512 + 128 (used for context switching by the threads)
I did not want to mention but i am giving you these recommendation as i have worked 2.5 years in PSS (Microsoft SQL Server).
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 30, 2009 at 12:59 am
What you've got there is VAS (virtual address space) fragmentation (not necessarily pressure).
There are a number of things that can cause this. Are you using any of the following?
Extended stored procs
sp_OA_... procs
Linked servers (whose drivers are in process)
Normally I'd suggest that you increase the value for -g, but if it's already 512, I'm nervous about increasing it further, especially with 3 instances on the server.
Do all instances have the same memory settings (including the -g512)?
What's happening at the time that error occurs?
Does it occur regularly?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 3:40 am
Hey there,
I've had similar problems over the years - here's what worked for me, just in case it helps you.
Run the following:
CREATE VIEW vasummary AS
select
Size = VaDump.Size,
Reserved = sum(case (convert (INT,VaDump.Base) ^ 0) when 0 then 0 else 1 end),
Free = sum(case (convert (INT,VaDump.Base) ^ 0x0) when 0 then 1 else 0 end)
from
(
--- combine all allocation according with allocation base, don't take into
--- account allocations with zero allocation_base
---
select CONVERT (varbinary,sum(region_size_bytes)) AS Size,
allocation_base AS Base
from sys.dm_os_virtual_address_dump
where allocation_base 0x0
group by allocation_base
UNION
(
--- we shouldn't be grouping allocations with zero allocation base
--- just get them as is
---
select CONVERT (varbinary,region_size_bytes), allocation_base
from sys.dm_os_virtual_address_dump
where allocation_base = 0x0)
)
as VaDump
group by Size
--- Get vasummary information: Number of regions of a given size in SQL Server Size and their status
---
select * from vasummary
--- Retrieve max available block
---
select max(size) from vasummary where Free 0
--- Get sum of all free regions
---
select sum(size*Free) from vasummary where Free 0
This creates a view and runs some sample queries, the most interesting of which are the sum of all free regions and the maximum available block. I usually set up a job to run the queries every minute or so, and log the results to a table. A quick Reporting Services graph of the total free VAS and maximum available block over time can help track down the cause of the problem.
The sys.dm_os_ring_buffers dynamic view can also provide good information (especially the OOM type) but that is limited to a fixed number of records, so I'd start off by restarting SQL Server, and logging the data as above.
In one case I remember well, the cause of the problem turned out to be the 100+ MB of VAS used by loading the CLR (Common-Language Runtime). The odd thing was that we were not using any CLR functionality on that server, and the 'clr enabled' option of sp_configure was turned off!
It turned out that one of the developers had written a report to monitor replication, which was calling sp_browsereplcmds to view replication commands in the distribution database. That system procedure calls a system CLR routine, and the server loads the CLR in order to run it. Once the report was removed, CLR disabled, and the server restarted, the server always had plenty of VAS available - both total, and largest block.
You can check to see if the CLR is loaded (despite the setting of 'clr enabled') by running:
select value from sys.dm_clr_properties where name = N'state'
BTW - a small addition to an earlier post: the extra 512KB of VAS reserved per thread outside the single-page allocator is for the thread's stack space, not just for context switching.
Depending on the number of cores on your server, more worker threads may be running than the default. If set to zero, the default is to provide 256 threads (256 * 512KB = 128MB of VAS space used). This is for a 32-bit server with four or fewer cores - the number of threads increases with the number of cores, and is much higher on 64-bit editions where VAS is not so much of an issue.
A 32-bit server with 32 cores will provide 480 threads - allocating 240MB of extra VAS, above the -g setting. A 64-bit server provides exactly twice as many threads at each core level - see this technet entry for details.
Let us know how you get on. This is a forum so I would encourage you to share your experiences for the benefit of others.
Paul
edit: missed the important word 'just'
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2009 at 3:42 am
Disregard the 2005-specific stuff in that post. I was given a link to follow and assumed it would take me to the 2005 forums - reading later posts made it clear that you are still using 2000* - so sorry about that. Hopefully some of it will still be useful!
Paul
* upgrade!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2009 at 4:05 am
I think we use the stack space for keeping the context of the thread during context switching .Thats the reason i used it :-)..You can always correct me Paul:-).
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 30, 2009 at 5:17 am
hi_abhay78 (6/30/2009)
I think we use the stack space for keeping the context of the thread during context switching .Thats the reason i used it :-)..You can always correct me Paul:-).
Sure. I just wanted to clarify that stack space is used for other purposes too!
You are welcome to correct/clarify any of my posts on this site too* 😀
Paul 😎
* As is just about everyone else
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2009 at 9:02 pm
Hi Gail
Do all instances have the same memory settings (including the -g512)? No Only this instance has -g512 setting
What's happening at the time that error occurs? Log Backup happening every 15mins I could see only 2 times in early morning this Error Message after the log backup
Does it occur regularly? 2 times only everyday almost @ sametime
Regards
Nag
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply