Enable Disable Index

USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO

You can Download Disable Enable script from here..... USPDisableEnableNonClusteredIndexes

In SQL Server 2008, all user-created databases are always full-text enabled and cannot be disabled.

Dropping an Index behavior

On Drop of Clustered Indexes

When a clustered index is dropped, the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can take time because in addition to dropping the clustered index, all nonclustered indexes on the table must be rebuilt to replace the clustered index keys with row pointers to the heap. When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last. That way, no indexes have to be rebuilt.

On Dropping Clustered Indexes Online

The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. For example, while a clustered index is being rebuilt by one user, that user and others can continue to update and query the underlying data. When you perform DDL operations offline, such as building or rebuilding a clustered index; these operations hold exclusive locks on the underlying data and associated indexes. This prevents modifications and queries to the underlying data until the index operation is complete.

Full-Text Indexes

An index specified as the full-text key for the table cannot be dropped

Dropping Index Refernce :: http://msdn.microsoft.com/en-us/library/ms190691.aspx

How does storage change?

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data.

Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

In general, rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

Reorganizing an Index

Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.

Large Object Data Type Compaction

Besides reorganizing one or more indexes, large object data types (LOBs) that are contained in the clustered index or underlying table are compacted by default when an index is reorganized. The data types image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml are large object data types. Compacting this data can cause better disk space use:

Reorganizing a specified clustered index will compact all LOB columns that are contained in the leaf level (data rows) of the clustered index.

Reorganizing a nonclustered index will compact all LOB columns that are nonkey (included) columns in the index.

When ALL is specified, all indexes associated with the specified table or view are reorganized and all LOB columns associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.

The LOB_COMPACTION clause is ignored if LOB columns are not present.

Rebuilding an Index

Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.

Disabling Nonclustered Indexes to Conserve Disk Space During Rebuild Operations

When a nonclustered index is disabled, the index data rows are deleted, but the index definition remains in metadata. The index is enabled when it is rebuilt. When the nonclustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index. However, by disabling and rebuilding a nonclustered index in separate transactions, the disk space made available by disabling the index can be reused by the subsequent rebuild or any other operation. No additional space is required except for temporary disk space for sorting, which is typically 20 percent of the index size. If the nonclustered index is on the primary key, any active, referencing FOREIGN KEY constraints will automatically be disabled. These constraints must be manually enabled after the index is rebuilt.

Rebuilding Large Indexes

Indexes with more than 128 extents are rebuilt in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted and then moved to new allocation units created to the store the rebuilt index. In the physical phase, the allocation units previously marked for deallocation are physically dropped in short transactions that happen in the background, and do not require many locks.

Fill Factor

The fill factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill factor value determines the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of free space for future growth.

Thanks

Yogesh

----Diable Index

----(1) Disabling Index Using T-SQL
USE AdventureWorks 
GO 
ALTER INDEX [IX_Address_StateProvinceID] ON Person.Address DISABLE 
GO

----(2) Disabling Index Using SQL Server Management Studio (SSMS)

----Enable Index 

----(1) Enable Index Using ALTER INDEX REBUILD Statement
ALTER INDEX [IX_Address_StateProvinceID] ON Person.Address REBUILD
GO

----(2) Enable Index Using CREATE INDEX WITH DROP_EXISTING Statement
USE AdventureWorks 
GO 
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID] ON [Person].[Address]  
( 
[StateProvinceID] ASC 
)WITH (DROP_EXISTING = ON, FILLFACTOR = 80) ON [PRIMARY] 
GO

----(3) Enable Index Using SQL Server Management Studio (SSMS)

Drop Index

You cannot drop an index used by either a PRIMARY KEY or UNIQUE constraint, except by dropping the constraint. By modifying the index, for example, to modify the fill factor value used by the index, you can essentially drop and re-create an index used by a PRIMARY KEY or UNIQUE constraint without having to drop and re-create the constraint.

DROP INDEX
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

When a nonclustered index is dropped, the index definition is removed from metadata and the index data pages (the B-tree) are removed from the database files.

When a clustered index is dropped, the index definition is removed from metadata and the data rows that were stored in the leaf level of the clustered index are stored in the resulting unordered table, a heap.

All the space previously occupied by the index is regained. This space can then be used for any database object.

An index cannot be dropped if the filegroup in which it is located is offline or set to read-only.

When the clustered index of an indexed view is dropped, all nonclustered indexes and auto-created statistics on the same view are automatically dropped. Manually created statistics are not dropped.

The syntaxtableorviewname.indexname is maintained for backward compatibility. An XML index or spatial index cannot be dropped by using the backward compatible syntax.

When indexes with 128 extents or more are dropped, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. For more information, see Dropping and Rebuilding Large Objects.

Sometimes indexes are dropped and re-created to reorganize or rebuild the index, such as to apply a new fill factor value or to reorganize data after a bulk load. To do this, using ALTER INDEXis more efficient, especially for clustered indexes. ALTER INDEX REBUILD has optimizations to prevent the overhead of rebuilding the nonclustered indexes.

When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last. That way, no indexes have to be rebuilt.

Using the MOVE TO Clause

If MOVE TO is not specified, the resulting table will be located in the same partition scheme or filegroup as was defined for the clustered index.

Dropping Clustered Indexes Online

You can specify the ONLINE option when you drop a clustered index. When set to ON, queries and modifications to the underlying data and associated nonclustered indexes are not blocked by the DROP INDEX transaction

You can drop the clustered index and move the resulting unordered table (heap) to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option. The MOVE TO option has the following restrictions:

• It is not valid for indexed views or nonclustered indexes.
• The specified partition scheme or filegroup must already exist.

Setting the MAXDOP Index Option

You can override the max degree of parallelism configuration option of sp_configure for the drop index operation by specifying the MAXDOP index option.

Full-Text Indexes

An index specified as the full-text key for the table cannot be dropped. View index properties to determine whether the index is a full-text key.

Disk Space Requirements for Index DDL Operations

when you create, rebuild, or drop indexes. Inadequate disk space can degrade performance or even cause the index operation to fail

Index Operations That Require No Additional Disk Space

The following index operations require no additional disk space:

ALTER INDEX REORGANIZE; however, log space is required.

DROP INDEX when you are dropping a nonclustered index.

DROP INDEX when you are dropping a clustered index offline without specifying the MOVE TO clause and nonclustered indexes do not exist.

CREATE TABLE (PRIMARY KEY or UNIQUE constraints)

Index Operations That Require Additional Disk Space

All other index DDL operations require additional temporary disk space to use during the operation, and permanent disk space to store the new index structure or structures.

When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.

The following index DDL operations create new index structures and require additional disk space:

CREATE INDEX

CREATE INDEX WITH DROP_EXISTING

ALTER INDEX REBUILD

ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE)

ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE) when the constraint is based on a clustered index

DROP INDEX MOVE TO (Applies only to clustered indexes.)

Temporary Disk Space for Sorting

If sorting is required, sorting occurs one new index at a time. For example, when you rebuild a clustered index and associated nonclustered indexes within a single statement, the indexes are sorted one after the other. Therefore, the additional temporary disk space that is required for sorting only has to be as large as the largest index in the operation. This is almost always the clustered index.

If the SORTINTEMPDB option is set to ON, the largest index must fit into tempdb. Although this option increases the amount of temporary disk space that is used to create an index, it may reduce the time that is required to create an index when tempdb is on a set of disks different from the user database. For more information about sorting in tempdb, see tempdb and Index Creation.

If SORTINTEMPDB is set to OFF (the default) each index, including partitioned indexes, is sorted in its destination disk space; and only the disk space for the new index structures is required.

Temporary Disk Space for Online Index Operations

If a clustered index is created, rebuilt, or dropped online, a temporary nonclustered index is created to map old bookmarks to new bookmarks. If the SORTINTEMPDB option is set to ON, this temporary index is created in tempdb. If SORTINTEMPDB is set to OFF, the same filegroup or partition scheme as the target index is used. The temporary mapping index contains one record for each row in the table, and its contents is the union of the old and new bookmark columns, including uniqueifiers and record identifiers and including only a single copy of any column used in both bookmarks.

http://msdn.microsoft.com/en-us/library/ms179542.aspx

http://www.mssqltips.com/tipprint.asp?tip=1788

http://msdn.microsoft.com/en-us/library/ms190691.aspx

So, how do you enable and disable an Index? What is the behavior of the same when compared to dropping an Index? How does storage change?

In ideal situation when capacity planning is done,Index will be residing in a separate LUN with sufficient space for growth.

Disabling an index prevents user access to the index, and for clustered indexes, to the underlying table data. The index definition remains in metadata and index statistics are kept on nonclustered indexes. Disabling a nonclustered index or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt. To rebuild and enable a disabled index, use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement.

Disabling the index wont gain space,however drop operation does gain space.

Index Enable / Disable

When ever an index is created, it is Enabled by default.

To Disable an index we use;

ALTER INDEX <IndexName> on <schema>.<tableName> DISABLE

To Enable an index we use;

ALTER INDEX <IndexName> on <schema>.<tableName> REBUILD

Dropping Indexes

When a nonclustered index is dropped, the index definition is removed from metadata and the index data pages (the B-tree) are removed from the database files. When a clustered index is dropped, the index definition is removed from metadata and the data rows that were stored in the leaf level of the clustered index are stored in the resulting unordered table, a heap. All the space previously occupied by the index is regained. This space can then be used for any database object.

When the clustered index of an indexed view is dropped, all nonclustered indexes and auto-created statistics on the same view are automatically dropped. Manually created statistics are not dropped.

The syntaxtableorviewname.indexname is maintained for backward compatibility. An XML index or spatial index cannot be dropped by using the backward compatible syntax.

An index cannot be dropped if the filegroup in which it is located is offline or set to read-only.

You use the DROP INDEX Transact-SQL DDL statement to Drop XML Indexes. If you drop the primary XML index, any secondary indexes that are present are also dropped. In Figure 8, we will drop the secondary index.

Large Objects

When you drop or rebuild large indexes, or drop or truncate large tables, the SQL Server 2005 Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both autocommit and explicit transactions in a multiuser environment, and applies to large tables and indexes that use more than 128 extents.

The Database Engine avoids the allocation locks that are required to drop large objects by splitting the process in two separate phases: logical and physical.

In the logical phase, the existing allocation units used by the table or index are marked for deallocation and locked until the transaction commits. With a clustered index that is dropped, the data rows are copied and then moved to new allocation units created to the store either a rebuilt clustered index, or a heap. (In the case of an index rebuild, the data rows are sorted also.) When there is a rollback, only this logical phase needs to be rolled back.

The physical phase occurs after the transaction commits. The allocation units marked for deallocation are physically dropped in batches. These drops are handled inside short transactions that occur in the background, and do not require lots of locks.

Deferred drop operations do not release allocated space immediately, and they introduce additional overhead costs in the Database Engine. Therefore, tables and indexes that use 128 or fewer extents are dropped, truncated, and rebuilt just like in SQL Server 2000. This means both the logical and physical phases occur before the transaction commits.

Storage Considerations

Whenever an index is created, rebuilt, or dropped, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits. Additional temporary disk space for sorting operations may also be needed. Different Online and Offline calculations are listed here

Transaction Log Disk Space for Index Operations is also in the topic. Large-scale index operations can generate large data loads that can cause the transaction log to fill quickly. To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has completed; however, the log can be backed up during the index operation. Therefore, the transaction log must have sufficient room to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. This is true for both offline and online index operations. Because the underlying tables cannot be accessed during an offline index operation, there may be few user transactions and the log may not grow as quickly. Online index operations do not prevent concurrent user activity, therefore, large-scale online index operations combined with significant concurrent user transactions can cause continuous growth of the transaction log without an option to truncate the log.

ONLINE = ON and MOVE TO

Using the ONLINE = ON and MOVE TO options requires additional temporary disk space

how do you enable and disable an Index?

SQL Server indexes are useful objects, greatly decreasing the time needed to find and return data in database tables. In real world, in order to speed the import of our large amount of data we want to disable all non-clustered indexes. The easiest way of doing this is to disable the clustered index since whenever we disable a clustered index, it disables all the non-clustered indexes on the table as well. An interesting side-effect that saves us the time of disabling each non-clustered index individually but presents us with a serious problem: once a clustered index is disabled, users can not access the underlying table data.

--Disable Index

ALTER INDEX indexname ON databasename.schemaname.tablename DISABLE

--Enable Index

ALTER INDEX indexname ON databasename.schemanamee.tablename REBUILD

Disabling a non clustered index leaves the current index on disk but marks it so that it is no longer maintained (no savings in disk space, savings from not maintaining it as rows change)

Disabling a clustered index makes the table unavailable

You cannot disable a primary key (which is enforced using an index) if the

