which i can see only by queries? or I can see it somewhere else? (Profiler/PerfMon etc..)

one more thing,

How can I know (during load) if the SQL is now in bottleneck ?

But if I can see all performance counters, and open an SQL profiler I can find the reason, am I wrong?

I now added page splits (at perfmon) and SP:Recompile (at...

thank you all.

Jeff, I really need to do it in segments according to TOP (@TOP_HANDLED_ROWS).

according to your suggestion, i'm still a bit afraid to leave it without any...

OK.

chance that NOLOCK will be ignored is other thing than NOLOCK to be harmful. so I can keep this NOLOCK anyway, what do I have to lose here?

How do you...

i put a NOLOCK of the select statement within the CTE just to fetch the records with the right criteria without any locks. than , in the same CTE transaction...

Yes, i just took off a row in the update that can be classified.

I work with millions of rows so I'm using TOP (+including indexes) to make the query fast...

read uncommitted. (specified in the XML deadlock)

WHY ??

Can you explain the deadlock? and how removing ROWLOCK will solve this out? the update has TOP of 100 only..

BTW, what did you say about the index? should...

Hi,

I updated as the people here suggested.

when i only run :

SELECT TOP (100) ID

FROM dbo.TableA

WHERE TableAStatusTypeID IN (1, 5, 9) AND LastUpdateTime <= dateadd(ss, Retries * -30, @CurUtcDate)

ORDER BY LastUpdateTime...

I see. well, I'm running it with read uncommitted isolation level. as i don't care few updates will not be performed exactly as i plan.

as for the execution plan,...

it seems to me you are all missing the whole point.

i want to lock only rows (i don't want SQL to lock page / table).

i'm dealing with a huge runtime...