This assumes you already know the basic layout/ SQL version of the remote location
- Exact details of issue (e.g 9 times out of ten it takes more than 30 seconds to run)
- Actual query plan
- Fragmentation of indexes related to the database concerned
- Rowcounts of tables
- Error logs if applicable
- 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.