need help optimizing code - 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 Programming

need help optimizing code

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 19:58:04

this code takes far too long to run any suggestions:

create table #minatest(
DatabaseName varchar(100),
pagecount varchar(100),
vfillfactor varchar(100),
TableName varchar(200),
IndexName varchar(100),
FragmentPercentage int,
newfragmentvalue int,
index_type_desc varchar(100),
index_level int
)

-- minatest table will contain indexes with fragmentation above 10% which need to be defragged
-- this will go through all databases
-- null indexes will not be affected

exec sp_msforeachdb'
use ?
INSERT INTO #minatest
SELECT
db_name(database_id),
phystat.page_count,
i.fill_factor,
OBJECT_NAME(i.object_id),
i.name,
phystat.avg_fragmentation_in_percent,
newfragmentvalue = 0,
index_type_desc,
index_level
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, ''DETAILED'') phystat
JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.page_count < 10000
'

DECLARE @Counts int,
@i int,
@DatabaseName varchar(100),
@pagecount varchar(100),
@vfillfactor varchar(100),
@TableName varchar(200),
@IndexName varchar(100),
@sql nvarchar(4000),
@nfsql nvarchar(4000),
@FragmentPercentage int,
@params nvarchar(4000),
@index_type_desc varchar(100),
@index_level int,
@vnewfrag int

SELECT @params = N'@cnt int OUTPUT'

select @Counts = count(Databasename)
from #minatest -- sets the maximum amount of fields to go threw as a number

declare targets cursor -- declare cursor with values to search through
for
select * from #minatest

open targets -- open cursor

fetch next from targets into @DatabaseName,@pagecount,@vfillfactor,@TableName,@IndexName,@FragmentPercentage,@vnewfrag,@index_type_desc,@index_level -- take rows from table

select @i=0
while @@fetch_status=0 and @i<=@Counts -- set loop condition

begin

select @sql = 'USE '+@DatabaseName+'; '+
' ALTER INDEX '+@IndexName+' ON '+ @TableName+
' REBUILD with (ONLINE=ON,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=OFF);'
exec sp_executesql @sql
select @nfsql = 'select @cnt = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(NULL,NULL, NULL, NULL, ''DETAILED'') phystat JOIN '+@DatabaseName+'.sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE i.name='''+@IndexName+''' and index_type_desc='''+@index_type_desc+''' and index_level='''+CAST(@index_level as varchar(20))+''''
exec sp_executesql @nfsql ,@params, @cnt=@vnewfrag OUTPUT

update #minatest set newfragmentvalue = @vnewfrag where IndexName = @IndexName and TableName = @TableName
select @i=@i+1

fetch next from targets into @DatabaseName,@pagecount,@vfillfactor,@TableName,@IndexName,@FragmentPercentage,@vnewfrag,@index_type_desc,@index_level -- take next field of table

end

close targets
DEALLOCATE targets

ALTER TABLE #minatest DROP COLUMN index_type_desc,index_level
select * from #minatest -- displays which indexes where defraged and their new frag value
drop table #minatest

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 01:59:48

"this code takes far too long to run"

How long?

How long would be acceptable?

You appear to be rebuilding everything without regard to the FragmentPercentage - is that intentional?

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-04 : 02:08:38

WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.page_count < 10000

i wouldve thought that did it, but please correct me if im wrong, it could take upto an hour on some databases and its mostly due to some approaches ive taken in that code

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 02:38:06

Missed that, sorry. I was looking at the WHERE clause in the CURSOR definition - Doh!

"it could take upto an hour on some databases"

I expect that cannot be beat. Its the physical time to do the reorganisation.

You could do different indexes on different days - e.g. "A-M" on Monday, "N-Z" on Tuesday etc.

I don't know about SQL 2005 but:

ONLINE=ON - I imagine this is slower than letting it just lock the table (assuming that you are doing this when no-one else is connected)

"REBUILD with" - if this is a REBUILD rather than a DEFRAG I would recommend that you do a DEFRAG. In SQL 2000 it is a much quicker operation (possibly except for the first time you do it - the house needs to be tidy at the outset!)

"STATISTICS_NORECOMPUTE=OFF" - Again, I don;t know about this, but my inclination is that the Statistics need rebuilding regularly and it may be faster to do it as part of the rebuild, rather than as a separate process. Worth testing that hypothesis I reckon.

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-04 : 23:48:15

thanx uve been greatly helpful!! i will try this

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-04 : 23:49:22

thanx uve been greatly helpful!! i will try this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources