There are several reasons that could cause a query or updates to take longer then expected. This could be due to network issues or it could even be due to the database design itself, but most of the time slow running queries are caused due to:

  1. Network related issues (check your network bandwidth)
  2. Insufficient memory on the system or for SQL Server
  3. Insufficient indexes
  4. Out of date statistics


In any case, you should check the following in order to troubleshoot slow running queries:

  1. Try copying a large file from your client machine to the server machine and the other way around to get a rough idea of your network performance at the time.  You can also try copying the file on the disk where the data files are residing, sometimes the disk i/o could also cause slowness which may be fixed by upgrading the firmware for the disk controller.  For more precise network statistics use perfmon counters, or tools like netmon, etc.
  2. If network is ruled out as the culprit, then identify which query or group of queries are performing slow. 
    1. Use SQL Profiler to identify slow running queries
    2. Then, get its execution plan.  One way to do that is by executing SET SHOWPLAN ON and then re-running the query.
    3. Check if the statistics exist and are up-to-date.  You can run CREATE STATISTICS command to create statistics for the specified table.  Make sure that you have AUTO_CREATE_STATISTICS turned on at the database level to avoid statistics related issues.  If the statistics have been created, check if AUTO CREATE and AUTO UPDATE statistics options are turned on at the database level; this tells SQL Server to automatically create and update statistics when needed.  You can also run sp_updatestats system stored procedure on table/database to manually update the stats.
    4. Use the Database Tuning Advisor (DTA) to identify and create missing indexes.
    5. Finally, make sure the data files are residing on the appropriate RAID level based on your needs and standards.
  3. Check your system to see if memory is the bottleneck.  Check for memory utilization, pagination, etc.
  4. Check if the query(s) is able to use all the available processors.  If not, make appropriate adjustments, of possible.

 

While this is far from a complete troubleshooting guide, this should get you started…

Special thanks to Saleem Hakani for this information!