One of the first things to do when you have performance issues is to find the low hanging fruit. My definition of low hanging fruit is any change that does not affect business or application logic. For example: Adding a much needed index on a table for coverage.
Although this list is by far not complete, it covers many of the common areas I have found performance issues with:
Let me give an example. Below are some numbers from DBCC SHOWCONTIG. I have removed some of the information to allow us to focus on the problem:
These are real numbers from a real system. This is one of the heaviest hit tables in the system. The query plan often generates table scans for queries against this table.
If you didn't catch the issue, see the 'Extent Scan Fragmentation'. This number presents the degree of physical 'fragmentation' for this table. You can think of this as a kind of indicator that represents the order of the data on your disk. A lower number means the data is more sequential on the disk. A higher number means the data is more randomly ordered on the disk. When you have a higher number and your queries generate a table scan, in essence, that means instead of doing sequential I/O, your end up doing random I/O. This can reduce performance as much as 3X.
One way you might 'fix' this problem is to rebuild your clustered index. In essence, physically reordering your data on the disk. This might make your system run faster for a while, but it will not really fix the problem. The problem was caused by having a clustered index on the table where the index is built over a non-monotonically increasing value, like a guid/unique identifier. Fixing the problem would be doing something that reduces the fragmentation. Rebuilding the index is treating the symptom. In some cases this is the best you can do.
Much of the low hanging fruit can be found looking at your perf counters. For example, if you see that your CPU is somewhat low, but your disk queue is high and your seeing a lot of page faults, you probably have an I/O problem caused by not enough throughput from your disks (add more spindles) or not enough memory (add more memory). If you find that your throughput is poor, but your perf counters look normal, make sure you examine your locks and network interface.
Something else that I have found all to frequently is the use of value types for identity which are way too big. For example, if you have a domain with four values in it, using a unique identifier is completely valid and over kill. I saw an attempt at building a data warehouse where a large dimension had a guid for the primary key on the table. A shopping cart analysis over a small sample of the data was taking hours to process. Changing the primary key to an int using the same data took only a few minutes to run.
One of the worst performance issues I have seen in the last few years was created on a system that was trying to keep an inventory on PCs. On each new item added to the inventory, a trigger was used to update reports. With small amounts of data, this seemed to run fine. However, once the database was populated, it was not uncommon for a single write operation to add an item to inventory resulted in tens of thousands of read operations to update the reports. This was a two fold problem: First there was no real business need to keep all of the views 100% up-to-date. Second, the system was effectively not usable after running for about 2 weeks. The fix for this was to remove the triggers and add in a semi-regular process to rebuild the reports.