August 28, 2002 at 3:40 am
Hi all,
We had a query which ran fine until yesterday. It joins three tables. Since yesterday afternoon, the query would not run. It would appear to be stuck. I ran DBCC CHECKDB, DBCC CHECKTABLE but no errors were posted. As this table has no index (bad I know) it was not that. I checked for blocking & other process locks but that was not a problem. When I recreated the three tables in the query on test server and populated the tables, it worked fine.
In the end, on the prod server, the table was dropped, re-created and re-populated and now the query is fine again..!!
Any idea's what else I could have checked?
Any suggestions will be appreciated!
Thanks,
Clive Strong
August 28, 2002 at 4:31 am
Not sure, was the query running and completed with no returned records. Would it not start or did it start and just clocked. If other than the clocking you could try profiler to make sure SQL was do something with it. Never relly heard of a query not doing anything. Also, try doing a query plan to see what the query engine thinks of the query when it is failing, use SET SHOWPLAN_TEXT ON. Then compare when you move to test if the same. If this query is not part of an SP it could be it is building a non-optimal query plan after internal fragmentation of the tables occurr or even external fragmentation may have an effect on heap tables, not sure.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 28, 2002 at 10:12 am
Agree with Antares. Query should have done something, but what happened.
Are you sure the query was correct? There was data to return. Perhaps you check the "check query" button and not the "execute" button?
Steve Jones
August 28, 2002 at 10:17 am
Hi,
I checked the query to ensure it was correct and ran the same query against a restored database on another server.
When I ran it from QA, it was like the tables were blocked. However when I checked EM, the only locks on the table were generated by the process running the query. I left the query running for 15 minutes and nothing.
When I dropped the table, re-created it and re-populated it all and ran the same query from the same process, my result set was returned in 3 seconds!
Never seen anything like that before!
Clive Strong
August 28, 2002 at 1:26 pm
When I have really hot tables that also need fairly frequent queries, I sometimes specify the (nolock) hint in my read-only queries. I never have locking problems from those :). Be aware that if you go that route that your resultset won't contain the changes happening while the select is running; i.e. your results are the latest *completed* results through when you ran the query. (Normal SELECTs will queue up behind UPDATE locks and always return the latest changes, including those made concurrently with the SELECT.)
- Troy King
- Troy King
September 23, 2002 at 7:04 pm
Delete all the statistics of those 3 tables.
September 24, 2002 at 1:26 am
Clive, you said there where no indexes on the table. I guess that includes clustered index, i.e. there was not a clustered index on the table. If so, did the table contain columns allowing null? Or columns with datatype (n)varchar? How many rows where there in the table, and how was it used? I'm interested as I think this is an interesting problem that maybe should not be left at solving it by dropping and recreating the table.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
September 24, 2002 at 1:40 am
Almost forgot about this one.
Every column on the table will allow NULLS and there are 8 columns that are varchar type.
It still stumps me this one. We have not had an issue since and as much as it looked like it was a blocking incident, I could see no locks. There was around 13000 rows in the table.
However, the table has been modified since. We have a Primary key and a few more indexes on this table now.
Clive Strong
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply