>>It came back today. I got it three times. I really do not know how to adjust that to avoid this deadlock problem:
>>
>>"Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
>>
>>

>>DECLARE @NoMember Integer
>>DECLARE @LastAccess DateTime
>>
>>SET @NoMember=12985
>>SET @LastAccess='2009-02-13 11:02:33'
>>
>>SELECT Member.Numero,Member.Nom,Member.Prenom,Member.LastAccess
>> FROM Member
>> WHERE Member.LastAccess>=@LastAccess AND
>> EXISTS (SELECT 1 FROM Monitor WHERE Monitor.NoMember2=Member.Numero AND Monitor.NoMember=@NoMember)
>> ORDER BY 2
>>

>>
>>I have the feeling SQL Server doesn't like to handle datetime indexes. Wherever I have seen that in the past, there was always a datetime included in the SQL command. Is the trick to avoid that and to get more optimization is to maintain a numeric datetime fields? If yes, what would be the best approach to handle that?
>
>Is this problem when you click on the Profile to get messages? I got a long delay there today.
>
>In SQL Server 2008 you can use date field, but I don't know if this is going to solve the problem. You can use DATETIMEOFFSET as well.

I 'd like to add that by using the NOLOCK hint there is the possibility to read dirty data. So there is no guarranty that when the transaction will be commited, that the information will not have change.

This is something that must be judged by the situation we are dealing each time and the expected outcome.

More important, the cause of the block, though tracing or other methods, should be investigated first and act accordingly.