DMV for index options (such as ONLINE)

• brett.walker

  SSC-Addicted

  Points: 475

  More actions

  January 13, 2017 at 12:18 pm

  #339275

  Hello,

  I have a script that returns lots of useful information about all the indexes in my tables.  I'm using sys.objects, sys.indexes, sys.columns, etc.  I'm wanting to add columns for all the index options for each index (ONLINE, PAD_INDEX, ALLOW_ROW_LOCKS, etc.  I can't find where these options are stored in the DMV's,  Can someone help me out?

  Thanks,
  Brett

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 13, 2017 at 12:35 pm

  #1922673

  brett.walker - Friday, January 13, 2017 12:18 PM

  Hello,

  I have a script that returns lots of useful information about all the indexes in my tables.  I'm using sys.objects, sys.indexes, sys.columns, etc.  I'm wanting to add columns for all the index options for each index (ONLINE, PAD_INDEX, ALLOW_ROW_LOCKS, etc.  I can't find where these options are stored in the DMV's,  Can someone help me out?

  Thanks,
  Brett

  some options, like ONLINE, are not stored; similar to how queries can be executed WITH(NOLOCK), rebuilding of indexes can be done with ONLINE, if you have enterprise edition, and the index meets the criteria, yada yada,  so that is not actually stored anywhere.

  The msdn definition here does not  make it obvious which options are preserved and which are not, but if you compare it to the columns in sys.indexes, you could infer which are run time only...the options are not stored anywhere else.
  according to my best peek, the items highlighted are not stored in the DMV's
  https://msdn.microsoft.com/en-us/library/ms188783.aspx?f=255&MSPPError=-2147217396

  <relational_index_option> ::=  {    PAD_INDEX = { ON | OFF }    | FILLFACTOR = fillfactor    | SORT_IN_TEMPDB = { ON | OFF }    | IGNORE_DUP_KEY = { ON | OFF }    | STATISTICS_NORECOMPUTE = { ON | OFF }    | STATISTICS_INCREMENTAL = { ON | OFF }    | DROP_EXISTING = { ON | OFF }    | ONLINE = { ON | OFF }    | ALLOW_ROW_LOCKS = { ON | OFF }    | ALLOW_PAGE_LOCKS = { ON | OFF }    | MAXDOP = max_degree_of_parallelism    | DATA_COMPRESSION = { NONE | ROW | PAGE}    [ ON PARTITIONS ( { <partition_number_expression> | <range> }    [ , ...n ] ) ]  }    

  [/.quote]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• brett.walker

  SSC-Addicted

  Points: 475

  More actions

  January 13, 2017 at 12:44 pm

  #1922675

  Lowell - Friday, January 13, 2017 12:35 PM

  brett.walker - Friday, January 13, 2017 12:18 PM

  Hello,

  I have a script that returns lots of useful information about all the indexes in my tables.  I'm using sys.objects, sys.indexes, sys.columns, etc.  I'm wanting to add columns for all the index options for each index (ONLINE, PAD_INDEX, ALLOW_ROW_LOCKS, etc.  I can't find where these options are stored in the DMV's,  Can someone help me out?

  Thanks,
  Brett

  some options, like ONLINE, are not stored; similar to how queries can be executed WITH(NOLOCK), rebuilding of indexes can be done with ONLINE, if you have enterprise edition, and the index meets the criteria, yada yada,  so that is not actually stored anywhere.

  The msdn definition here does not  make it obvious which options are preserved and which are not, but if you compare it to the columns in sys.indexes, you could infer which are run time only...the options are not stored anywhere else.
  according to my best peek, the items highlighted are not stored in the DMV's
  https://msdn.microsoft.com/en-us/library/ms188783.aspx?f=255&MSPPError=-2147217396

  <relational_index_option> ::=  {    PAD_INDEX = { ON | OFF }    | FILLFACTOR = fillfactor    | SORT_IN_TEMPDB = { ON | OFF }    | IGNORE_DUP_KEY = { ON | OFF }    | STATISTICS_NORECOMPUTE = { ON | OFF }    | STATISTICS_INCREMENTAL = { ON | OFF }    | DROP_EXISTING = { ON | OFF }    | ONLINE = { ON | OFF }    | ALLOW_ROW_LOCKS = { ON | OFF }    | ALLOW_PAGE_LOCKS = { ON | OFF }    | MAXDOP = max_degree_of_parallelism    | DATA_COMPRESSION = { NONE | ROW | PAGE}    [ ON PARTITIONS ( { <partition_number_expression> | <range> }    [ , ...n ] ) ]  }    

  [/.quote]

  Got it.  That makes sense.  

  Thanks for the help.