•  

    Just as a follow up to this, we saw the same problem on a brand new, 4 processor HP server. Doing very simple remote select queries on it from another box took 1+ minute to return 1000 rows, over an hour to handle a 78,000 row table.  Sometimes it would bomb out with:

    [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server

    [Microsoft][ODBC SQL Server Driver]TDS buffer length too large

    (repeated 3x)

    Running either query locally returned the data immediately, so we ruled out CPU, memory and disk I/O as a cause. This focused our attention on the network connection.

    Eventually our resident network expert took a look at the pair of network cards and noticed a very large number of transmit errors in the properties tab, over 300,000,000. He tracked it down to a switch which had recently replaced an old hub, and the network card settings on the server hadn't been adjusted accordingly. They were set to half duplex, so he changed them to full duplex and everything went much faster - the 1000 rows query took just 2 seconds to return the full data set rather than over a minute.


    Jon