As we know Indexing is most important point at performance context but here I am going to discuss something which should keep in mind for performance. 

Following is very simple concept of RDBMS.

Table seek is good for large tables (large No. of  rows) but for the small table (few no. of rows like 10-50 rows)  Table Scan is helpful for them.

The reason behind it is that; Indexes always performs B-Tree searching. And if we apply it for small table than it takes more time to search 

instead for large table it is good. We can measure it by "Logical Reads Count".

Follow some snaps for more details. you can also perform it at your end too.

For the beginners I would like to elaborate what is Table Seek and Table Scan

Table Scan: It is an operator for any heap table (which is not having any clustered index). Record will search row by row.

Follow: http://msdn.microsoft.com/en-us/library/ms181129.aspx

Table Seek: It is an operator for searching for Indexed Table.

Follow: http://msdn.microsoft.com/en-us/library/ms190400.aspx

OPERATION WITH SMALL TABLE

OPERATION WITH LARGE TABLE