You can do a lot from within management studio, the third link posted by JF2015 is essential reading
If you've got the Northwind database, open a new query window against it and put in the following SQL
SELECT CustomerID, CompanyName
WHERE City= 'London'
Now choose Query -> Display Estimated Execution Plan
The database engine will show you that for this query it will attempt to perform an Index Seek
against the index named City
Now try the same thing with this SQL
SELECT * FROM Orders O
WHERE ShipCity Like '%London%'
ORDER BY OrderDate
You'll notice this time it's performing an Index Scan (80% of total query Cost) and a Sort (20%).
Depending on the amount of rows in the table, you would generally want to avoid a Scan. The engine has to analyse all the rows in the table to determine which rows qualify for your statement
The execution plan window is a really useful part of SQL Server, you should get used to checking SQL statements here.