sql server - Dropping and reindexing has a huge performance boost -


i have db 17gb of data , max size @ 45gb. inserts done once every 24hours (import clients' system). indexes disabled @ time of import.

i seeing performance issue between reorganizing , rebuilding versus dropping , rebuilding every index.

reorg/rebuild takes ~1-3mins complete.
dropping , rebuild takes 10+min complete.

i have implemented reorg procedure, had drop , rebuild - taking 10mins each client every night.

my problem seems reorg not enough maintain performance , have run full rebuild @ time. no dba, mere .net dev - seems odd me of performance hangs in indexes , them being rebuild , fresh.

i found reorganise index vs rebuild index in sql server maintenance plan - explains of concepts behind reorganizing vs rebuilding indexes.

my reorg/rebuild script can found here http://www.sqlservercentral.com/forums/topic1010651-146-1.aspx#bm1010715 (posted by: pavan_srirangam) (i'm not sure thats copy found in days, looks it. i've made changes logging start/stop). if script finds index fragmentation higher 30% should rebuild it. in dropping , rebuild index scratch or reading elseif (avg frag > max frag) wrong.

one consideration existing index pages current data before import happens. after import, pages can reorganised , defragged pointing data before import. issue comes - import has generated fair lot of new data. needed make reorg script handle new data , generate index pages new data or included in reorg/rebuild script (concept of reorg indexes)?

edit update:
had case of bad performance. had timeout exceptions ticking in left , right. decided run reorg script, ended doing 4 times , ended @ fragment_count = 10. not provoke timeouts, boss said couldn't in @ all. mind on vpn db-server in question. our test page running @ 15-20secs. in end had run full drop , rebuild, took 12mins. test page went down 3-5secs.

the drop , rebuild comes down to:
declare @sqltemplate varchar(50)
set @sqltemplate = 'alter index @indexname on @tablename @changemode'

declare cindexes cursor local forward_only read_only
for
select obj.name tablename, idx.name indexname
sys.indexes idx
join sys.objects obj
on idx.object_id = obj.object_id
obj.type = 'u'
, idx.name 'ix_%'
, idx.is_primary_key = 0
, idx.type = 2
order obj.name, idx.name

open cindexes
fetch next cindexes @tablename, @indexname
while @@fetch_status = 0
begin
set @sql = replace(@sqltemplate, '@tablename', @tablename)
set @sql = replace(@sql, '@indexname', @indexname)
set @sql = replace(@sql, '@changemode', @changemode)
print cast(getdate() varchar) + ' --- ' + @sql
execute sp_executesql @sql
set @sql = null
fetch next cindexes @tablename, @indexname
end
close cindexes
deallocate cindexes

what makes special compared reorg script.

(note: im trying indent, doesn't work me)


Comments

Popular posts from this blog

java - SNMP4J General Variable Binding Error -

windows - Python Service Installation - "Could not find PythonClass entry" -

Determine if a XmlNode is empty or null in C#? -