{"id":41582,"date":"2016-10-24T00:00:00","date_gmt":"2016-10-24T00:00:00","guid":{"rendered":"https:\/\/www.techopedia.com\/experts\/what-does-defragmentation-do-for-sql-systems\/"},"modified":"2020-07-06T20:32:01","modified_gmt":"2020-07-06T20:32:01","slug":"what-does-defragmentation-do-for-sql-systems","status":"publish","type":"q-a","link":"https:\/\/www.techopedia.com\/7\/32158\/enterprise\/databases\/what-does-defragmentation-do-for-sql-systems","title":{"rendered":"What does defragmentation do for SQL systems?"},"content":{"rendered":"

Continuous database<\/a> maintenance and monitoring are the key elements to be considered for running a SQL<\/a> system smoothly. When a database is created and populated, initially the data is placed in a contiguous physical location (if sufficient contiguous physical space is available). So, in this case the logical ordering and physical ordering of data are likely to be similar, and it increases performance.<\/p>\n

When the data is modified, deleted or updated, relevant indexes are also updated automatically to reflect those changes. As a result, indexes become fragmented and the information scattered across the storage space. It changes the physical ordering of data (as it loses contiguous allocation) and the retrieval becomes time consuming, resulting in slow database performance.<\/p>\n

The solution to this problem is to perform defragmentation<\/a> on a periodic basis. Defragmentation actually rebuilds or reorganizes the indexes<\/a> to match the logical ordering of data with the physical ordering. Before performing any defragmentation operation, all the indexes should be analyzed properly. The analysis results determine whether a reorganization or rebuild is required.<\/p>\n

The two main operations performed by the defragmentation process are:<\/p>\n