Some issues with SQL Server performance are fairly common between instances, and there are some basic things you can always check first when you begin your troubleshooting steps. In this article by Thomas LaRock, he lists the most common categories of performance issues.
Indexes are the number one cause of problems with SQL Server. That doesn’t mean SQL Server doesn’t do indexes well. These days SQL Server does indexing quite well, actually. No, the issue with indexes and SQL Server have to do with how easy it easy for users to make mistakes with regards to indexing. Missing indexes, wrong indexes, too many indexes, outdated statistics, or a lack of index maintenance are all common issues for users with little to no experience (what we lovingly call ‘accidental DBAs’).
I know, this area covers a LOT of ground. The truth is that with a little bit of regular maintenance a lot of these issues disappear. Keep in mind that your end-users don’t get alerted that the issue is with indexing. They just know that their queries are taking too long, and that’s when your phone rings. It’s up to you to know and understand how indexing works and how to design proper maintenance.