MSSQL Query Optimization

Here are the tutorials that will get you started in understanding MSSQL Query Processing and Optimization.

These topics cover MSSQL Execution plan, how to analyse it and then optimize the query.
These also talks about Indexes.
At the end attached a white paper on how any RDBMS System process queries

SQL Performance

1.       SQL Server Performance: Introduction to Query Tuning [includes, introduction to understanding execution plans] [Pinal Dev]
http://pluralsight.com/training/Courses/TableOfContents/query-tuning-introduction

for more details on indexes watch this
2.       SQL Server Performance: Indexing Basics [Pinal Dev]
http://pluralsight.com/training/Courses/TableOfContents/sql-server-indexing

to know how Pinal Dev does performance tuning in a real world scenario, watch this.
3.       Play by Play: Pinal Dave- Pinal Dave discusses real-world SQL Server performance tuning and query optimization.
http://pluralsight.com/training/Courses/TableOfContents/play-by-play-pinal-dave

for some trivia, watch this
4.       SQL Server Questions and Answers [Pinal Dev]
http://pluralsight.com/training/Courses/TableOfContents/sql-server-qa

SQL Performance Advanced

for more details on execution plan, watch this
5.       SQL Server: Query Plan Analysis, Execution plan in detail
http://pluralsight.com/training/Courses/TableOfContents/sqlserver-query-plan-analysis

6.       SQL Server: Performance Troubleshooting Using Wait Statistics
http://pluralsight.com/training/Courses/TableOfContents/sqlserver-waits

7.   To know how SQL query is processed , read this article.

introduction to query processing and optimization