2012: disable/enable(rebuild) index. Why are compression settings lost?

All replies

• 

  

  0

  Sign in to vote

  Hello Jakub,

  How are your rebuilding the index, with SSMS UI? If you start the rebuild, click on the "Create Script" button and have a look at the result:

  ALTER INDEX [IDX_MyTable_Index] ON [dbo].[MyTable] 
  REBUILD PARTITION = ALL WITH 
    (PAD_INDEX = OFF, 
     STATISTICS_NORECOMPUTE = OFF, 
     SORT_IN_TEMPDB = OFF, 
     IGNORE_DUP_KEY = OFF, 
     ONLINE = OFF, 
     ALLOW_ROW_LOCKS = ON, 
     ALLOW_PAGE_LOCKS = ON, 
     FILLFACTOR = 10)
  GO

  
  No DATA_COMPRESSION defined, so the index won't be compressed. See MSDN ALTER INDEX (Transact-SQL) => DATA_COMPRESSION

  ---

  Olaf Helper

  [ Blog] [ Xing] [ MVP]

  • Proposed as answer by Satheesh Variath Wednesday, January 22, 2014 5:55 AM

  Wednesday, January 22, 2014 5:38 AM
• 

  

  0

  Sign in to vote

  Hi Olaf,

  I've tried both via the UI and issuing a rebuild via tsql

  I know that by default the script function doesn't include the data compression (it needs to be set to true under options|object explorer|scripting), so I understand that.

  My question however is more to do with this situation:

  create table compression_test (id int primary key, buskey int, value1 varchar(10))
  go
  create index covering on compression_test (buskey) include (value1) with (data_compression=page)
  go
  

  
  

  I now have a table with a compressed nonclustered index which we can verify

  SELECT  object_name(a.object_id) tablename
  ,       b.name indexname
  ,       a.data_compression_desc
  FROM        sys.partitions a
  INNER JOIN  sys.indexes b 
  ON              b.object_id = a.object_id AND b.index_id = a.index_id
  WHERE   object_name(a.object_id) = 'compression_test'
  
  tablename        indexname                       data_compression_desc
  ---------------- ------------------------------- ---------------------
  compression_test PK__compress__3213E83F50076F25  NONE
  compression_test covering                        PAGE
  

  A common pattern when loading a data warehouse is to disable all nonclustered indexes prior to the load and then re-enable them after the load

  So I disable the index like so

  alter index covering on compression_test DISABLE

  The above query will now only show the primary key

  Now, if i rebuild this index using

  alter index covering on compression_test REBUILD

  and run the above query i get

  tablename         indexname                       data_compression_desc
  ----------------- ------------------------------- ---------------------
  compression_test  PK__compress__3213E83F50076F25  NONE
  compression_test  covering                        NONE
  

  prior to being disabled the index was compressed. after the rebuild it's uncompressed.

  I know that if i issue a

  alter index covering on compression_test REBUILD with (Data_compression=page)

  it'll be compressed again but that's not the point :)

  My question is why is this set option lost in the first place? And is there a list somewhere of which other index options are lost on disable/rebuild? Are partition settings also lost? - i haven't tested this today and don't remember if it is lost or not.

  What exactly is kept in a disabled index? just the column definitions?

  ETL frameworks work best when such things are automated. And seeing as data compression is especially useful in data warehouses it would be a useful option to be able to set against the tables and not have to worry about hardcoding in code
  

  ---

  Jakub @ Adelaide, Australia

  Wednesday, January 22, 2014 6:10 AM
• 

  

  0

  Sign in to vote

  Hello Jakubk,

  • Nonclustered indexes do not inherit the compression property of the table. To compress indexes, you must explicitly set the compression property of the indexes. By default, the compression setting for indexes will set to NONE when the index is created.

  • When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.

  Refer: MSDN

  Try the below:

  create table compression_test (id int , buskey int, value1 varchar(10))
  go
  create clustered index IX_covering on compression_test (id)with (data_compression=page)
  Go
  create index covering on compression_test (buskey) include (value1) with (data_compression=page)
  go
  
  
  SELECT  object_name(a.object_id) tablename
  ,       b.name indexname
  ,       a.data_compression_desc,b.is_disabled
  FROM        sys.partitions a
  INNER JOIN  sys.indexes b 
  ON              b.object_id = a.object_id AND b.index_id = a.index_id
  WHERE   object_name(a.object_id) = 'compression_test'
  
  alter index covering on compression_test DISABLE
  alter index IX_covering on compression_test DISABLE
  
  
  SELECT  object_name(a.object_id) tablename
  ,       b.name indexname
  ,       a.data_compression_desc,b.is_disabled
  FROM        sys.partitions a
  INNER JOIN  sys.indexes b 
  ON              b.object_id = a.object_id AND b.index_id = a.index_id
  WHERE   object_name(a.object_id) = 'compression_test'
  
  alter index IX_covering on compression_test REBUILD
  alter index covering on compression_test REBUILD
  
  
  SELECT  object_name(a.object_id) tablename
  ,       b.name indexname
  ,       a.data_compression_desc,b.is_disabled
  FROM        sys.partitions a
  INNER JOIN  sys.indexes b 
  ON              b.object_id = a.object_id AND b.index_id = a.index_id
  WHERE   object_name(a.object_id) = 'compression_test'
  
  Drop table Compression_test

  
  

  Wednesday, January 22, 2014 6:31 AM
