---
title: "Reindexing - Index Fragmentation"
canonical: "https://wiki.soft1.eu/space/SS5EN/1236271859/Reindexing%20-%20Index%20Fragmentation"
format: markdown
---
#### ➡️ Reindexing DBs
Go to [https://ola.hallengren.com/scripts/](https://ola.hallengren.com/scripts/) to download [MaintenanceSolution.sql](https://ola.hallengren.com/scripts/MaintenanceSolution.sql). The script creates a stored procedure IndexOptimize in the selected database.
:info: *The full documentation in English is available **[here](http://ola.hallengren.com/Documentation.html#IndexOptimize)**. *
📢 **Note that**
- It is suggested to create a master database or a new database *(e.g., named UTILITIES)* that will include only maintenance tools.
- Before defragmentation, take a backup copy of these databases.
- After defragmentation, check the size of the Transaction Log, as it may increase significantly.
- Rebuild or Reorganise indexes with fragmentation in all user databases: The script (by default) does not defragment indexes if they include fewer than 1000 pages (page_count). To change this behavior, you should add the @PageCountLevel parameter to the command: @PageCountLevel = **.**
---
#### ➡️ Check Index Fragmentation
DECLARE @dbName nvarchar(255);
SELECT @dbName = 'db name to be checked';
SELECT
[db.name](http://db.name/) AS databaseName
, ps.OBJECT_ID AS objectID
, OBJECT_NAME ( ps.OBJECT_ID, db_id(@dbName) ) AS tableName
, ps.index_id AS indexID
, [si.name](http://si.name/) AS indexName
, ps.partition_number AS partitionNumber
, ps.avg_fragmentation_in_percent AS fragmentation
, ps.page_count
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (db_id(@dbName), NULL, NULL , NULL, N'Limited') ps
ON db.database_id = ps.database_id
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID AND ps.index_id = si.index_id
WHERE ps.index_id > 0
AND ps.page_count > 100
AND ps.avg_fragmentation_in_percent > 30
ORDER BY tableName, indexName
OPTION (MaxDop 1);