>I see you found some enhancement when you created a clustered index.
>
>All my tables have clustered indexes on the primary key.
>
>As most of the searches are against the primary key, this helps a lot. Also, when no other indexes can be found, from the execution plan, for a good performance, the clustered index is usually used.

A few other points:

Another area where a clustered index will help is when the majority of queries need to search/scan based on "most common order" of the table. Sometimes people will build clustered indexes on a date column...IF the most common search/scan on that table is for a date/range of dates.

But as someone also said....a person needs to think long and hard about using a value as a clustered index if newly inserted values are random and don't follow any sort pattern. That's when fragmentation/performance issues can occur.

So knowing the most common data access strategy will help - otherwise it's better to just play it safe.