Index fragmentation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Index fragmentation

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-03-08 : 05:04:27

Hi all

I've got some code which checks a given database for all indexes and then lists those with >30% fragmentation.
The code is here:-


use SymposiumDW
go
SELECT
	object_name(IPS.object_id) AS [TableName],
	ips.object_id as [TableID],
	SI.name AS [IndexName], 
	IPS.Index_type_desc,
	ips.index_depth,
	IPS.avg_fragmentation_in_percent, 
	IPS.avg_fragment_size_in_pages, 
	IPS.avg_page_space_used_in_percent, 
	IPS.record_count, 
	IPS.ghost_record_count,
	IPS.fragment_count, 
	IPS.avg_fragment_size_in_pages
FROM
	sys.dm_db_index_physical_stats(db_id(N'[SymposiumDW]'), null, null, NULL , 'DETAILED') IPS
	JOIN sys.tables ST WITH (nolock)
		ON IPS.object_id = ST.object_id
	JOIN sys.indexes SI WITH (nolock)
		ON IPS.object_id = SI.object_id
		AND IPS.index_id = SI.index_id
where
	ST.is_ms_shipped = 0
	and IPS.avg_fragmentation_in_percent>=30 -- allow limited fragmentation
	and IPS.index_type_desc<>'heap'
	and IPS.index_level=0
	and object_name(IPS.object_id)<>'sysdiagrams'
ORDER BY
	IPS.avg_fragmentation_in_percent desc
	,object_name(IPS.object_id)

The problem is, it takes about 2 hours to run for each database and I'm trying to find a way to speed it up.

So, 2 questions:-
1) Can I speed it up and f so how?
2) Is there a way of checking all indexes rather than doing this one database at a time?

Any help greatly appreciated.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-08 : 05:50:45

Check this out
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

this will do exactly what you need.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-03-08 : 06:18:55

That's a very comprehensive list of things it does.
Does it overwrite the current data in each of the created tables (I can't tell through the code) to just give you the final output of each run?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-08 : 06:26:09

It doesnt change anything..it reads data from system tables and gives the output.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-03-08 : 06:34:10

Ah, so it just gives recommendations on which indexes (in this case) should be reorganised/rebuilt?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-08 : 06:35:55

as already mentioned based on the fragmentation levels it reorganizes/rebuilts the indexes....this will no way harm your data...

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-08 : 06:38:39

try this:

If exists (select * from tempdb.sys.all_objects where name like '#bbc%' )
drop table #bbc
create table #bbc
(DatabaseName varchar(100),ObjectName varchar(100),Index_id int, indexName varchar(100),avg_fragmentation_percent float,IndexType varchar(100),Action_Required varchar(100) default 'NA')
go
insert into #bbc (DatabaseName,ObjectName,Index_id, indexName,avg_fragmentation_percent,IndexType)

exec master.sys.sp_MSforeachdb ' USE

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent, index_type_desc

-- , record_count, avg_page_space_used_in_percent --(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'
go

update #bbc
set Action_Required ='Rebuild'
where avg_fragmentation_percent >30
go

update #bbc
set Action_Required ='Rorganize'
where avg_fragmentation_percent <30 and avg_fragmentation_percent >5
go

select * from #bbc where DatabaseName
not in('master','msdb','model','tempdb')
order by DatabaseName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources