SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Index fragmentation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 03/08/2013 :  05:04:27  Show Profile  Reply with Quote
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

India
649 Posts

Posted - 03/08/2013 :  05:50:45  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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 - 03/08/2013 :  06:18:55  Show Profile  Reply with Quote
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

India
649 Posts

Posted - 03/08/2013 :  06:26:09  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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 - 03/08/2013 :  06:34:10  Show Profile  Reply with Quote
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

India
649 Posts

Posted - 03/08/2013 :  06:35:55  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

India
649 Posts

Posted - 03/08/2013 :  06:38:39  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000