I have a DB with about 17gb of data and max size at about 45gb. Inserts are done once every 24hours (import from clients' system). Indexes are disabled at the time of import.
I am seeing a performance issue between reorganizing and rebuilding versus dropping and rebuilding every index.
Reorg/rebuild takes about ~1-3mins to complete.
Dropping and rebuild takes 10+min to complete.
I have recently implemented the reorg procedure, previously we only had the drop and rebuild - taking 10mins for each client every night.
My problem is it seems the reorg is not enough to maintain performance and I have to run the full rebuild at time. I am no DBA, just a mere .NET dev - but it seems odd to me that so much of my performance hangs in indexes and them being rebuild and fresh.
I found the Reorganise index vs Rebuild Index in Sql Server Maintenance plan - that explains some of the concepts behind reorganizing vs rebuilding indexes.
My reorg/rebuild script can be found here http://www.sqlservercentral.com/Forums/Topic1010651-146-1.aspx#bm1010715 (posted by: pavan_srirangam) (I'm not sure thats the copy I found back in the days, but it looks like it. I've made some changes like logging start/stop). But if the script finds an index with a fragmentation higher than 30% it should rebuild it. As in dropping and rebuild the index from scratch or am I reading the elseif (avg frag > max frag) wrong.
One consideration could be that the existing index pages are current with the data before import happens. After the import, those pages can be reorganised and defragged pointing to the data before the import. Issue now comes - the import has generated a fair lot of new data. What is needed to make the reorg script handle this new data and generate index pages for the new data or is that already included in the reorg/rebuild script (concept of reorg indexes)?
EDIT UPDATE:
We just had a case of bad performance. I had timeout exceptions ticking in left and right. So I decided to run the reorg script, ended up doing so 4 times and ended at fragment_count = 10. I could not provoke any timeouts, my boss said he couldn't get in at all. Mind you I was on a VPN to the DB-server in question. Our test page was running at 15-20secs. In the end I had to run the full drop and rebuild, took 12mins. The test page went down to 3-5secs.
The drop and 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 AS TableName, idx.name AS IndexName
FROM sys.indexes AS idx
JOIN sys.objects AS obj
ON idx.object_id = obj.object_id
WHERE obj.type = 'U'
AND idx.name LIKE 'IX_%'
AND idx.is_primary_key = 0
AND idx.type = 2
ORDER BY obj.name, idx.nameOPEN cIndexes
FETCH NEXT FROM cIndexes INTO @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() AS varchar) + ' --- ' + @sql
EXECUTE sp_executesql @sql
SET @sql = NULL
FETCH NEXT FROM cIndexes INTO @tableName, @indexName
END
CLOSE cIndexes
DEALLOCATE cIndexes
What makes this so special compared to the reorg script.
(Note: Im trying to indent, but it doesn't work for me)
