Index fragemention Script for Azure SQL DB

Question

Hi Team,

I am looking for Index fragmentation T-SQL script for Azure SQL DB and which just generate Alter index build/reorganize based on given %.

Please help me.

i am using below script. but it is not generating ALTER statements:
---TO Check Index Fragmentation----
SELECT DB_NAME(ips.database_id) AS DatabaseName,
SCHEMA_NAME(ob.[schema_id]) SchemaNames,
ob.[name] AS ObjectName,
ix.[name] AS IndexName,
ob.type_desc AS ObjectType,
ix.type_desc AS IndexType,
-- ips.partition_number AS PartitionNumber,
ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
ips.record_count AS [RecordCount],
ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]
AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
ORDER BY DatabaseName

Answer 1

Hi，@Vijay Kumar

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

CREATE TABLE #IDXFRAG  
    (   DbName                          sysname,  
        ObjName                         sysname,  
        IdxName                         sysname     NULL,  
    -- columns below are exactly as generated by SYS.DM_DB_INDEX_PHYSICAL_STATS function  
        database_id                     smallint,  
        [object_id]                     int,  
        index_id                        int,  
        partition_number                int,  
        index_type_desc                 nvarchar(60) COLLATE Latin1_General_CI_AI NULL,  
        alloc_unit_type_desc            nvarchar(60) COLLATE Latin1_General_CI_AI NULL,  
        index_depth                     tinyint NULL,  
        index_level                     tinyint NULL,  
        avg_fragmentation_in_percent    float NULL,  
        fragment_count                  bigint NULL,  
        avg_fragment_size_in_pages      float NULL,  
        page_count                      bigint NULL,  
        avg_page_space_used_in_percent  float NULL,  
        record_count                    bigint NULL,  
        ghost_record_count              bigint NULL,  
        version_ghost_record_count      bigint NULL,  
        min_record_size_in_bytes        int NULL,  
        max_record_size_in_bytes        int NULL,  
        avg_record_size_in_bytes        float NULL,  
        forwarded_record_count          bigint NULL  
   -- ,   primary key nonclustered (DbName, ObjName,[object_id],index_id,partition_number)   -- IdxName is NULL if HEAP (index_id=0)  
    )  
GO  
    
IF NOT EXISTS (SELECT * FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IDXFRAG') AND name = N'IDXFRAG_CI')  
    create index IDXFRAG_CI on #IDXFRAG (DbName,ObjName,IdxName)  
    
-- tip: you can re-run in SSMS for different db's by   
declare tblcur cursor for  
    select  object_id, TblName=schema_name(schema_id)+'.'+name  
    from    sys.tables where type = 'U'  
    order by TblName   
    
declare @dbid smallint, @objid int, @TblName nvarchar(257)  
select @dbid=db_id()    --, @objid=OBJECT_ID('SEDOL_PRICE')  
--select @dbid, @objid  
open tblcur  
fetch next from tblcur into @objid, @TblName  
while @@fetch_status=0  
 begin  
 -- print @TblName        -- DEBUG only  
    delete from #IDXFRAG where DbName=db_name() and [object_id]=@objid      -- allow re-runs and multiple db's  
    insert into #IDXFRAG  
        select  DbName=db_name(), ObjName=@TblName, IdxName=I.name,  
                database_id,s.[object_id],s.index_id, partition_number,  
                index_type_desc,alloc_unit_type_desc,  
                index_depth,index_level,  
                avg_fragmentation_in_percent,fragment_count,  
                avg_fragment_size_in_pages,page_count,  
                avg_page_space_used_in_percent,record_count,  
                ghost_record_count, version_ghost_record_count,  
                min_record_size_in_bytes,max_record_size_in_bytes,  
                avg_record_size_in_bytes,forwarded_record_count   
        from    SYS.DM_DB_INDEX_PHYSICAL_STATS(@dbid,@objid,NULL,NULL,'SAMPLED')    S  
        join    sys.indexes                                                         I on I.object_id= @objid and I.index_id=S.index_id   
        --where   S.avg_fragmentation_in_percent > 20 AND S.page_count > 8   
              
    fetch next from tblcur into @objid, @TblName  
 end  
close tblcur  
deallocate tblcur  
go  
    
select  DbName, ObjName,IdxName, index_id, partition_number,index_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count, forwarded_record_count    --, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes  
from    #IDXFRAG  
order by DbName,ObjName,IdxName    -- IDXFRAG_CI  
go  

After you execute the statement, you can view the index fragmentation percentage of all tables in the current database.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Go to http://ola.hallengren.com to get the standard solution.

Answer 3

You can use the below script to find fragmentation % and then based on a range can rebuild the index

This can be a part of Index Rebuild Stored Procedure:

SELECT
OBJECT_SCHEMA_NAME(ips.OBJECT_ID) 'Schema',
OBJECT_NAME(ips.OBJECT_ID) 'Table',
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
)
ORDER BY
avg_fragmentation_in_percent DESC

Post filtering the indexes which you want to rebuild you can run the below query:

ALTER INDEX <Index_Name> ON <Table_Name> REBUILD WITH (ONLINE = ON)