I have a stored proc being called in a .NET application from a SqlDataAdapter as the Fill method. While the call to stored proc is being processed I get an entry in the SQL server Activity Monitor indicating that another process is blocked by the .NET app and the WaitType is listed as LCK_M_IX. When the Fill method returns the block is removed and the previously blocked process continues. The issue is that the call to the Fill method is made often and by multiple processes so the lock is almost always in place. I don't see anything in the stored procedure that warrents the lock. I've listed the stored proc below. Can someone suggest how I can execute this proc without bringing the db to a standstill? Thanks.
The stored proc looks like this:
CREATE PROC [dbo].[uspMsgProcessSelect]
@MaxRowCount int = 0
SET ROWCOUNT @MaxRowCount
S.MsgProcessed IS NULL
S.MsgClaimed IS NULL