How to monitor the performance of a SQL Server and the impact of fragmentation

Question

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.

Answer 1

Rebuilding the indexes clears the plan cache for everything using those indexes. Most likely you have a bad plan due to parameter sniffing causing the performance issue.

https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/#:~:text=Something%20Smells%20Funny,to%20create%20an%20execution%20plan.

Answer 2

Hi @Ian Hannah ,

Microsoft recommends that customers consider and adopt the following index maintenance strategy:

• Do not assume that index maintenance will always noticeably improve your workload.
• Measure the specific impact of reorganizing or rebuilding indexes on query performance in your workload. Query Store is a good way to measure the "before maintenance" and "after maintenance" performance using the A/B testing technique.
• If you observe that rebuilding indexes improves performance, try replacing it with updating statistics. This may result in a similar improvement. In that case, you may not need to rebuild indexes as frequently, or at all, and instead can perform periodic statistics updates. For some statistics, you may need to increase the sampling ratio using the WITH SAMPLE ... PERCENT or WITH FULLSCAN clauses (this is not common).
• Monitor index fragmentation and page density over time to see if there is a correlation between these values trending up or down, and query performance. If higher fragmentation or lower page density degrade performance unacceptably, reorganize or rebuild indexes. It is often sufficient to only reorganize or rebuild specific indexes used by queries with degraded performance. This avoids a higher resource cost of maintaining every index in the database.
• Establishing a correlation between fragmentation/page density and performance also lets you determine the frequency of index maintenance. Do not assume that maintenance must be performed on a fixed schedule. A better strategy is to monitor fragmentation and page density, and run index maintenance as needed before performance degrades unacceptably.
• If you have determined that index maintenance is needed and its resource cost is acceptable, perform maintenance during low resource usage times, if any, keeping in mind that resource usage patterns may change over time.

Refer to the MS document Optimize index maintenance to improve query performance and reduce resource consumption.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.