The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
Issues which I faced in my organization is my company database contains more than 700 tables identifying fragmentation percentage of all table is time consuming process as for each table we need to used SYS. DM_DB_INDEX_PHYSICAL_STATS to avoid this generally written the below syntax to list all table Fragmentation percentage, This syntax can also be used to generate report periodically to identify the fragmentation percentage.
Once fragmentation percentage is identified then we need to plan for de-fragmentation according to the fragmentation level
-- be safe for sys.indexes although BOL says "sys.dm_db_index_physical_stats requires only an Intent-Shared (IS)"
SET
TRANSACTION
ISOLATION
LEVEL
READ
UNCOMMITTED
NOCOUNT
ON
ANSI_NULLS
QUOTED_IDENTIFIER
GO
-- if object_id('tempdb.dbo.#IDXFRAG') is not null drop TABLE #IDXFRAG
if object_id(
'tempdb.dbo.#IDXFRAG'
)
is
not
null
DROP
table
tempdb.dbo.#IDXFRAG
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
partition_number
index_type_desc nvarchar(60)
COLLATE
Latin1_General_CI_AI
alloc_unit_type_desc nvarchar(60)
index_depth tinyint
index_level tinyint
avg_fragmentation_in_percent
float
fragment_count
bigint
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
-- , primary key nonclustered (DbName, ObjName,[object_id],index_id,partition_number) -- IdxName is NULL if HEAP (index_id=0)
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
DbName=db_name(), ObjName=@TblName, IdxName=I.
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,
'SAMPLED'
) S
join
sys.indexes I
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
@objid, @TblName
end
close
deallocate
go
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
order
by
DbName,ObjName,IdxName
-- IDXFRAG_CI
See below gallery for more http://gallery.technet.microsoft.com/List-of-all-tables-with-b1586515#content