Query limited to 23 or less rows
-
I have an odd problem with one table in a SQL 7 database. It’s running SP4 on Windows 2000 Pro fully patched. Note that the server is in a remote location with a site-to-site VPN in place.
If I attempt to query more than 23 rows from the table, performance goes way down, and only 15 rows are returned. If I select more than 59 rows I get a Connection Broken error.
For example:
SELECT TOP 23 * FROM tbl_Acct
23 rows returned in 1 sec (no problem)
SELECT TOP 24 * FROM tbl_Acct
15 rows returned in 1 minute, 3 seconds.
SELECT TOP 59 * FROM tbl_Acct
15 rows returned in 1 minute, 4 seconds
SELECT TOP 60 * FROM tbl_Acct
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I can turn around and query any number of records off any table on that server so I suspect the error message is misleading. This problem happens both in VB and the Query analyzer. If I register the server in Enterprise manager, all 1200 records are visible. Further, if I use query analyzer directly on the server, all 1200 records are visible.
Consistency Check on the database shows no problems. This is the only table in the database that shows any problems. We have a number of these servers scattered around the country and this is the only table that exhibits this behavior. I’m waiting for change control to completely re-build the table and indexes.
Does anybody have any ideas what is going on?
Thanks in advance,
Ian
-
Is there any change in the execution plan?
-
Ah, good question. And that brings up another clue. When I show the execution plan for any query over 23 records, it returns the connection broken error on the messages tab and no execution plan. Also interesting that it only returned 6 rows, rather than the 15.
It’s almost as if there is a limit on the amount of data, including error messages, that can be returned from the table over the connection. So I started limiting my output to one column, two columns, etc. Now I can return the entire table, except for one column, in less than a second.
I’m starting to suspect that the VPN having some sort of IDS that sees the data in that column, interprets it as ‘bad’, and terminates the connection.
-
Do you have large columns (varchar(8000), text image)?
what does the estimated execution plan shows?
-
The column in question is varchar(127). The remaining columns are all varchar 10, 10, 20, 10. Also 2 int columns and 1 char(1) column.
The execution plan is:
SELECT – Cost: 0%
TOP – Cost: 0%
Table Scan – Cost: 806%
That for a select top 1 * query
-
Check the version of MDAC on both client and server, I used to get a lot of connection problems pre 2.7
Far away is close at hand in the images of elsewhere.
Anon. -
Yes, it’s scary how many problems are magically solved by upgrading MDAC!
The server is currently running 2.7 and clients here are running 2.7 or 2.8.
But that’s is a good point, I’ll add an MDAC refresh to the change control on the server.
-
If I may : Cost: 806%??????????
-
A mystery indeed! Also, the same when running local on the server which is the first sign of trouble on the server. Still doesn’t explain why there are different results.
Also, I found another table that is not normally accessed remotely exhibiting the same problem. This time, it will return about 1500 rows before the error. Table scan showing 877%.
I don’t think we are in Kansas anymore…
-
Can "out of date stats" cause this problem?
-
I just ran sp_updatestats - no change.
-
Well I'm out of ideas... not that I had many for this one
. I'll keep monitoring this one in case anything pops in. -
Remi,
Did you remember our discussion with ram4tech at
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=198251#bm198292
Strange situation when retrieving data (only 6 records)
I understand there was a problem with locking or open transaction and he finally fixed his problem and let us know that : ".........In the Process Info pane, I saw an open transaction (think its the same that I got when I ran DBCC OPENTRAN). I took a chance and killed that process. Then I was able to retrieve data from dbo.TableName....."
Could it be something similar in this case?
Another idea is to do a select with the WHERE to avoid selecting the row #23 if Ian is correct and it is VPN or ODBC driver could not interpret a hidden character in the data itself
Yelena
Regards,Yelena Varsha
-
I had an issue similar with an AS/400 driver, but it was an amount of data that was being returned, xx bytes, before it cutoff. If you can select different rows, and it's always the same number of rows, that's strange. You might try pulling two rows at a time and see if you can find one that has some strange data string that's breaking TDS. I have see data cause issues.
-
I’ve tried both dbcc opentran and sp_who2 tricks listed in that post. There were no open transactions and no blocked by processes. It was a good thought though.
I've also tried using different where clauses. Note that if I exclude the one column, I can easily return all the other data in the table with out issue. So it seems to be isolated to that column.
This table is identical to a number that are around the country so if it is the driver or IDS or something, it has to be specific to that one VPN connection.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply