November 20, 2006 at 2:49 pm
I am having some problems with a table and can't quite figure out how to fix this.
If I try
select * from table
the server will just hang there, now if I use TOP then I can go all the way to to TOP 32752 but as soon as I use 32753 it will hang again.
I've tried to do each of the individual columns, and it still just hangs. Because of this I haven't been able backup the table, export it or anything. Any ideas what might be happening here?
November 20, 2006 at 3:09 pm
Sounds like a memory problem (not enough). How are you doing the query?
1. On the server itself or from a remote machine?
2. From Enterprise Manager, Query Analyzer or another application?
If you are doing this in Enterprise Manager, then I can understand it. Enterprise Manager isn't really designed to return a lot of data. Try your query in Query Analyzer.
-SQLBill
November 20, 2006 at 3:11 pm
.... ok, so something's changed on the server. After not working for over an hour, the table is suddely responsive once again at over 58k records. I was under the impression that you could have millions of rows per table, but keep seeing something about 32,767 listed in various places and stopping the query always listed like 32,744
anyways, seems like everything is working, but now I just need to figure out why it bombed like that.
------- Looks like we were posting at the same time ------
I was running this with QA on the machine with a remote desktop session. There is over 8gb of ram on the machine, but I was using a remote desktop session if that might have any impact
November 20, 2006 at 3:22 pm
You're right, a table can have millions of rows. I've never had problems running QA using Microsoft's Remote Desktop Connection (formerly terminal services). If this starts happening again, I would suggest running sp_who2 and sp_lock to look at the current blocking and locking activity.
November 20, 2006 at 3:33 pm
What version/edition do you have?
Run this and post the results...
SELECT ServerProperty('ProductLevel'),
ServerProperty('ProductVersion')
-SQLBill
November 21, 2006 at 7:21 am
I got back SP4, 8.00.2039
and further testing of the system seemed to indicate when we had the application for this database open and were viewing records from the table, it would stop the queries, but once the view was closed the queries would run fine.
I didn't think the app would try and lock the table, but perhaps that is the cause.
November 21, 2006 at 7:37 am
It's strange that the table would list the number of records bound by the size of a small int. Is it possible that your unique ID field, (assuming you're using one), is set to the size of a small int?
November 21, 2006 at 11:57 am
It sounds to me like locking is the most likely cause. Some query or process had row # 32753 locked in a transaction. If you select just the first 32752 rows, it can return the data because none fo the rows are locked. If even one row that it needs to return is locked, SQL Serve will wait for it to unlock.
The solution: Unless you are concerned with reading dirty data (a record involved in an active transaction), use the nolock optimizer hint in your select query. The nolock hint does 2 things. It tells SQL Server to read the data even if it is locked and it does not issue a shared lock of it's own. A shared lock will block an exclusive lock request, so using nolock is better for the system all around.
The use of nolock is a requirement here except in certain situations, such as reading financial data or certain situations where I want to ensure that dirty reads do not occur.
Try this: select * from table with(nolock)
November 21, 2006 at 12:23 pm
Thanks for the help.
Robert, that description makes perfect sense.
We got done with the necessary updates and this was a one time thing, but I will remember the nolock tip!
Bib
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply