Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Even though storage as a subsystem is cheap these days.We have seen DBA’s struggle to find Log file

  • Even though storage as a subsystem is cheap these days. We have seen DBA’s struggle to find Log files run out of space and stop transactions. Understanding size is a critical aspect, now tables data, index data, log data and more is becoming a competency of an effective DBA. 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?

    Posted on 01-21-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

6  Answers  

Subscribe to Notifications
  • Score
    9

    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

    Replied on Jan 21 2012 2:20AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    9

    ----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

    Replied on Jan 21 2012 10:47AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    9

    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.

    Replied on Jan 21 2012 6:42PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    9

    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

    Replied on Jan 27 2012 12:57AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    9

    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.

    Replied on Feb 2 2012 2:23AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    8

    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

    Replied on Feb 2 2012 7:43AM  . 
    Mitesh Modi
    18 · 10% · 3080

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.