2

I have a SQL query that is quite simply select * from tblOrders where customerID = 5000but it never returns. I waited 10 minutes and gave up.

The weirdest thing is that other queries on the same DB, but on another table, works fine. Removing the where-clause doesn't help either, so it seems like the table is somehow not responding. It's about 30000 lines, so it's not the biggest table either.

I'm using MS SQL SMS 2008 Express against a SQL Server 2008 express running on a remote server.

Christian Wattengård
  • 5,543
  • 5
  • 30
  • 43
  • Is the customerID set as a primary key or indexed? If not then it may be going through every record in the DB regardless if it finds it or not. – Matt Asbury Jan 07 '11 at 10:26
  • 3
    Do you have any open transactions that could be locking the table? [DBCC OPENTRAN](http://msdn.microsoft.com/en-us/library/ms182792.aspx) – codingbadger Jan 07 '11 at 10:29
  • Is the table indexed on customerID? And are you positive that it is in fact a table, and not a view? Also, what happens if you "select customerID from tblOrders where customerID = 5000". And finally, is customerID numeric? – Ronnis Jan 07 '11 at 10:29
  • I restarted the server and it started to respond slightly better. It now responds in good time as long as i limit the lines. I discovered that it has no indexes at all, so i added indexes on OrderID and CustomerID. All ID fields are numeric. Still, a "top 5000 order by OrderID" select takes 19 seconds on this table, compared to 2 seconds on another table (top 5000 order by tableprimaryid) – Christian Wattengård Jan 07 '11 at 10:35
  • Doing a full "select * from table tblorders by orderid" took 2 minutes, but it at least got through :) – Christian Wattengård Jan 07 '11 at 10:38
  • Does `SELECT CustomerID FROM tblOrders WHERE CustomerID = 5000` return?? Or `SELECT CustomerID FROM tblOrders WHERE OrderID = (value)` ?? – marc_s Jan 07 '11 at 11:18

3 Answers3

5

Try this to by-pass any locks on table -

select * from tblOrders(nolock) where customerID = 5000
Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
3

It sounds like your table is locked

run this query to see what locks are held against it.

USE master;
GO
EXEC sp_lock;
GO

but table locking is a whole mindfield of its own here is some info in the sp_lock system stored proc http://msdn.microsoft.com/en-us/library/ms187749.aspx

when you find the lock you can kill it

KILL { session ID | UOW } [ WITH STATUSONLY ]

http://msdn.microsoft.com/en-us/library/ms173730.aspx

Gaven
  • 371
  • 1
  • 6
0

I agree with the others that this is most probably a locking issue. By default write access to a table still blocks read (only) access.

Since SQL Server 2005 this can be fixed by using the "row versioning". You need to change the settings of the database to enable this.

See the manual for a more detailed explanation:

http://msdn.microsoft.com/en-us/library/ms345124%28SQL.90%29.aspx