SQL Server Performance

SQL server 2005 online indexing

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by ismailadar, Dec 16, 2009.

  1. ismailadar New Member

    hi everybody,
    i wonder about SQL server 2005 online indexing because i have an applicaiton that inserts a table about 1500 rows for each minute. i have a clustered index on this table the index is insertedtime coloum. but when i query last inserted 500 rows it is too slowy. and i think sql server 2005 can not able to perform online indexing? we use sql server 2005 enterprise editon
    thanx in advanced
  2. Luis Martin Moderator

    Welcome to the forum!.
    Clustering indexes are on line.
    Can you post your query and all indexes in that table?
  3. ismailadar New Member

    thanx for your reply here is my query result for indexes on this table:
    index_id1
    partition_number1
    index_type_descCLUSTERED INDEX
    alloc_unit_type_descIN_ROW_DATA
    index_depth3
    index_level0
    avg_fragmentation_in_percent4,571423306
    fragment_count6684
    avg_fragment_size_in_pages16,23608618
    page_count108522
    avg_page_space_used_in_percentNULL
    record_countNULLAlso when i query last inserted data i use :
    Select top 500 Coloums from insertedTable
    order by insertTime desc
  4. Luis Martin Moderator

    What about to create cluster index desc?
    CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) [ WITH <backward_compatible_index_option> [ ,...n ] ] [ ON { filegroup_name | "default" } ]
    because default is ASC so the cluster index is not useful in "order ...... desc"
  5. ismailadar New Member

    when i create index i select desc option but i wonder about how can i query whether my indexs online or offline?
  6. FrankKalis Moderator

    [quote user="ismailadar"]
    when i create index i select desc option but i wonder about how can i query whether my indexs online or offline?
    [/quote]
    Check out INDEXPROPERTY in BOL. One property is "IsDisabled". This is about the closest you can get. To the best of my knowledge there is no way to determine whether an index has been rebuilt online or offline. This is a rebuild option that you specify at runtime when the index is altered. Once this operation is completed, it is irrelevant.
    Also, if your query takes "too long" for just 500 rows & you need this query frequently, it may worth considering a dedicated index for the query.
  7. satya Moderator

    What kind of syntax you are using to perform ALTER INDEX ..>ONLINE operation?
    As per BOL, if you use ALTER INDEX REBUILD the indexes that are excluded are: Disabled clustered index or disabled indexed view, XML index and Index on a local temp table. Specifying the keyword ALL may cause the operation to fail when the table contains an excluded index.
    When you perform online index operations, the following guidelines apply:
    • Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.

      • Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.

        • Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.
  8. ismailadar New Member

    thanx for your reply here is my query result for indexes on this table:
    index_id1
    partition_number1
    index_type_descCLUSTERED INDEX
    alloc_unit_type_descIN_ROW_DATA
    index_depth3
    index_level0
    avg_fragmentation_in_percent4,571423306
    fragment_count6684
    avg_fragment_size_in_pages16,23608618
    page_count108522
    avg_page_space_used_in_percentNULL
    record_countNULLAlso when i query last inserted data i use :
    Select top 500 Coloums from insertedTable
    order by insertTime desc

Share This Page