Tag: index

database_tables_indexes_fragmentation_compute_TSQL

This script compute tables indexes fragmentation to keep a view on.

-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: www.abcdata.ca
-- Create date	: 2018-03-28
-- Description	: Compute tables indexes fragments
-- =================================================

declare @db_id																				smallint;
declare @table_name_pattern														nvarchar	(250);
declare @frag_floor_percent														integer;
declare @record_count_min															integer;

set @db_id							= db_id	(N'my_db_name');
set @table_name_pattern = '%table_name_pattern%';
set @frag_floor_percent = 40;
set @record_count_min	  = 10000;

select				object_name	(a.object_id) as	TableName
						, c.name										as	IndexName
						,	a.Index_type_desc
						, a.avg_fragmentation_in_percent
						, a.avg_fragment_size_in_pages
						,	a.avg_page_space_used_in_percent
						, a.record_count
						,	a.ghost_record_count
						,	a.fragment_count
						,	a.avg_fragment_size_in_pages
from					sys.dm_db_index_physical_stats	(@db_id	, null , null , null , 'DETAILED') a
							inner	join	sys.tables b
							on					b.object_id				= a.object_id
							and					b.is_ms_shipped		= 0
							inner	join	sys.indexes c 
							on					c.object_id = a.object_id 
							and					c.index_id	= a.index_id
where					lower (left (object_name	(a.object_id) , 4)) like	@table_name_pattern
and						a.avg_fragmentation_in_percent								>			@frag_floor_percent
and						a.record_count																>			@record_count_min								
order	by			7 desc;	

Index with almost 50% of fragmentation need to reorganized to become relevant to be selected and used by query optimizer.