Shedding more light on a frequent check up called ‘Fragmentation’ whenever we get issues on application slowness or bad server performance-
As the data in Microsoft SQL Server tables changes their indexes change. Over time these indexes become fragmented. This fragmentation will adversely affect performance.
Common questions that arrive in our minds is “How is SQL Server’s fragmentation affecting my Server?”
Fragmentation of SQL Server indexes mainly creates wasted space that can affect your server performance much more than one might expect.
Fragmentation of your SQL Server’s internal allocations and page structures result in ‘gaps’ or ‘void’ space that is dead weight carried along with valid data. Your backups, storage, I/O channels, buffer memory, cached data, logs, tempdb, CPU’s and query plans are impacted by these unnecessary voids.
SQL Server’s fragmentation continually eats away at these resources with nearly every update, delete, insert, and table/index change. If ignored, fragmentation can be the proverbial ‘death by a thousand cuts’ to a server’s performance and scalability.
What creates the voids and other adverse effects and how do I get a handle on them?
Typical, day to day activity causes SQL Servers to fragment over time. Changes to your data – inserts, updates, deletes, and even changing varchar values contribute to fragmentation. The full list of actions that cause fragmentation is long and the rate of fragmentation varies across different indexes and tables.
Sometimes there is a pattern resulting from seasonal or annual peaks (e.g., when financials run).
But more often than not, it is difficult to find, predict and proactively manage fragmentation manually.
Look for the index fragmentation levels by using SQL Server DMV’s and perform rebuild/reorganize operation on indexes.
Scheduling jobs on a timely basis, helps to improve the performance in a slight manner, but simultaneously we should look to fine tune the application queries as well (Are they running with bad execution plans) to serve a long term solution and keep the server performance tuned.