Why does SmartList not return all of the expected data for large queries?

Here is another support issue that you might come across.

I just closed a case where a SmartList was returning less than the expected number of records. If a SQL Statement was executed via the SQL Server Management Studio, it returned more records than were shown in the SmartList.

The system was comprised of a SQL Server machine and Terminal Server machine. If the SmartList was executed directly on the SQL Server, all the data was returned as expected. If the SmartList was executed on the Terminal Server machine, the returned data was missing records.

Turning on DEXSQL.LOG logging (via the Support Debugging Tool or Dex.ini settings) allowed us to see that a connection error was occurring.  We were also able to locate the Select statement issued by SmartList to gather the data together.

If you look near the top of the DEXSQL.LOG (assuming it was started just before displaying the SmartList), you will find a statement that starts with the following:

SELECT 1 AS '1',

Taking this statement and running it from SQL Server Management Studio on the SQL Server machine confirmed how many records should be displayed.

Below is the error found near the end of the DEXSQL.LOG (assuming it was stopped as soon as SmartList finished processing) showing the connection was closed:

/*  Date: 06/20/2013  Time: 1:43:08
stmt(21859448):*/
{ CALL TWO.dbo.zDP_SOP10106SS_1 ( 3, 'INV00001' ) }
/*
/*  Date: 06/20/2013  Time: 1:43:08
SQLSTATE:(08S01) Native Err:(10054) stmt(21931584):*/
[Microsoft][SQL Server Native Client 10.0]TCP Provider: An existing connection was forcibly closed by the remote host.
*/
/*
/*  Date: 06/20/2013  Time: 1:43:08
SQLSTATE:(S1010) Native Err:(0) stmt(21931584):*/
[Microsoft][ODBC Driver Manager] Function sequence error*/
/*
/*  Date: 06/20/2013  Time: 1:43:08
SQLSTATE:(S1010) Native Err:(0) stmt(21931584):*/
[Microsoft][ODBC Driver Manager] Function sequence error*/

 

With some help from the SQL Server support team, we were able to resolve this issue by disabling the TCP Chimney and SynAttackProtect networking features on the two servers involved.

  

The following article explains the issues with the TCP Chimney primarily for a Windows Server 2003 machine:

"General Network error," "Communication link failure," or "A transport-level error" message when an application connects to SQL Server (KB 942861)

The following registry changes will disable the TCP Chimney feature on a Windows Server 2003 machine:

1. Launch regedit.exe
2. Edit DWORD EnableTCPChimney under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
3. Edit DWORD EnableRSS under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
4. Edit DWORD EnableTCPA under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
5. Changes take effect after a reboot. 

 

The following article explains how to disable the TCP Chimney for a Windows Server 2008 machine.

Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008 (KB 951037)

The following command line command will disable the TCP Chimney feature on a Windows Server 2008 or later machine:

netsh int tcp set global chimney=disabled

 

Troubleshooting: Connection Forcibly Closed (TechNet Article)

The following registry changes will disable SynAttackProtect feature:

1. Launch regedit.exe
2. Add DWORD value named SynAttackProtect under registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\, set value to 0
3. Changes take effect after a reboot

 

After the TCP Chimney and SynAttackProtect features were disabled on both the Server and Client machines, the problem with the connection being forcibly closed went away.

Hope this information is useful.

David