There are hardware focused optimizations that can and do improve your users data access, and you can fix things at the code level too. Then there is optimizing your database engine to squeeze out every last bit of performance.
Are you just a bit curious on how to get started?
A recent blog article targets many areas in your code that you can mitigate to help deliver the best performance, there are also indexing guides which I don't think I've ever covered in my blog, check it out here:
which covers these topics,
- Database Optimization (in the Codebase) Indexes
- Tune SQL query performance by avoiding coding loops
- Avoid correlated subqueries
- Select sparingly
- The use of temp tables SQL Server Performance Tuning: Execution Plans
To optimize your existing indexes you can refer to my previous article Boosting Performance and review how to get that database back on track!