There are many factors which affect the performance of your database ranging from hardware to good design. Indexes Index design is a large concern when designing your data structure. SQL Server indexes are similar to the indexes you would find in the library or the tabs in a dictionary. I'm going to touch on how setting your indexes can affect performance. To retrieve data from a table, a SQL statement is passed and the table is searched in a linear mode until the data that is needed is come across.
Indexes eliminate whole table scans by allowing queries to access data by using the index directly.
This allows queries to perform lookups much more efficiently. The reason for this is simple if you think about it. Inserting a record in a table with a lot of indexes requires the insert to traverse all the indexes in the table. This is where the design aspect comes into play. If the majority of work running against a table is lookups then you will want to use indexes, if the majority of work done against a table is inserting then you probably do not want to use indexes.