Fine Tuning Queries

Database administrators have to balance between creating too many indexes and too few indexes. For example, there is no need to index every column individually or involve the column in many overlapping indexes. They should also take into consideration that, the index that will enhance the performance of SELECT queries will also slow down the different DML operations, such as INSERT, UPDATE and DELETE queries.

Another thing to consider is the tuning of the index itself, as the index that is working fine with your queries in the past may not fit the queries now, due to frequent changes in the table schema and data itself. This may require removing the index and create more effective one. On the other hand, the index may be suffering from fragmentation issues only due to changing the data very frequently, that can be resolved using the different index maintaining tasks.

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
SELECT * FROM tblTable01 WHERE Col01 < 3000

The performance of the query can be also tuned using the combination of the SQL Server Profiler and the Database Engine Tuning Advisor tools. 

Sources:

Tracing and tuning queries using SQL Server indexes