[AD]This is clearly an ad, but I think it's also clearly on topic, given some of the concerns mentioned below:
I'm wishing you the best of luck solving your problems with the current tools, since you've probably invested a lot in that infrastructure, but if you can't find a way to solve you issues, please note that our ODBC, OLE DB and ADO.NET providers all work nicely with SQL Server, including on 64-bit Windows 7 or 2008. The ADO.NET is the best, as it is 100% .NET and compiles to IL which the JIT compiler will turn into 64-bit code, as needed. It binds natively to Visual Studio components, including Visual Studio 10 targeted to .NET 4.0. Our products are not free, and not bundled with the O/S, but when you need something that really works, our products work. If something doesn't work, we'll fix it. We stand behind our products, and turn around fixes and upgrades to handle new O/S quickly. My dev machine is presently a Windows 7 64-bit. All our products work nicely on it. Check us out at http://www.fwic.net The company is FusionWare Integration Corp., and we now market and sell the products formerly owned by FusionWare Corporation, GA eXpress, General Automation, and in the beginning, Liberty ODBC. We are the original Liberty ODBC developers. We have customers who have been using our products successfully since the early 1990s. Some of our people helped write some of the first ODBC drivers for Microsoft, so we've been at it for a long time. We have done extensive work assisting our customers in integration applications built on MultiValue (all flavors), SQL Server (since 4.2b Sybase/Microsoft versions), DB2 including DB2/400, Oracle, and many other databases. In addition to our ADO.NET running nicely in 64-bit mode SSIS, we have a product called mv2SQL which can run standalone or within DTS to transfer data into SQL Server, and a Legacy2SQL bridge product that you might find interesting. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Craig Purnell Sent: Monday, June 14, 2010 5:31 PM To: U2 Users List Subject: Re: [U2] SQL server to Universe via ODBC problems 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 <[email protected]> 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 [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
