You can absolutely alter index when the corresponding table is being loaded.
Many times I trigger ETL processes without considering the indexes. Then after sometime I realise that the load which was meant to finish very quickly did not finish for a considerable amount of time. This usually happens for new development or test loads.
For production load, I disable indexes if I have to load huge data set into the tables. It is not always possible to disable an index on a production table if it is being used by multiple users when the loading is taking place. Disabling indexes will slow the performance for users who are querying the table.
To read how to enable/disable index, please read sql-server-enable-and-disable-index.html
Example:
CUSTOMER table has an index named IDX_USERID on USERID field.
When web user JOHN logs in , IDX_USERID is used by SQL server to quickly extract details of user JOHN. CUSTOMER table has tens of millions of customer records. Without the index it would take forever for SQL query to extract details for the required customers. Therefore index is very important for user experience as people do not like systems that have slow response.
If this system is a 24x7 available system for users, then its not possible to disable indexes on this table during loading. But usually most of the systems have a down time of atleast a few hours everyday. This downtime is used by support staff to quickly load the tables.
Hope this helped
Many times I trigger ETL processes without considering the indexes. Then after sometime I realise that the load which was meant to finish very quickly did not finish for a considerable amount of time. This usually happens for new development or test loads.
For production load, I disable indexes if I have to load huge data set into the tables. It is not always possible to disable an index on a production table if it is being used by multiple users when the loading is taking place. Disabling indexes will slow the performance for users who are querying the table.
To read how to enable/disable index, please read sql-server-enable-and-disable-index.html
Example:
CUSTOMER table has an index named IDX_USERID on USERID field.
When web user JOHN logs in , IDX_USERID is used by SQL server to quickly extract details of user JOHN. CUSTOMER table has tens of millions of customer records. Without the index it would take forever for SQL query to extract details for the required customers. Therefore index is very important for user experience as people do not like systems that have slow response.
If this system is a 24x7 available system for users, then its not possible to disable indexes on this table during loading. But usually most of the systems have a down time of atleast a few hours everyday. This downtime is used by support staff to quickly load the tables.
Hope this helped
No comments:
Post a Comment