• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2760
  • Last Modified:

WaitType LCK_M_IX from select statement

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
AS

SET ROWCOUNT @MaxRowCount
SELECT
      S.ID,
      S.ReceivedAt,
      S.FromHost,
      S.[Message],
      S.DeviceReportedTime
FROM
      dbo.SysLog S
WHERE
      S.MsgProcessed IS NULL
      AND
      S.MsgClaimed IS NULL
ORDER BY
      S.ReceivedAt
0
Paracom_Inc
Asked:
Paracom_Inc
2 Solutions
 
RiteshShahCommented:
can you try this one?


CREATE PROC [dbo].[uspMsgProcessSelect]
      @MaxRowCount int = 0
AS

SET ROWCOUNT @MaxRowCount
SELECT
      S.ID,
      S.ReceivedAt,
      S.FromHost,
      S.[Message],
      S.DeviceReportedTime
FROM
      dbo.SysLog S WITH(NOLOCK)
WHERE
      S.MsgProcessed IS NULL
      AND
      S.MsgClaimed IS NULL
ORDER BY
      S.ReceivedAt
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use the nolock locking hint as RiteshShah wrote but then you should know that can return dirty reads (records that doesn't exist more for any reason).
Otherwise you can check for indexes to increase speed of the select. One column that should be indexed for sure is [ReceivedAt].

Good luck
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now