Query limited to 23 or less rows
-
Does this table has a CLUSTERED index or is it missing one?
* Noel -
Ian,
Did you try OLEDB driver instead of ODBC in VB? Go to
http://www.connectionstrings.com/
expand SQL Server and then OLEDB nodes. There will be a string that lets you to specify a library. Try both. Let us know the result.
ODBC connectios sometimes are limited to a number of characters it can return. What is the data type of the field in question?
For Query Analyzer did you try a client Network Utility to change the library?
Here is an example from this site:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Regards,Yelena Varsha
-
I would like to add my 2 cents as well.
We have got a problem something similar to that. It came out that the problem was HW (disk) related. Fortunatly we could solve the problem with chkdsk.
- Did you checked the consistency of your disks?
- Did you tried to drop and rercreate all of your indexes (includeing a clusterd one)?
- did you tried to copy all rows into a new table, then drop the original one and rename the new one to the old name?
- did you tried to bcp out all rows?
Bye
Gabor -
sounds like vpn overhead and possible packet size / loss issues ... assuming you are using ADO not odbc ... which yelena stated correctly is completely a hog
I wouild look at other data flow (http telnet ftp etc..) across the network or if you can set it up local...
Bad router or table can be a thorn... but a dodgy concept to resolve. Also the vpn connection could be using the local driver not the remote one.
...2 canadian cents...
-
Thanks to all for the comments and suggestions.
My vb app is using oledb, not odbc. I've found it to be the most reliable in any situation. Not sure what provider the query analyzer uses...
We are going to drop and re-create the table and indexes tomorrow morning. If that doesn't work, we are going to start looking at the VPN and OS. At this point, I'm not sure what else to do.
Thanks again.
-
Like I said: I have seen very strange behaviours on large tables Lacking Clustered indexes!! or having Clustered indexes with large fragmentation
Good Luck!
* Noel -
Adding a clustered index immediately resolved the problem.
It’s interesting that the problem only surfaced on this one of so many identical servers. It’s nice to have a documented case of why you always have a clustered index!
Cheers!
Ian
-
I still belive that the problem was HW (or logical volume) related.
By adding a clustered index you have moved all data pages to a new location.
I think that you could have achived the same result by dropping/recreating the table without clustered index.
I have a lot of tables without any clustered index by design and I have never experienced this behaviour because if the lack of clustered index. As I've told you we've got once a similar problem, where a chkdsk could solve the issue.
Anyhow I also belive that having a clustered index on a table is a way to go, otherwise you won't be able to reorganise it (if the table is big enough, over 8 pages = 64K). The only thing is that you have to chosse carefully the candidate for a clustered index
Bye
Gabor -
>>I still belive that the problem was HW (or logical volume) related.<<
Gabor, Believe me, I have seen this on brand spanking new machines/Drives!!
The reason beign associated to Heap structures. I am pretty sure that I had a link to this lets see if I can find it ( I have too many in my bag
)
* Noel -
For what it's worth, I started by making a copy of the table in question. It exhibited the same problem. I then added the clustered index to the new table and the problem was resolved.
So, just for kicks and grins, I just dropped the clustered index. As expected, I can still access all the data.
Perhaps the key clue was that the execution plan was showing 800% on the table scan; even if executed from the server. Regardless of where the problem was, what I find most interesting is that dbcc was unable to detect any problems.
Ian
-
That is cool!
It may be related to data as well. Reorganizing a physical order of the rows may push a bad row closer to the end of the table (in case this index is not used) or the execution plan is using the index and avoids this record at all.
When the index is dropped the data are already re-written, so the problem does not manifest itself again.
Yelena
Regards,Yelena Varsha
-
Very impressive. Should note that into the library of solutions. Still does not explain why there is not a event or tool to visualise these types of issues, is this product by design made to be a rubics cube for some of the most rudimentary aspects.
-
Ok,
I found one that was related to the inserts but it has been also experienced (read suffered) on selects too:
PRB: Poor Performance on a Heap
I still have to keep looking but I am sure that I have it somewhere
Cheers!
* Noel -
The clue here may be more to do with the firewall config and the MTU settings. Try settings the MTU to 1404 to see if this helps. If you can get someone to query the database directly on the server can they return the correct result without the error message. If so I would think the firewall may well responsible for fragmenting the packets.
-
OK, so I just read the other pages of the post, and its not the VPN....could have been though
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply