Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Page History: Index Optimization - SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Current Revision


Page Revision: Wed, Feb 22, 2023, 8:35 AM


Reference

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

Query

SELECT 
     SchemaName = OBJECT_SCHEMA_NAME(ips.object_id)
    ,ObjectName = OBJECT_NAME(ips.object_id)
    ,IndexName = i.name
    ,IndexType = i.type_desc
    ,ips.avg_fragmentation_in_percent
    ,ips.avg_page_space_used_in_percent
    ,ips.page_count
    ,ips.alloc_unit_type_desc
from       
    sys.dm_db_index_physical_stats(DB_ID(),default,default,default,default) ips
    INNER JOIN sys.indexes AS i 
        ON ips.object_id = i.object_id
        AND ips.index_id = i.index_id
where 1=1
    and ips.object_id = object_id('dbo.MyTable') -- TODO
ORDER BY 
    page_count DESC;

Optimizations

  • Update statistics on a table: UPDATE STATISTICS dbo.MyTable
  • Update statistics on a database: EXEC sp_updatestats; (UCSF = 43 seconds)
  • Reorganize an index: ALTER INDEX IndexName ON dbo.MyTable REORGANIZE
  • Reorganize all indexes on a table: ALTER INDEX ALL ON dbo.MyTable REORGANIZE
  • Rebuild an index: ALTER INDEX IndexName ON dbo.MyTable REBUILD
  • Rebuild all indexes on a table: ALTER INDEX ALL on dbo.MyTable REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.