There is a great e-book on this topic: “Dissecting SQL Server Execution Plans” by Grant Fritchey.
Why is this query running slow? Is my index getting used? Why does this query run faster than this query? Well, have you even looked at the execution plan?
Execution plans show you what’s going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including which indexes are getting used and where no indexes are being used at all; how the data is being retrieved, and joined, from the tables defined in your query; how aggregations in GROUP BY queries are put together and the anticipated load, and the estimated cost, that all these operations place upon the system. All this information makes the execution plan a vitally important tool in the tool belt of database administrator, database developers, report writers, developers, and pretty much anyone who writes TSQL to access data in a SQL Server database.
The goal with this book was to gather as much useful information on execution plans as possible into a single location, and to organize it in such as way that it provided a clear route through the subject, right from the basics of capturing plans, through their interpretation, and then onto how to use them to understand how you might optimize your SQL queries, improve your indexing strategy, spot some common performance issues, and more.