sql-serversql-server-2012database-performancelong-running-processesserver-configuration

SQL Server long running query taking hours but using low CPU

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.

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.

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.

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.

I have full control of the server so I can change any configuration parameter.

I have a few questions:

  1. Is it possible to improve the performance of the queries using parallelism?
  2. Why is the CPU usage so low?
  3. What are the best practises for configuring SQL Server?
  4. 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.

EDIT: I checked the log and I found this:

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

Solution
    1. Bump up max memory to 24 gb.
    2. Move tempdb off the c drive and consider mult tempdb files, with auto grow at least 128 Mbps or 256 Mbps.
    3. Install performance dashboard and run performance dashboard report to see what queries are running and check waits.
    4. If you are using auto grow on user data log and log files of 10%, change that to something similar to tempdb growth above.
    5. Using performance dashboard check for obvious missing indexes that predict 95% or higher improvement impact.
    6. Disregard all the nay Sayers who say not to do what I'm suggesting. If you do these 5 things and you're still having trouble post some of the results from performance dashboard, which by the way is free.
    7. One more thing that may be helpful, download and install the sp_whoisactive stored proc, run it and see what processes are running. Research the queries that you find after running sp_whoisactive.