Most queries return a handful of rows by filtering on a couple columns. A non-clustered index duplicates those columns you are filtering on into their own separate data structure, where it keeps these columns sorted (that’s the secret to knowing where things are at). Now your query can read a subset of your total data size to get what you need. This makes things very fast when compared to using the basic storage you would get in a clustered index (see T-SQL Tip 4). To oversimplify index design, if it is in the WHERE clause, it should be in an index (most of the time). Adding basic non-clustered indexes can easily contribute to order of magnitude improvements.
