Road map of steps that I can use to troubleshoot the performance of a TSQL statement or a SP and keep track of the things discovered on the way for future reference
My way of achieving it
Using scripts that I gathered or taken from what other people shared over time over the internet and by scripts that I created.
1. The script can be found in this book, SQL Server DMVs in Action by Ian W. Stirk right under — Listing 3.6 Identifying indexes used by a given routine
2. The script can be downloaded from here
3. The script can be downloaded from here
4. The script can be downloaded from here
5. The script can be downloaded from here
6. Check the execution plan of the routine in XML format and inside search after PARAMETERCOMPILEDVALUE. Check if the values from there are identical with the values that the application is using or the job.
In most of the cases the index optimization or updating the statistics will help us and maybe will solve the issue but in other cases as the post stated the above steps are just the beginning of troubleshooting.