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
Welcome to the forum!. Clustering indexes are on line. Can you post your query and all indexes in that table?
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
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"
when i create index i select desc option but i wonder about how can i query whether my indexs online or offline?
[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.
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.
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