• 

  

  0

  Sign in to vote

  Hi Latheesh,

  I'm not sure what you're trying to say. It's just the same tsql as I posted except it's a table heap...

  The MSDN link has nothing relating to rebuilding disabled indexes

  ---

  Jakub @ Adelaide, Australia

  Wednesday, January 22, 2014 11:47 AM
• 

  

  0

  Sign in to vote

  
  

  I'm not sure what you're trying to say. It's just the same tsql as I posted except it's a table heap...

  The MSDN link has nothing relating to rebuilding disabled indexes

  ---

  Jakub @ Adelaide, Australia

  Its not a HEAP table. I created a clustered index explicitly to show the difference. I was trying to explain, when you disable a non clustered, and then REBUILD the index, it will loose the compression whereas for clustered index it will persist the value. Please execute the script and see the difference. Now, if you look at the difference, even you disable the clustered index, the query to find the compression would return the compression type because it inherits the compression value where as for non-clustered, the compression value will be lost.

  For non clustered, you need to specify the COMPRESSION method explicitly when you do a DISABLE and REBUILD. 

  Wednesday, January 22, 2014 11:56 AM
• 

  

  0

  Sign in to vote

  Ok, so a disabled clustered index keeps it's compression status when rebuilt (not very useful as disabling the ci makes the table inacessible...). Since there's no answer as to why a nc index doesn't retain it's compression settings I'm guessing it's one of those "just because" scenarios. It's not explicitly stated in the doco.
  

  So that means that ETL processes that disable nc indexes when performing loads need to have the full index creation string hardcoded somewhere

  Do you know which other index settings are lost on nc index disable/rebuild? I just tested partitioning and that's retained thankfully.

  Edit:

  one other thing, rebuilding an enabled index RETAINS the data_compression setting, so this behaviour is inconsistent.

  ---

  Jakub @ Adelaide, Australia

  
  
  

  • Edited by jakubk Wednesday, January 22, 2014 11:59 PM

  Wednesday, January 22, 2014 11:17 PM
• 

  

  1

  Sign in to vote

  Below is the documentation (but according to it we should always lost compression).

  I guess that using following rules will be safe bet for disabled indexes.

  http://technet.microsoft.com/en-us/library/ms188388.aspx
  

  REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]

  Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

  
  

  
  

  • Marked as answer by jakubk Thursday, January 23, 2014 1:47 AM

  Thursday, January 23, 2014 1:08 AM
• 

  

  0

  Sign in to vote

  Interesting, thanks. That's what I was after. But yes it does seem inconsistent.

  I think it's because the index entries in sys.partitions are deleted when an index is disabled.

  But then how does it know to recreate the partitions of a disabled index on rebuild. That should also default to unpartitioned according to the article above (not that i'm complaining!)

  ---

  Jakub @ Adelaide, Australia

  Thursday, January 23, 2014 1:50 AM
• 

  

  0

  Sign in to vote

  
  

  So that means that ETL processes that disable nc indexes when performing loads need to have the full index creation string hardcoded somewhere

  Do you know which other index settings are lost on nc index disable/rebuild? I just tested partitioning and that's retained thankfully.

  Edit:

  one other thing, rebuilding an enabled index RETAINS the data_compression setting, so this behaviour is inconsistent.

  Its always better to provide the options explicitly for REBUILD INDEX to avoid any miss.

  As I mentioned in the test script, it applies only when you do a DISABLE and REBUILD in my testing not for REBUILD-ing the enabled ones.

  Thursday, January 23, 2014 2:27 AM