These are few tips to increase SQL Performance:
- An index increases time to perform INSERT / UPDATE / DELETE operations. So minimal uses of indexes should be preferred. Not more than 4-5 indexes per table
- Indexes should be on Integer values than Character values.
- While generating Composite Key, ensure that the most important key is on the left.
- Clustered indexes are more preferable than nonclustered
- If the same query is executed over and again, prefer having an index on the search criteria columns. It will enhance your performance
- Join tables based on Integer columns than String based columns.
- Use a SQL Server Profiler before deploying your application
I hope this helps!