Information to gather when troubleshooting performance of a query from a remote location

This assumes you already know the basic layout/ SQL version of the remote location

  1. Exact details of issue (e.g 9 times out of ten it takes more than 30 seconds to run)
  2. Actual query plan
  3. Fragmentation of indexes related to the database concerned
  4. Rowcounts of tables
  5. Error logs if applicable
  6. List of recent most expensive queries

From the above information this is what you can attempt to analyze

  • Where the delay is in the query you are troubleshooting by analyzing high cost portions of execution plan
  • Advise about levels of fragmentation
  • Try to reproduce the issue by achieving similar rowcounts of data in your dev environments
  • Find out if any other expensive queries could be hogging resources
  • Check for implicit conversions

We will continue to add to this, however want it to be a fairly simple list.

Feel free to share any helpful troubleshooting queries you may have in your toolbox.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.