Study the internals of SQL Server and practice the art of query plan prediction.
Practice by writing queries against data you know cold (cardinality, density, distribution, etc). Make sure the queries are complex enough that it's a challenge. Then see if you can picture the plan in your head. Does that compare to the actual. When you start to figure out when SQL will pick LOOP v HASH v MERGE join types and other such operations like sorts, tops, etc ... Then you are starting to get a feel for it.
Regarding Internals documentation, there's never been a book to top this one: https://www.amazon.com/Gurus-Guide-SQL-Server-Boxed/dp/0321287509/. Since Ken is no longer with us :(, I'm not sure what the best current book is. The way I learned how to do this by feel is studying internals.
You can download a free version of SQL Server (many versions) so that you can look at query plans: https://www.microsoft.com/en-us/download/details.aspx?id=42299. Use that for practice.
There are many well documented sample databases for practice:
https://www.codeproject.com/Articles/20987/HowTo-Install-the-Northwind-and-Pubs-Sample-Databa
https://blogs.msdn.microsoft.com/samlester/2012/08/23/finding-the-correct-version-of-the-adventureworks-sql-server-sample-database/
Before the days of graphical query plans, we used time and io stats and showplan.
Study the internals of SQL Server and practice the art of query plan prediction.
Practice by writing queries against data you know cold (cardinality, density, distribution, etc). Make sure the queries are complex enough that it's a challenge. Then see if you can picture the plan in your head. Does that compare to the actual. When you start to figure out when SQL will pick LOOP v HASH v MERGE join types and other such operations like sorts, tops, etc ... Then you are starting to get a feel for it.
Regarding Internals documentation, there's never been a book to top this one: https://www.amazon.com/Gurus-Guide-SQL-Server-Boxed/dp/0321287509/. Since Ken is no longer with us :(, I'm not sure what the best current book is. The way I learned how to do this by feel is studying internals.
You can download a free version of SQL Server (many versions) so that you can look at query plans: https://www.microsoft.com/en-us/download/details.aspx?id=42299. Use that for practice.
There are many well documented sample databases for practice: https://www.codeproject.com/Articles/20987/HowTo-Install-the-Northwind-and-Pubs-Sample-Databa https://blogs.msdn.microsoft.com/samlester/2012/08/23/finding-the-correct-version-of-the-adventureworks-sql-server-sample-database/
Before the days of graphical query plans, we used time and io stats and showplan.
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql