Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tripodal
Constraint Violating Yak Guru
259 Posts |
 Posted - 2009-03-26 : 12:53:16
|
| So, We have approximately 350 databases, and have recently been creating many nwe SP's and added a few new DLL's.Started to recieve the memory error a few days later. Found article below, which lead me to add a startup parameter of -g340 to sql2000.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39704and Microsoft Article Q316749, QYay, its fixed. How do I monitor this condition? Any advice on a sql sp or a performance counter that indicates this memory shortage?Will SQL2008 have a similar issue? |
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-03-27 : 13:51:01
|
| Ok so now my head hurts.http://msdn.microsoft.com/en-us/library/aa175282(SQL.80).aspxI have managed to distil some of the bits, you can track the bufferpool usage with the perfmon SQL Server:Buffer Manager\Target Pages counter and the Total Pages counter, the total pages counter is dynamically adjusted as free memory in the server increases or decreases. Unforutnaly this is Bpool memory, I need to track the Bpool memory region of sql2000. What I need to see more directly is the MemtoLeave region.I have considered using the Virtual Byte Perfmon - (Total pages *8192) / (my startup parameter + the default reserved value of 384mb) Which should give me the approximate percentage of used MemToLeave (memory to leave) regions.This seems backasswards. Insight or debate welcome :) |
 |
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-03-27 : 14:38:53
|
| For the curious look what I have found.From this page.about 1/4 the way down.http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/cc1b3e43-0db8-4e75-b5ab-bc2a4c93b12b/*edit*Works great in sql2005. Not 2k unfortunatly. Back to digging.Run the following query, and paste the output back here: Code Snippet;WITH VAS_Summary AS (SELECT Size = VAS_Dump.Size, Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM ( SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size], region_allocation_base_address [Base]FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION SELECT CONVERT(VARBINARY, region_size_in_bytes) [Size], region_allocation_base_address [Base]FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ) AS VAS_Dump GROUP BY Size) SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB],CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] FROM VAS_Summary WHERE Free <> 0Apparantly to display the amount of memory in the memtoleave area. Fist being allocated and second number being available. Im a bit leary to run this code, as I know next to nothing in terms of acutally queries. |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-03-30 : 10:25:18
|
| There is a utility which displays the largest contigous region of free address space. It is called vmstat, it included with one of the sql workshop downloads that microsoft handles. Unfortuantely It looks as if it does not give the actual total usage.http://www.microsoft.com/downloads/details.aspx?FamilyId=AEC18337-887F-4EC6-A858-81F84DE8082F&displaylang=enIt is in lab 3 for those interested.It appears to me now, after all of this, this is actually an OS issue more than a sql issue.It also appears to me that I never wanted to know this much about how sql server handles memory. But perhaps this is another counter totally external from sqlserver, Im off to that rabbithole.o7 |
|
|
|
|
|
|
|
|
Sql2000 - Failed to reserve contiguous Memory.