while updating table with rowlock facing performance issue
" update omro_fet_op_itm_tmp with (rowlock) set sl_no = @count, @count = @count+1 where guid = @guid_tmp " One of our client had performance related issues.every month end of transaction has been blocked due to some deadlock arises. normally they are using all their stored procedures. they added with(rowlock) hints.i just mentioned example statement. Sometimes they are using a code like delete xy table where guid =@guid..it also causing performance issue.kindly tell me the solution.how i can tune that query.
The row lock is not needed when you delete one or few records, and if you try to delete a lot of records you may get performance related issues (due to memory and CPU). I hope they are not using rowlocks for read operations, because you may touch a lot of records in read operations. The issue you experience looks more like concurrent (long running) transactions trying to operating on same sets of records.