SQL Server long running query taking hours but using low CPU

sql-server sql-server-2012 database-performance long-running-processes server-configuration
Francisco Goldenstein picture Francisco Goldenstein · Mar 18, 2015 · Viewed 10.6k times · Source

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

Answer

DBA TED picture DBA TED · Apr 13, 2016
  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.

Related questions

How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
Auto increment primary key in SQL Server Management Studio 2012

How do I auto increment the primary key in a SQL Server database table, I've had a look through the forum but can't see how. I've looked the the properties but can't see an option, I have seen an answer …

Read More
Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More