Hi,

Recently, our client database give problem of query that lock up
tables, causing our web-based application to halt. We found that
simple query like (this is a make up query with similar code like
my actually query as I can't use my actual query here, my actual
query have 4-5 joins)

select a.name, b.add, c.tel
from name a inner join address b
on a.person_id = b.person_id and a.person_id = @i_person_id
and b.version = (select max(version) from address where person_id =
a.person_id)
inner join tel c on c.person_id = b.person_id
and c.version = (select max(version) from tel where person_id = b.person_id)

The above query work when our SQL server 2000 SP3 database size is around
1.5G. After the database size increase to around 2.4G, the above query
might not work(lock table) for different parameter person_id that
we pass it. Some of them work and some don't even though they
are the same query.

After some debugging and tunning, When we change the query to query the max(version) separately, our query work without any problem.

I am wondering why the query work when my DB size is small and
all the suddenly cannot work or halt my DB when the size is
big.

Anyone with similar issue or problem, pls kindly advise.
Thank you