title: Concurrency in SQL Server 2008
body: <p>There is a stored procedure:</p>
  
  <pre><code> CREATE PROCEDURE [dbo].[TestProc]
   AS
   BEGIN 
       SET NOCOUNT ON;
  
       create table #thistable (rid char(32))
       insert into #thistable(rid)
       select A0RID from tblCdbA0 with (nolock)
    END
  </code></pre>
  
  <p>When the procedure is executed alone it takes 400-500 ms, but when 10 threads are executing the same procedure in parallel, then the first thread finishes in 1300 ms, the last - in 6000 ms and average is 4800 ms. 
  As you can see there is no locking where threads wait when other finish to execute. Moreover, server CPU is loaded at less then 100%, i.e. there are enough resources in order to execute them at the same time. How could that be?</p>
  
  <p>EDIT: Found a good article about concurrent inserts:
  <a href="http://sqlcat.com/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx" rel="nofollow noreferrer">Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads</a></p>