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.
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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...
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -