August 22, 2010 at 12:53 am
Hi, i read many topics/articles about DISABLING/REBUILDING indexes but couldn't get it.
can anybody explain any difference between "create" and "disable/rebuild" index ?
additionally where we can use it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 22, 2010 at 10:27 am
Create, as you might guess, creates a completely new index. If there's an existing index with the same name, you get an error.
Disable drops the b-tree structure, just leaving the meta-data behind. When you rebuild it you're recreating the index completely.
Disable/rebuild is almost the same as drop/create, except that you don't need to know the index's definition to recreate it. It's used anywhere wher you would want to drop an index, do something then recreate the index.
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
August 22, 2010 at 10:27 am
This is really useful when bulk loading data into a DW. When you bulk load the data, you can DISABLE all of the non clustered indexes, load the data, then perform a rebuild all and all indexes will be rebuilt.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 22, 2010 at 10:37 am
Jeffrey Williams-493691 (8/22/2010)
This is really useful when bulk loading data into a DW. When you bulk load the data, you can DISABLE all of the non clustered indexes, load the data, then perform a rebuild all and all indexes will be rebuilt.
And you don't have to worry whether or not the index definitions have changed since you created the load job, as you would if you used drop/create.
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
August 22, 2010 at 6:01 pm
Disabling the indexes and then rebuilding them also means you don't need extra space in your data file. For example, a 5 GB non-clustered index normally requires an extra 5 GB of space in the data file to rebuild it because it doesn't drop the index until the rebuild is complete. By dropping the index first (via DISABLE), you get 5 GB of space back, which you can then use to rebuild.
While you can use this technique to avoid your database size from getting too large, I still recommend having the extra space in the data file for this sort of maintenance.
August 22, 2010 at 9:53 pm
Jim McLeod (8/22/2010)
a 5 GB non-clustered index normally requires an extra 5 GB of space in the data file to rebuild it because it doesn't drop the index until the rebuild is complete.
which index we need to drop ? and why ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 22, 2010 at 10:41 pm
Bhuvnesh (8/22/2010)
Jim McLeod (8/22/2010)
a 5 GB non-clustered index normally requires an extra 5 GB of space in the data file to rebuild it because it doesn't drop the index until the rebuild is complete.which index we need to drop ? and why ?
"It" being the rebuild process. It doesn't drop the existing index structure that is being rebuilt. So, if you're rebuilding index A, a completely new, separate, copy of index A will be created. Once this is fully complete, the old structure will be removed, and the new one will be kept and made active. This is all done automatically.
Perhaps a better phrase instead of "drop" would be that it releases the pages used by the old index structure. The index definition is not dropped.
So, you have the old structure (5 GB), and the new structure (5 GB), for a total of 10 GB required. As soon as the new structure is in place, the old structure is dropped, bringing your size back to 5 GB. (This value of 5 GB for the rebuilt index is for simplicity - it could easy be smaller due to defragmentation, or larger, due to a fill factor.)
August 22, 2010 at 10:49 pm
Jim McLeod (8/22/2010)
Bhuvnesh (8/22/2010)
Jim McLeod (8/22/2010)
a 5 GB non-clustered index normally requires an extra 5 GB of space in the data file to rebuild it because it doesn't drop the index until the rebuild is complete.which index we need to drop ? and why ?
"It" being the rebuild process. It doesn't drop the existing index structure that is being rebuilt. So, if you're rebuilding index A, a completely new, separate, copy of index A will be created. Once this is fully complete, the old structure will be removed, and the new one will be kept and made active. This is all done automatically.
Perhaps a better phrase instead of "drop" would be that it releases the pages used by the old index structure. The index definition is not dropped.
So, you have the old structure (5 GB), and the new structure (5 GB), for a total of 10 GB required. As soon as the new structure is in place, the old structure is dropped, bringing your size back to 5 GB. (This value of 5 GB for the rebuilt index is for simplicity - it could easy be smaller due to defragmentation, or larger, due to a fill factor.)
Thanks got it , so it means: unnecessarily wastage of space jus to retain the meta data of index?.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 25, 2010 at 5:55 am
Bhuvnesh (8/22/2010)
Jim McLeod (8/22/2010)
a 5 GB non-clustered index normally requires an extra 5 GB of space in the data file to rebuild it because it doesn't drop the index until the rebuild is complete.which index we need to drop ? and why ?
If, for example, you're using this approach in what's problably the commonest scenario, when loading staging tables in an ETL process, you'd commonly drop / disable all non-clustered indexes before starting the load, then create/enable rebuild the indexes once the load is complete. The benefits to this are twofold, 1)the overall load time will tend to be reduced as SQL Server is not having to maintain the indexes on the fly during the load process as each new record/batch is inserted
2) Rebuilding the indexes once the load is complete will tend to significantly reduce fragmentation, providing benefits further downstream for the transformations using these indexes.
As Gail has pointed out, the use of disable ... rebuild is more maintainable than dropping and recreating. With the latter, if you amend or create a covering index, you must then also remember to amend the code in the drop/recreate sections(s). If you use disable, this is 'handled for you'
August 25, 2010 at 6:07 am
Thanks to all
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply