Craig: Thanks so much for the feedback.. We are in the process of working with Rocket to see if they can address any of this.. Will let all know what feedback we get..
regards, Jay Jones PTNA Allentown, PA Craig Purnell wrote: > > Hello > > Some thoughts and some code for you. > > 1. Are you running SQL Server 64bit on 64bit Windows? With running SQL at > -g2048 you would almost have to be. 2048 is a very high value for the > virtual address space, but I guess you had to do it to keep the > application > running. It's not a good long term solution as it just masks the > underlying > problem. > > 2. You are on the right track trying to analyze the VAS. The code you > posted helps you do this. VAS is the area of SQL Server memory reserved > for > "extra stuff" ie. linked server queries, extended stored procedures, CLR > .net runtime etc. > > 3. One possibility is that the uniVerse driver is 32bit (check this out). > It > may be some strange interaction with running 32bit WOW (windows on > windows) > is causing this. Again, I am assuming you are running 64bit windows. > > 4. I stongly suspect the underlying problem is that the UniVerse driver is > comsuming SQL Server VAS and not releasing it and/or fragmenting it. We > looked at UniOledb driver a few years ago and couldn't get it to work > reliably so we gave up. One solution we were able to get to work is > to bypass the linked server and use SQL 2000 DTS to move the data out of > UV and into SQL. Given the demise of SQL 2000 in our environment, we now > run > the old DTS packages from the command line. It's not pretty but it works. > We > were never able to get SQL 2005 SSIS to work with the uniVerse ODBC > driver. let me know if you want some examples. > > Here's some code that may help you further analyze your VAS and what is > consuming it (I cannot take credit for writing this code): > > --- Find out how dlls map to our VAS > select base_address, name from sys.dm_os_loaded_modules ORDER BY NAME > -- analysis of the memory clerks > select > type, > sum(virtual_memory_reserved_kb) as [VM Reserved], > sum(virtual_memory_committed_kb) as [VM Committed], > sum(awe_allocated_kb) as [AWE Allocated], > sum(shared_memory_reserved_kb) as [SM Reserved], > sum(shared_memory_committed_kb) as [SM Committed], > sum(multi_pages_kb) as [MultiPage Allocator], > sum(single_pages_kb) as [SinlgePage Allocator] > from > sys.dm_os_memory_clerks > group by type > > --raw vas dump > SELECT * FROM sys.dm_os_virtual_address_dump > > -- try and Map loaded modules into the VAS > -- this may help you tell what's consuming the VAS > SELECT lm.base_address, lm.file_version, lm.[description], lm.NAME > [region_base_address], > ([region_size_in_bytes]/1024) AS 'Region_KB' FROM > sys.[dm_os_loaded_modules] lm INNER JOIN sys.[dm_os_virtual_address_dump] > vad ON lm.base_address = vad.region_base_address > ORDER BY region_kb > For more info on these SQL dmv's google them or look em up on books > online. > Hope this helps. Post back as to how it goes. Again, I'm pretty sure it's > the driver that's causing the memory leak. Maybe this will give you ammo > to > take to Rocket software support. > > Regards, > > Craig Purnell > Baker Hostetler LLP > Cleveland , OH > > > > On Mon, Jun 14, 2010 at 7:30 AM, ptnaman <rjo...@penntreaty.com> wrote: > >> >> >> >> +++++++++ >> We have used the –G option in the SQL Server Service startup to increase >> to >> 2048 (2 gig) . the protected memory pool. >> >> The server is a 16 processor Windows 2003 server with 32 gig of memory - >> 22 >> gig is allocated to SQL server. >> >> Increasing the –g option from the default 348 to 2048 allows us to run a >> day’s worth of transactions (most usually) and we restart the sql server >> service nightly as a scheduled job to flush out this memtoleave area. >> >> +++++++++ >> >> We use the following code to interrogate the MemtoLeave region of memory >> - >> 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 >> ) >> >> INSERT INTO tbl_chkMemToLeaveSpace >> SELECT GETDATE(), SUM(CONVERT(BIGINT,Size)*Free)/1024, >> CAST(MAX(Size) AS BIGINT)/1024 >> FROM VAS_Summary >> WHERE Free <> 0 >> ++++++ >> >> This has given us insight into the 2 gig protected memory pool… it seems >> to >> us that there is a memory leak. Only restarting the SQL Server Service >> remediates this collapse. The collapse doesn’t tear down sql server - >> but >> no calls using the linked servers to UV will work without a restart. >> >> --- >> >> >> > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > > -- View this message in context: http://old.nabble.com/SQL-server-to-Universe-via-ODBC-problems-tp28844105p28890130.html Sent from the U2 - Users mailing list archive at Nabble.com. _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
