on 2013 Sep 30 4:56 PM
We are in the process of upgrading from Sybase v9 to v11, and I am having issues with the v11 odbc driver for a couple connections. I am seeing performance taper after approx. 500MB of data is transferred.
I am performing all tests on the same windows 2008r2 server, using SQL Server Integration Services 2012 to connect and pull data. I have two 32bit (but am seeing same results with 64bit v11) system odbc connections, one for the v9 drivers and one for the v11 drivers. Both are configured for the same Sybase v11 database, with the same settings. I am pulling two different data sets in parallel that total roughly 1GB.
I am seeing a steady average of roughly 3MB/s with the v9 drivers. 1GB of data is taking approx. 350sec to transfer.
The v11 drivers start to taper around 500MB, going from 3MB/s to < 1MB/s. It ends up taking roughly 4000 seconds to finish transferring the 1GB of data.
Does anyone have any suggestions or information? I didn't find anything that seems to relate to this in the connection parameters or network protocol options of the documentation. Additionally, it doesn't seem there is a meaningful difference in the connection logs below.
Thanks!
ODBC Log for v9:
Mon Sep 30 2013 14:55:50 14:55:50 Application information: 14:55:50 "HOST=*;OS=Windows NT 6.1 Build 7601 Service Pack 1;PID=0x1d70;THREAD=0x7e0;EXE=C:\\Program Files (x86)\\Microsoft SQL Server\\110\\DTS\\Binn\\ISServerExec.exe;VERSION=9.0.2.3381;API=ODBC;TIMEZONEADJUSTMENT=-240" 14:55:50 Attempting to connect using: UID=*;PWD=********;ENG=*;START=*;CON=SQL_DBC_135108e8;ASTOP=NO;INT=NO;DBG=YES;LOG=C:\\Sybase Drivers\\ODBC_ASA9.log;DMRF=NO;LINKS=SharedMemory,TCPIP{ip=10.90.6.43};COMP=NO 14:55:50 Attempting to connect to a running server... 14:55:50 Trying to start TCPIP link ... 14:55:50 Loading wsock32.dll 14:55:50 Loading ws2_32.dll 14:55:50 TCP using Winsock version 2.0 14:55:50 My IP address is 10.90.5.53 14:55:50 My IP address is 127.0.0.1 14:55:50 TCPIP link started successfully 14:55:50 Attempting TCPIP connection (no asasrv.ini cached address) 14:55:50 Looking for server with name * 14:55:50 Trying to find server at address 10.90.6.43 14:55:50 Found server, verifying server name 14:55:50 Found database server at address 10.90.6.43:2638 14:55:50 Found database server * on TCPIP link 14:55:50 Connected using client address 10.90.5.53:50246 14:55:50 Connected to server over TCPIP at address 10.90.6.43:2638 14:55:50 Writing server address 10.90.6.43:2638 to asasrv.ini cache 14:55:50 Connected to ASA Engine version 11.0.1.2584 14:55:50 Connected to the server, attempting to connect to a running database... 14:55:50 [ 307] Connected to database successfully **ODBC Log for v11:** Mon Sep 30 2013 14:54:41 14:54:41 Attempting to connect using: UID=*;PWD=********;DBN=*;ENG=*;CON=SQL_DBC_114a7948;ASTOP=NO;INT=NO;CBSIZE=1460;DBG=YES;LOG='C:\\Sybase Drivers\\ODBC_ASA11.log';DMRF=NO;LINKS='SharedMemory,TCPIP{ip=10.90.6.43}';COMP=NO 14:54:41 Attempting to connect to a running server... 14:54:41 Trying to start TCPIP link ... 14:54:41 TCP using Winsock version 2.2 14:54:41 My IP address is 10.90.5.53 14:54:41 My IP address is ::1 14:54:41 My IP address is 127.0.0.1 14:54:41 TCPIP link started successfully 14:54:41 Attempting TCPIP connection (address 10.90.6.43:2638 found in sasrv.ini cache) 14:54:41 Trying to find server at cached address 10.90.6.43:2638 without broadcasting 14:54:41 Found server, verifying server name 14:54:41 Found database server at address 10.90.6.43 14:54:41 Found database server * on TCPIP link 14:54:41 Connected using client address 10.90.5.53:50238 14:54:41 Connected to server over TCPIP at address 10.90.6.43:2638 14:54:41 Connected to SQL Anywhere Server version 11.0.1.2584 14:54:41 Application information: 14:54:41 IP=10.90.5.53;HOST=*;OSUSER=*;OS='Windows 2008R2 Build 7601 Service Pack 1';EXE='C:\\Program Files (x86)\\Microsoft SQL Server\\110\\DTS\\Binn\\ISServerExec.exe';PID=0x1f2c;THREAD=0x1f7c;VERSION=11.0.1.2584;API=ODBC;TIMEZONEADJUSTMENT=-240 14:54:41 Connected to the server, attempting to connect to a running database... 14:54:41 [ 304] Connected to database successfully
I have a similar issue that I am dealing with. I suspect that the newer Sybase ODBC drivers have some type of negative interaction with windows tcp auto tuning. If you disable windows tcp auto tuning, you get some improvement. There is a known defect in Windows Server 2008 R2 (and on Win 7 machines) related to windows tcp auto tuning.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try removing the CBSIZE=1460 since the default is 7300 for everything except Windows Mobile.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry, I should have mentioned that I got the same performance issues with 7300 CBSIZE. It was at that initially, and I changed to 1460 since that was the only difference I saw in settings between the drivers. I am going to change both to the maximum setting since it's server to server, but I don't think this will eliminate the slowdown. I will verify. Thanks for the suggestion.
Edit: I am seeing the same results with buffer sizes of 1460, 7300, and 16000.
User | Count |
---|---|
70 | |
10 | |
9 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.