July 17, 2012 at 12:16 pm
Hi there
I have some fragmented indexes on my production databases.When i try to defrag them using ALTER INDEX ...REBUILD WITH(ONLINE=ON) some of them still remain fragmented although the index page count is much bigger then 1000 pages (so not very small index).for example one of them has avg_fragmentation_in_percent=59 % and the index page_count is 138984 pages and it's a clustered index.
Any idea??!!
Thanks in advance.
Pooyan
Pooyan
July 17, 2012 at 2:44 pm
Is the database being shrank after the reindexing completes?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 17, 2012 at 3:14 pm
No of course shrinking the data files ends in fragmented indexes.
Pooyan
July 17, 2012 at 3:16 pm
pooyan_pdm (7/17/2012)
No of course shrinking the data files ends in fragmented indexes.
Cool, someone that actually knows that! You'd be amazed at those who don't.
July 17, 2012 at 3:47 pm
What order are you defragmenting them? Clustered first then non-clustered? Does the table have any of the disallowed index types or large object data types?
July 17, 2012 at 3:54 pm
No the index does not contain any BLOB data types and no specific order is used in defraging them.
this is the script I wrote to defrag them:
USE [master]
GO
ALTER DATABASE [Uthertube] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO
USE [Uthertube]
SET NOCOUNT ON;
DECLARE @DB nvarchar(40)='Uthertube',@Threshold tinyint=9,@Table NVARCHAR(100),@Schema nvarchar(100),@Index NVARCHAR(256),@c CURSOR,@ExecStr NVARCHAR(512)
SET @C=CURSOR FAST_FORWARD FOR
SELECT OBJECT_NAME(ps.OBJECT_ID) TableName,s.name SchemaName,i.name Indexname FROM sys.dm_db_index_physical_stats(DB_ID(@DB),null,null,null,null) ps INNER JOIN sys.indexes i on ps.object_id=i.object_id and ps.index_id=i.index_id INNER JOIN sys.tables t on ps.object_id=t.object_id INNER JOIN sys.schemas s on t.schema_id=s.schema_id
WHERE (ps.avg_fragmentation_in_percent>@Threshold) and (i.index_id>0) and (ps.page_count>1000)
OPEN @C
FETCH NEXT FROM @C INTO @Table,@Schema,@Index
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @ExecStr=' ALTER INDEX ['+@Index+'] ON ['+@Schema+'].['+@Table+'] REBUILD WITH ( ONLINE = ON , SORT_IN_TEMPDB = ON)'
BEGIN TRY
EXEC (@ExecStr)
END TRY
BEGIN CATCH
SELECT @index IndexName, ERROR_MESSAGE() Error
END CATCH
FETCH NEXT FROM @C INTO @Table,@Schema,@Index
END
DEALLOCATE @C
GO
USE [master]
GO
ALTER DATABASE [Uthertube] SET RECOVERY FULL WITH NO_WAIT
GO
Pooyan
July 17, 2012 at 4:24 pm
Can you post the complete column output from sys.dm_db_index_physical_stats using a detailed scan for the index in question
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2012 at 4:27 pm
My concern would be that if the non-clustered indexes were rebuilt, then the clustered index was rebuilt, the non-clustered indexes might get re-fragmnted based on the movement of rows in the clustered index.
July 17, 2012 at 4:34 pm
It was my own fault. Because there were so many indexes I didn't read all the error messages during Defrag .As David said it was because of BLOB field in index keys. Thanks All;-)
Pooyan
July 17, 2012 at 4:39 pm
pooyan_pdm (7/17/2012)
It was my own fault. Because there were so many indexes I didn't read all the error messages during Defrag .As David said it was because of BLOB field in index keys. Thanks All;-)
Thanks for owning up and posting back 🙂
With LOBs you'll need to rebuild offline.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2012 at 4:50 pm
David Webb-200187 (7/17/2012)
My concern would be that if the non-clustered indexes were rebuilt, then the clustered index was rebuilt, the non-clustered indexes might get re-fragmnted based on the movement of rows in the clustered index.
If you rebuild the NCIs first then the CI, you force rebuilds on the NCIs because of changes to the CI.
July 17, 2012 at 6:25 pm
By rebuilding Clustered indexes ,index definition(index key) are not changed so why should any Nonclustered index get rebuilt??
Pooyan
July 17, 2012 at 7:08 pm
pooyan_pdm (7/17/2012)
By rebuilding Clustered indexes ,index definition(index key) are not changed so why should any Nonclustered index get rebuilt??
The clustered index maintains the logical order of the data. This means while rebuilding the clustered index the data will actually be sorted resulting in changes to which pages the data resides on. This means that all the clustered indexes will also have to be rebuilt to reflect the changes to where the data resides in the clustered index.
July 17, 2012 at 10:03 pm
As far as I know in the leaf level of any nonclustered index in addition to index key(s) or included columns, the clustered index key(s) are stored. In this case changing the order of clustered index or changing the actual pages which clustered index key(s) reside in ,has nothing to do with the noneclustered index. Whenever theres a need to traverse from noneclustered index to the clustered index(a bookmark look up for example) it's done with searching the associated clustered key in the clustered index with no need to know in which page this key resides.
This post whould be helpful.
Pooyan
July 17, 2012 at 11:15 pm
pooyan_pdm (7/17/2012)
This post whould be helpful.
The clustered index key is stored in the leaf node of the nonclustered indexes. If you move data around in the pages of the clustered index, you change the address of the data, requiring that the nonclustered indexes be rebuilt to reflect the new location of the data that they reference.
This is also why when building indexes you first create the clustered index then the nonclustered indexes. When dropping the indexes you start with the nonclustered indexes then drop the clustered index.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply