This is the scenario that we have:
a) SQL Server 2012 has been allocated 16GB of RAM.
b) We have two databases. The total page count is approx. 400,000 across both databases so should be around 3.2GB which should easily be able to be cached in memory.
c) The databases are heavily updated i.e. rows are frequently being inserted, updated and removed.
d) After a relatively short period of time quite a few of the indexes are heavily fragmented. My understanding is that if the indexes are in memory then this should not be an issue. The PK of the tables is an integer - there is a GUID column in one of the tables but there is no index on this.
e) On a customer system performance was becoming an issue. The databases indexes were all rebuilt and the system ran fine again. After a relatively short period of time quite a few on the indexes were heavily fragmented again but the system was running well and continues to run well.
I am trying to understand why fragmented appeared to cause an issue but no longer does. What metrics could I monitor to know if the database is not performing well - fragmentation does not seem to be enough on its own.