Scenario
Searching and reading data is getting slower even after performed increased Disk Free Space and Disk Defragmentation but SSD is not an option then you can continue reading on how to detect database fragmentation and how to perform database defragmentation
Why need to perform rebuild and reorganize indexes?
>> To allow you to get the requested information quicker
When should perform rebuild and reorganize indexes?
>> When fragmentation is 30% or higher
Solution
- Connect your database with SSMS (SQL Server Management Studio)
https://support.qne.com.my/a/solutions/articles/81000385373
- Right-click your server instance in Object Explorer, and then select New Query
- To find out which are those indexes fragmentation percentage is 30% and higher
Paste the following T-SQL code snippet into the query window and Replace [SampleDB_Testing] to your database name:
-----Replace [SampleDB_Testing] to your database name-----
USE [SampleDB_Testing]
SELECT OBJECT_NAME(ips.OBJECT_ID)
,i.NAME
,ips.index_id
,index_type_desc
,avg_fragmentation_in_percent
,avg_page_space_used_in_percent
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
WHERE avg_fragmentation_in_percent >= 30
ORDER BY avg_fragmentation_in_percent DESC
- Execute the query by selecting Execute or selecting F5 on your keyboard
- The results of the indexes fragmentation is 30% and higher are displayed under the area where the text was entered.
- To Rebuild and reorganize indexes fragmentation percentage is 30% and higher
Right-click your server instance in Object Explorer, and then select New Query
- Paste the following T-SQL code snippet into the query window and Replace [SampleDB_Testing] to your database name:
-----Replace [SampleDB_Testing] to your database name-----
USE [SampleDB_Testing]
declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)
declare FragmentedTableList cursor for
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE
-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC
OPEN FragmentedTableList
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
if(@percentFragment<= 30)
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ')
print 'Finished reorganizing ' + @indexName + 'on table ' + @tableName
END
ELSE
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REORGANIZE;')
print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
END
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment
END
CLOSE FragmentedTableList
DEALLOCATE FragmentedTableList
- Execute the query by selecting Execute or selecting F5 on your keyboard.
- After the query is complete, all indexes with fragmentation more than 29.99% is defragmented
Know more about Optimize index maintenance to improve query performance and reduce resource consumption