November 26, 2007 at 12:32 am
If the data of a table is changed by means of INSERTs only, i.e. no UPDATEs or DELETEs, will there ever be need for an index rebuild? More specifically, if the lifetime of a table is as follows:
1. Creation of table and indexes (or, equivalently(?), truncation)
2. Data insertion (INSERTs only)
will an index rebuild be just waste of time?
The application where the question has arisen is an SQL2K5 data warehouse where most tables are truncated and filled every night (only inserts, no updates/deletes). The current ETL jobs perform an index rebuild on each of these tables after data insertion is complete.
I suspect that this is not necessary, in that the rebuilt indexes should be identical to the ones built during data insertion (all indexes are in place and active before and during data insertion). Am I right that performing index rebuild on these tables is waste of time?
November 26, 2007 at 1:13 am
Am I right that performing index rebuild on these tables is waste of time?
It depends on the how your index(es) are defined. If your clustered index is on an identity field with ever increasing values, there will be no fragmentation for this index. But if you have any additional non-clustered indexes, these will still be fragemented and should be rebuild.
If you clustered index is on a field which isn't constantly increasing with every INSERT, when you might even consider to reindex the clustered index as well.
Markus
[font="Verdana"]Markus Bohse[/font]
November 26, 2007 at 1:27 am
The most critical tables are the large fact tables in the DWH. These have a clustered (primary key) index and additional non-clustered indexes on foreign keys. Based on your answer, then, the best solution should be to retain the index rebuild in the ETL process for these tables. Thanks for your time!
November 26, 2007 at 3:58 am
If you want to avoid unnecessary rebuilds, you can check the fragmentation of the table and decide based on that whether or not to rebuild.
SELECT * from sys.dm_exec_physical_stats([db_id], [schema_id], [table_id],[index_id],[mode])
It's not fast, unfortunately.
Edit: Forum ate my brackets, and everything in them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2007 at 4:52 am
Just As Gail already wrote you could first check on the fragmentation level using sys.dm_db_index_physical_stats but keep in mind that on very large tables this too can take a long time. I would advise to use the "Limited" mode instead of doing a full analysis.
Depending on the size of your ETL loads it might even be more efficient to drop the indexes before the load starts and then recreate them afterwards.
This article[/url] explains it bit more about when dropping indexes maybe more efficient.
Markus
[font="Verdana"]Markus Bohse[/font]
November 26, 2007 at 5:13 am
hi vebjornwa
Check theses links,
http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx
http://www.mssqltips.com/tip.asp?tip=1367
Regards,
Ahmed
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply