November 15, 2011 at 9:21 pm
Hi,
Since there is no option to rebuild an index on a specific partition with option ONLINE=ON, would rebuilding index across all partitions but with option ONLINE=ON avoid blocking and help avoid lock type LCK_M_SCH_M?
Not sure why but see some blocking and lock LCK_M_SCH_M when rebuilding a NC index on a specific partition.
Thanks
November 16, 2011 at 12:07 am
I don't think it's a limitation...
REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
When you rebuild an XML index or a spatial index, the options ONLINE = ON and IGNORE_DUP_KEY = ON are not valid.
PARTITION
Specifies that only one partition of an index will be rebuilt or reorganized. PARTITION cannot be specified if index_name is not a partitioned index.
PARTITION = ALL rebuilds all partitions.
ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to ON an error is raised.
Note Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
ALTER INDEX (Transact-SQL)
November 16, 2011 at 12:27 am
Dev (11/16/2011)
I don't think it's a limitation...
From the page you referenced:
WITH (<single_partition_rebuild_index_option>)
SORT_IN_TEMPDB, MAXDOP, and DATA_COMPRESSION are the options that can be specified when you rebuild a single partition (PARTITION = n). XML indexes cannot be specified in a single partition rebuild operation.
Rebuilding a partitioned index cannot be performed online. The entire table is locked during this operation.
Which kinda defeats the purpose of partitioning.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 12:30 am
Lexa (11/15/2011)
Since there is no option to rebuild an index on a specific partition with option ONLINE=ON, would rebuilding index across all partitions but with option ONLINE=ON avoid blocking and help avoid lock type LCK_M_SCH_M?
Yes, that or reorganise at a partition level. Be aware that if you're rebuilding the entire table and it's a huge table you'll have all the normal fun of rebuilding a huge table - log growth, TempDB growth, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 5:55 am
GilaMonster (11/16/2011)
Lexa (11/15/2011)
Since there is no option to rebuild an index on a specific partition with option ONLINE=ON, would rebuilding index across all partitions but with option ONLINE=ON avoid blocking and help avoid lock type LCK_M_SCH_M?Yes, that or reorganise at a partition level. Be aware that if you're rebuilding the entire table and it's a huge table you'll have all the normal fun of rebuilding a huge table - log growth, TempDB growth, etc.
Thanks. Would there be any difference in doing a REBUILD of index with ONLINE=ON vs. CREATE INDEX with ONLINE=ON, DROP_EXISTING = ON? When is DROP_EXISTING =ON preferred?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply