Script to find the Fragmentation in Indexes
In my previous articles, Fragmentation in SQL Server and Rebuild And Reorganization of Indexes , we talk in detail about Index fragmentation and rebuild or reorganize the indexes. Below is the SQL script which we can use to find the Fragmentation in Indexes created on the database.
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level, avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM sys.dm_db_index_physical_stats (DB_ID( N'Database name') , NULL, NULL, NULL , 'SAMPLED') ORDER BY avg_fragmentation_in_percent DESC
avg_fragmentation_in_percent represents logical fragmentation.
If this value is higher than 5% and less than 30%, then we should use
ALTER INDEXREORGANIZE
If this value is higher than 30%, then we should use
ALTER INDEX REBUILD WITH (ONLINE = ON)
Please note:- As per MSDN, rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.
Re-commentated Articles On Indexes
Rebuild And Reorganization of Indexes
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
OBJECT_NAME() will be scoped to the current database, which is master in your example, despite the OBJECT_ID being from another database, so that could return nothing or name of an object from the master database if one exists with the same OBJECT_ID. Also, online index rebuilds are only available in Enterprise (or Developer) edition, so many people can't use that feature.
Thanks For Correction. I have remove the Use Master command.:-)
script that works (subst function: do yourself).
What I do NOT understand: there are some tables I reorganize over and over again, but fragmentation stays high, up to 66 %.
alter procedure _adm_reorg_ix
as
/*
if @@error = 0
exec _adm_reorg_ix
*/
declare @c cursor
set @c = cursor for
select sql from ( — a
SELECT
tab_name= o.name,
ind_name = i.name,
avg_fragmentation_in_percent,
page_count,
sql = case
when avg_fragmentation_in_percent < 5 then
''
when avg_fragmentation_in_percent < 30 and page_count > 100 then
dbo.subst2('ALTER INDEX $1 on $2 reorganize',i.name,o.name) collate database_default
else
dbo.subst2('ALTER INDEX $1 on $2 rebuild WITH (ONLINE = OFF)',i.name,o.name) collate database_default
end,
s.index_id,
s.index_type_desc,
s.index_level,
avg_page_space_used_in_percent
FROM
master.sys.dm_db_index_physical_stats (DB_ID(db_name()) , NULL, NULL, NULL , 'SAMPLED') s
left join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
cross apply ( values (OBJECT_NAME(s.OBJECT_ID))) o (name)
–order by 3 desc
) a
where sql <> ''
declare @sql varchar(300)
open @c
while 0= 0 begin
fetch next from @c into @sql
if @@fetch_status <> 0 break
print @sql
exec (@sql)
end
go
… [Trackback]
[…] Information on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Read More on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] There you can find 71776 additional Info on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Here you will find 67584 more Info on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Find More to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Read More Info here on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Read More Information here to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Read More Info here to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Here you can find 21329 additional Info to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Find More to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Read More here to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Find More Information here to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Find More Information here on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Here you can find 74599 additional Information to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] There you can find 37039 more Information on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Read More here on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] There you can find 18452 additional Information on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Here you will find 89414 additional Information to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Here you can find 61033 more Info to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Find More on to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Information on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Read More Information here on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Find More Info here on that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2015/01/script-to-find-fragmentation-of-indexes.html […]