title: SQL Server long running query taking hours but using low CPU
body: <p>I'm running some stored procedures in SQL Server 2012 under Windows Server 2012 in a dedicated server with 32 GB of RAM and 8 CPU cores. The CPU usage is always below 10% and the RAM usage is at 80% because SQL Server has 20 GB (of 32 GB) assigned. </p>
  
  <p>There are some stored procedures that are taking 4 hours some days and other days, with almost the same data, are taking 7 or 8 hours. </p>
  
  <p>I'm using the least restrictive isolation level so I think this should not be a locking problem. The database size is around 100 GB and the biggest table has around 5 million records.</p>
  
  <p>The processes have bulk inserts, updates and deletes (in some cases I can use truncate to avoid generating logs and save some time). I'm making some full-text-search queries in one table.</p>
  
  <p>I have full control of the server so I can change any configuration parameter.</p>
  
  <p>I have a few questions:</p>
  
  <ol>
  <li>Is it possible to improve the performance of the queries using
  parallelism?</li>
  <li>Why is the CPU usage so low?</li>
  <li>What are the best practises for configuring SQL Server?</li>
  <li>What are the best free tools for auditing the server? I tried one
  from Microsoft called SQL Server 2012 BPA but the report is always
  empty with no warnings.</li>
  </ol>
  
  <p>EDIT:
  I checked the log and I found this:</p>
  
  <p>03/18/2015 11:09:25,spid26s,Unknown,SQL Server has encountered 82 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL11.HLSQLSERVER\MSSQL\DATA\templog.ldf] in database [tempdb] (2).  The OS file handle is 0x0000000000000BF8.  The offset of the latest long I/O is: 0x00000001fe4000</p>