| Author |
Topic |
|
poncho
Yak Posting Veteran
62 Posts |
 Posted - 2008-06-04 : 15:43:05
|
| and TCP as a connection method.When using Named Pipes the issue is no longer there.On massive batch inserts we sometimes get a long pause at the end of one insert and before begining the next one. Example:1000 inserts in the same table and then repeat. This will work fine for 3 or 4 iterations, then pause during the 5th iteration for up to 40 seconds and then simply continue.When this exact same procedure is done using Named Pipes as the connection method this never happens.While this is happening neither the server or the workstation is doing anything, 0% CPU, 0% network, it just sits there.All this using the SQL Native Client 2005 and ADO.Anyone have any ideas? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-04 : 16:51:54
|
| Here is why from Books online:http://msdn.microsoft.com/en-us/library/aa178138(SQL.80).aspx |
 |
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2008-06-05 : 09:55:46
|
| I have read that before, but it doesn't explain why TCP has a pause during a batch insert process and named pipes doesn't. By the logic of that article I should be experiencing better performance with the TCP transport. In actuality the transaction is a little faster with TCP vs. Named Pipes up until TCP pauses for up to 40-50 seconds. Using Named Pipes as the transport method this transaction typically takes 7.2-7.4 seconds vs. 6.8-7.2 seconds with TCP, so yes TCP is slightly faster, but not enough to make a difference on a fast LAN, however my issue is seperate from this. TCP pauses or hangs or waits for something during the batch inserts and then continues 40-50 seconds later.Thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-05 : 11:32:45
|
| Never had this issue. |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2008-06-05 : 13:31:24
|
| What's odd is that we can reproduce this at 2 locations with completely different servers, clients and network. I say 2 because we are rolling out tome testing on the MSSQL version of our apps, so we don't have more then 2 locations currently testing. Our apps where originally written for Pervasive SQL. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-05 : 14:12:43
|
| I'd open a case with Microsoft on this. Do you have a support agreement with them already? If not, I believe cases cost about 250 bucks. We've purchased a bucket of cases ahead of time, so I'm not sure what it costs per case but I've heard 250 bucks.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2008-06-10 : 09:11:32
|
| We've done some more testing. The issue seems to be when we are using both server and client cursors. If we have one connection to the DB with a server side cursor in read only, and then open client side cursors to do an insert into another table, well after many inserts (thousands) we eventually get this error:TCP provider: Only one usageof each socket address(protocol/network address/port) is normally permitted (-2147467259)We don't ge this using Named Pipes.I have written a small VB6 project that recreates this issue. Is anyone interested in looking at it and see what they can come up with?In the meantime I will be opening a case with MS today and submit this to them. |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2008-06-10 : 13:49:30
|
| Further tests have netted me a solution. By adding TcpTimedWaitDelay registry entry on the client (Windows XP) the issue no longer seems to be there. I ran my code for over 1 hour with not one error, where normally around 1 minute the issue would appear. Here is where I got the registry settings from:http://msdn.microsoft.com/en-us/library/ms819739.aspxEven though they mention on a Win 2003 server, it only made a difference when I made the change on the client side. There was no difference when the change was done on the Server. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-10 : 19:23:35
|
| Thanks for the information. |
|
|
|
|
Performace issue with SQL Native Client