table is published as part of a transactional replication publication

If you disable an index on an indexed view the index data is deleted

(different than for standard clustered/non clustered as above)

To enable an index we have to rebuild it (or check the 'use index' box on

the index options table, which also triggers a rebuild)

So why use it?

1. A service pack may disable an index and warn you so that you can rebuild when it's complete it it cannot guarantee the data is valid
2. Work around a disk IO error on an index page
3. Removing the index because you think you no longer need it
4. Rebuilding an index when space is short because this way we don't keep the original copy on disk, freeing up that space

Of those the only one I think we might find interesting on a day to day basis is to disable an index for troubleshooting.

What is the behavior of the size when compared to dropping an Index?

Disabling an index prevents user access to the index, and for clustered indexes, to the underlying table data. The index definition remains in the system catalog. Disabling a nonclustered index or clustered index on a view physically deletes the index data. Disabling a clustered index prevents access to the data, but the data remains unmaintained in the B-tree until the index is dropped or rebuilt.

When a nonclustered index is disabled, the index data rows are deleted, but the index definition remains in metadata. The index is enabled when it is rebuilt. When the nonclustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index. However, by disabling and rebuilding a nonclustered index in separate transactions, the disk space made available by disabling the index can be reused by the subsequent rebuild or any other operation. No additional space is required except for temporary disk space for sorting, which is typically 20 percent of the index size. If the nonclustered index is on the primary key, any active, referencing FOREIGN KEY constraints will automatically be disabled. These constraints must be manually enabled after the index is rebuilt.

How does storage change?

Follow these steps to estimate the amount of space that is required to store a nonclustered index:

1. Calculate variables for use in steps 2 and 3.
2. Calculate the space used to store index information in the leaf level of the nonclustered index.
3. Calculate the space used to store index information in the non-leaf levels of the nonclustered index.
4. Total the calculated values.

Size od Disabled Index

Eg:

Create Table T1 (col1 int identity(1,1), Col2 Varchar(5000))

Create Clustered index IX On T1(col1)
Create index INX On T1(Col2)
Insert into T1 Select 'Latheesh'
Go 500

Alter index INX on T1 disable

SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('T1'),NULL,NULL,'DETAILED')

There would not be any row for the above query. It shows that B-Tree structure is no more maintanined once the index is disabled.

Enable And Disable Index

---Disabling Index Using T-SQL

---Disable Index
USE AdventureWorks
GO
ALTER INDEX [IX_Address_StateProvinceID] ON Person.Address DISABLE
GO

----Enable Index
----(1) Enable Index Using ALTER INDEX REBUILD Statement
ALTER INDEX [IX_Address_StateProvinceID] ON Person.Address REBUILD
GO

----(2) Enable Index Using CREATE INDEX WITH DROP_EXISTING Statement
USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID] ON [Person].[Address] 
(
[StateProvinceID] ASC
)WITH (DROP_EXISTING = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

----Enable and Disable Index Using SQL Server Management Studio (SSMS)

What is the behavior of the same when compared to dropping an Index?

Disabling an index removes the b-tree and leaves the metadata. Dropping it removes the b-tree and removes the metadata.

How does storage change?

When a nonclustered index is dropped

When a nonclustered index is dropped, the index definition is removed from metadata and the index data pages (the B-tree) are removed from the database files.

When a clustered index is dropped

When a clustered index is dropped,the index definition is removed from metadata and the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can take time because in addition to dropping the clustered index, all nonclustered indexes on the table must be rebuilt to replace the clustered index keys with row pointers to the heap. When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last. That way, no indexes have to be rebuilt. All the space previously occupied by the index is regained. This space can then be used for any database object.

When a clustered index is dropped Online

Temporary disk space is required to drop a clustered index during an online drop operation, or when the MOVE TO clause is specified.

When the clustered index of an indexed view is dropped, all nonclustered indexes and auto-created statistics on the same view are automatically dropped. Manually created statistics are not dropped.

Full-Text Indexes

An index specified as the full-text key for the table cannot be dropped. View index properties to determine whether the index is a full-text key.

http://msdn.microsoft.com/en-us/library/ms190691.aspx

http://www.mssqltips.com/sqlservertip/1788/disabling-indexes-in-sql-server-2005-and-sql-server-2008/

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109

http://msdn.microsoft.com/en-us/library/ms176118.aspx