---
title: "what is index fragmentation in MS Sql Server"  
description: "what is index fragmentation in MS Sql Server"  
author: "Amit Singh"  
published: 2010-11-09  
updated: 2020-09-17  
canonical: https://www.mindstick.com/interview/219/what-is-index-fragmentation-in-ms-sql-server  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 2 minutes  

---

# what is index fragmentation in MS Sql Server

Index fragmentation is a phenomena where index contents are no longer stored continuously in the storage. When index contents become scattered in the storage, fragmented, performance on index will degrade.\
\
If you want to see the fragmentation level of an index, you can use the system function called sys.dm_db_index_physical_stats() in the following format:\
\
SELECT * FROM sys.dm_db_index_physical_stats(\
database_id, table_id, index_id, DEFAULT, DEFAULT )

## Answers

### Answer by Anurag Sharma

When we perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor. If you want to determine the level of fragmentation, you can use the DBCC SHOWCONTIG statement. The DBCC SHOWCONTIG statement displays fragmentation information for the data and indexes of the specified table or view.\
\

### Answer by Amit Singh

Index fragmentation is a phenomena where index contents are no longer stored continuously in the storage. When index contents become scattered in the storage, fragmented, performance on index will degrade.\
\
If you want to see the fragmentation level of an index, you can use the system function called sys.dm_db_index_physical_stats() in the following format:\
\
SELECT * FROM sys.dm_db_index_physical_stats(\
database_id, table_id, index_id, DEFAULT, DEFAULT )


---

Original Source: https://www.mindstick.com/interview/219/what-is-index-fragmentation-in-ms-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.