February 28, 2012 at 6:45 am
How do we know table is fragmented? Please explain in details.
February 28, 2012 at 6:47 am
February 28, 2012 at 12:03 pm
hemant789 (2/28/2012)
How do we know table is fragmented? Please explain in details.
Below is a example to find the fragmentation of any table.
USE AdventureWorks
GO
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), OBJECT_ID('HumanResources.Employee'), NULL, NULL, NULL);
February 28, 2012 at 2:32 pm
Here is a script that i created a while ago to list out the table fragmentation of the whole DB, run it against your db. yer it runs through a cursor but it did the job 🙂
/*
Ref: Incident [001861]
Name: TABLE_FRAGMENTATION_STATS.sql
Version: 1.0
Author: Glen Wass
Date Created : 19/10/2011
Description: script to identify table fragmentation
Impact:
Example exec:
:
Version History: DateVersionAuthor
Comments
---------------------------------------------------------------------------
19/10/2011|1.0| GW | Initial release
15/11/2011| 1.1| GW| cast added to average % fragmentation
*/
declare @obj_name varchar(50),
@id int,
@db int
set @db=db_ID()
declare @fg_results table (OBJ_name varchar(50),
[OBJECT_ID] [int] NULL,
[index_id] [int] NULL,INDEX_NAME nvarchar (255),
[avg_fragmentation_in_percent] [float] NULL,
[page_count] [bigint] NULL ,
[origfillfactor] [tinyint] null
)
declare frag cursor read_only
for
select name,id from sysobjects
where type='u'
and name not like 'zz%'
order by 1 desc
open frag
fetch next from frag into @obj_name,@id
while @@fetch_status=0
begin
insert into @fg_results
select @obj_name,a.OBJECT_ID,a.index_id,name,cast(avg_fragmentation_in_percent as int), page_count,b.fill_factor
from sys.dm_db_index_physical_stats(@db,@id, NULL, NULL , null) as a
inner join sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where a.object_id=@id
and b.object_id=@id
fetch next from frag into @obj_name,@id
end
close frag
deallocate frag
/*
avg_fragmentation_in_percent value |Corrective statement| Command to run
> 5% and < = 30% ALTER INDEX REORGANIZE alter index index name on dbo.tablename reorganize
> 30%ALTER INDEX REBUILD WITH (ONLINE = ON)* alter index index name on dbo.tablename rebuild
*/
--identify indexes that need re organizing
select * from @fg_results
where avg_fragmentation_in_percent between 5 and 30
select * from @fg_results
where avg_fragmentation_in_percent > 30
***The first step is always the hardest *******
February 28, 2012 at 2:45 pm
Why would you need a cursor for that? If you omit the ObjectID parameter from the index_physical_stats, it runs for the entire DB anyway.
This is equivalent to the innards of your cursor:
insert into @fg_results
select OBJECT_NAME(a.object_id),a.OBJECT_ID,a.index_id,name,cast(avg_fragmentation_in_percent as int), page_count,b.fill_factor
from sys.dm_db_index_physical_stats(@db,NULL, NULL, NULL , null) as a
inner join sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where OBJECT_NAME(a.object_id) NOT LIKE 'zz%' AND ObjectProperty(a.object_id,'IsUserTable') = 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply