How to rebuild indexes in SQL Server

Rebuilding indexes in SQL Server is an essential maintenance task that helps optimize database performance and improve query execution times. Indexes play a crucial role in speeding up data retrieval by providing quick access paths to rows in a table. However, over time, indexes can become fragmented due to data modifications such as inserts, updates, and deletes, leading to degraded performance.

We strongly recommend users to sign up:

SELECT
sqlschema.name AS 'SchemaName',
object_name(sqlfrag.object_id) AS 'TableName',
sqlindexes.name AS 'IndexName',
sqlfrag.alloc_unit_type_desc AS 'AllocUnitType',
sqlfrag.index_type_desc AS 'IndexType',
sqlfrag.page_count AS 'PageCount',
sqlfrag.index_depth AS 'IndexDepth',
sqlfrag.avg_fragmentation_in_percent AS 'AvgFragmentationPercent',
sqlfrag.fragment_count AS 'FragmentCount',
sqlfrag.avg_fragment_size_in_pages AS 'AvgFragmentPageCount',
sqlfrag.object_id,
sqlfrag.index_id,
sqlfrag.partition_number
FROM sys.dm_db_index_physical_stats(DB_ID(),null,null,null,'LIMITED') sqlfrag
LEFT OUTER JOIN sys.indexes sqlindexes (NOLOCK) ON sqlindexes.object_id = sqlfrag.object_id AND sqlindexes.index_id = sqlfrag.index_id
JOIN sys.objects sqlobjects (NOLOCK) ON sqlfrag.object_id = sqlobjects.object_id
JOIN sys.schemas AS sqlschema (NOLOCK) ON sqlschema.schema_id = sqlobjects.schema_id
WHERE sqlobjects.is_ms_shipped = 0
AND sqlobjects.object_id not in (SELECT major_id FROM sys.extended_properties (NOLOCK) WHERE name = N'microsoft_database_tools_support')
AND sqlfrag.index_id <> 0
AND page_count > 5000
AND avg_fragmentation_in_percent > 10
ORDER BY sqlfrag.page_count DESC

Hope this helps!
Contact SQLOPS today to leverage our expertise and ensure your database operates at its peak efficiency.