Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lfmn
Posting Yak Master
141 Posts |
 Posted - 2002-08-21 : 13:05:03
|
| I'm having a problem I've never seen (or heard of before) and I'm hoping someone can help. I'm having a problem running the following query:SELECT *FROM Aspect.dbo.tblTable (nolock)WHERE location = 'ANDOVER' AND processdate = '8/18/2002' ORDER BY location, ext_numIt hangs and takes approximately 6 minutes to stop. The results set is two rows.Here is the strange part - if you run it with the where clause or the and clause, there is no problem, but when you add the order by part, that is when it hangs.If anyone has any ideas .... HELP!!P.S. I checked and the location and ext_num column are in the results setSQL is useful if you don't know cursors :-) |
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-08-21 : 13:16:08
|
| I think a little more info may be helpful.How many rows in the table?Do indexes exist on any of the WHERE or ORDER BY columns?How many concurrent users?What size processor?How much memory?This should be plenty for openers.solart |
 |
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2002-08-21 : 14:00:36
|
| How many rows in the table? 13,000,000Do indexes exist on any of the WHERE or ORDER BY columns? index on locationHow many concurrent users? I took the server off the network and was the only user and it still hungWhat size processor? 4 550 processorsHow much memory? 2GBAlso, it's SQL server 7 sp2 running on NT 4, sp6.something else which might be helpful - I am experiencing rollbacks in many of my databases.This problem started last night. Everyone claims that they did not move any new code into production ... sure.Also, out of 1.7 TB, we have approx 187 GB freeSQL is useful if you don't know cursors :-) |
|
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2002-08-21 : 14:55:22
|
| Installed SP3 and the problem is solved....Whatever the problem was :-)SQL is useful if you don't know cursors :-) |
|
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-08-21 : 15:05:18
|
Glad your problem is resolved. This reinforces the idea that keeping software up to-date is important (assuming there are no overriding reasons not to). Don't know if this problem is described and/or dealt with on Microsoft's KB.Happy Querying  |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-08-22 : 05:54:38
|
| 1 (aside) comment.....given that you can only have 1 location value....as specified by the existance of the "WHERE location = 'ANDOVER'" clause...including "location" in the "ORDER BY location, ext_num" adds no value....you might as well have "ORDER BY ext_num"this may also be part of the problem, in that you have asked SQL Server to do unnecessary work....re working without the WHERE and ORDER by components....have a look at the performance plans for them....they'll be different from the plan which include either or both of WHERE or ORDER BY....because with the latter SQL will be creating and filling (on the fly) temporary tables that will hold the results of these operations....remember...if you want to sort 13,000,000 rows (or even a subset of same).....they'll end up being stored somewhere during the sort...and storage takes time. |
|
|
|
|
|
|
|
|
query hangs