How to rebuild indexes in SQL Server

Adam Fulton
crop cyber spy hacking system while typing on laptop
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 AS 'SchemaName',
object_name(sqlfrag.object_id) AS 'TableName', 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',
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. 

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…