Yes, the conclusion we have come to is that we either use the (PAGLOCK) hint or change the Transaction Isolation Level to READ UNCOMMITTED when we process SELECTS. Obviously...

Thanks Karl,

I think you have answered my question in terms of the cause of the problem. Sorting an overall solution may be more tricky for us.

I am...

I should also say that the Execution plan says it is going to do a Clustered Index Seek.

Just to go over the problem -

We are running SELECT statements on the CONTACT table (using the default READ COMMITTED isolation level). When we do this we get...

We know what is going on in the sense that:

If We run the following in Query Analyser:

SELECT SERIALNO, TITLE, FIRSTNAME, SURNAME FROM

CONTACT WHERE SURNAME LIKE 'A%'

We get a shared table...

The weird thing is that originally we didn't get the problem on our copy of our customer's database.

....until we rebuilt all the indexes (the Unique Clustered Index on SERIALNO in...

I'm using SELECT * from as a simple example. Doing SELECT SERIALNO, EMAILADRESS FROM CONTACT WHERE.....

gives us the same problem except that the query running speed is much faster...

Hi Carolyn,

Do you mean

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?

We are experimenting with this, but I'm still unsure as to why the query optimizer is deciding to use table level locking...