title: Many operations time out and block process in SQL Server
body: <p>We have a lot of operations that time out in our site log.</p>
  
  <p>After installing Redgate SQL Monitor on the server, we figured out we have many blocked processes and some times deadlock.</p>
  
  <p>With Redgate we realized problem is for a stored procedure. It's a simple stored procedure and just increase view count of product (simple update)</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SP_IncreaseProductView]
      @PID int 
  AS
  BEGIN
      UPDATE dbo.stProduct 
      SET ViewCount = ViewCount + 1 
      WHERE ID = @PID
  END
  </code></pre>
  
  <p>When that stored procedure is off, everything is fine but some times block process error is back.</p>
  
  <p>This table(product) hasn't any trigger. but its like heart of system and has 12000 records.</p>
  
  <p>It has 3 indexes, 1 clustered and 2 non-clustered, and many statistics </p>
  
  <p>We don't have any transaction. block process mostly happens in update query.</p>
  
  <p>How can I figure out where the problem is?</p>
  
  <p>Sorry for my bad languages</p>
  
  <p>Thanks</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I think the problem isn't the <code>SP</code>, its about update on product table (Its my opinion). Its large table. I still get block process when <code>SP</code> is off but less.
  We have a lot of select and update on this table.</p>
  
  <p>Also i rewrite the increase view count with <code>LINQ to SQL</code>, but still get block process like when <code>SP</code> is on.</p>
  
  <p><strong>Edit 2:</strong></p>
  
  <p>I set profiler and get all query on product table.
  530 select (most with join with another 2 table) and 25 update per minute (on product table only).</p>
  
  <p>For now, <code>[SP_IncreaseProductView]</code> is off. Because when its on, we getting block process and operation timed out about every 10 second and web site stopped.</p>
  
  <p>After that (set <code>SP</code> to off) block process still exist but roughly 50 per day.</p>