To optimize the performance of your database, you need to monitor and tune. You determine the performance baseline, how SQL Server is currently performing, through monitoring. Your optimization process includes making changes and then monitoring their effect. This means adjusting two overlapping performance areas, that is, database server performance and query performance.
Well-designed indexes are an important part of database optimization because they are the key factor in achieving optimal query performance and response time. In the meantime, poorly designed indexes, such as missing or excessive indexes on tables, indexes placed on the wrong column or inadequate index maintenance strategies, can be a source of impaired query and database performance.
Checkout my article here, in which I shared I some guidelines, which you can follow to make indexes more effective, and improve performance during the creation, implementation and maintenance of indexes.
This article is published on SearchSQLServer.techtarget.com.