aviadavi (2/27/2013)
if I will remove the hints, SQL will make up on his own.. and will lock pages/tables as well (this i want to avoid)
Correct, it will. This is the recommended approach. Let SQL manage the locking, don't try to outsmart it as you will often fail (unless you know exactly what you're doing and why)
now, anyone have an idea why there is a lock escalation in my statement?? although there is TOP clause.
I explained that already.
You're probably getting escalation because of the rowlock hint.
Without the hint, SQL takes locks at whatever granularity it thinks best and escalates to table locks at a specific threshold
With the hint, SQL takes row locks and escalates to table locks at the same threshold.
So if SQL would have taken page locks, but you force it to take row locks, that means more locks taken and an earlier escalation.
Plus the query is inefficient and probably lacking useful indexes, so SQL has to read and lock a lot more of the table than it would were the query efficient with suitable indexes.
getting rid of the ROWLOCK is impossible as i don't want it to be locked in any other level than ROW.
Short of traceflags that disable lock escalation completely and leave your server at risk of running out of lock memory, throwing ugly errors and potentially crashing, you cannot force only row locks. The ROWLOCK hint says 'start with row locks, escalate to table if necessary'
I recommend you tune that query, the changes the others have mentioned will make it more efficient and assuming you have appropriate indexes will mean that SQL will have to read and lock less of the table, likely resulting in row locks by default, without needing the hint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability