October 18, 2007 at 4:10 am
Hi everybody, I found a heavily fragmented table so I ran
alter index all on mytable
but after it was done the sys.dm_db_index_physical_stats view still showed the fragmentation % to be just as high as it was.
Any idea why this is happening?
Thanks in advance!
October 18, 2007 at 4:22 am
Hi
Which option(s) did u use in your alter index statement.
Try doing a DBCC Indexdefrag or if u can do DBCC DBReindex. DBReindex can be done using REBUILD option in Alter Index statement.
"Keep Trying"
October 18, 2007 at 4:22 am
So when you ran that commmand you didn't get an error like this?
[font="Courier New"]Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MyTable'.[/font]
John
October 18, 2007 at 4:42 am
No errors...I did a dbcc indexdefrag and got
pages scanned 7
pages moved 0
pages removed 0
the index is showing frag % = 85,7142857142857
I even dropped and recreated the index and the frag % was the same...very odd.
BTW: This index is a non-clustered index...the table's clustered index was indeed defragged down to 0 fragmentation...is there something special about the non-clustered indexes?
October 19, 2007 at 2:47 am
It is almost impossible to get zero fragmentation on an index, especially if it is a non-cluster index.
The fragmentation value is telling you how many non-contiguous chunks of extents you have. When you rebuild an index, all that you ensure is that the extents will be physically allocated in the index sequence, and no unused extents are left between the extents allocated to your index. It is this work that maximises performance. Having the table in a single fragment adds nothing more to the performance, because SQL does I-O in extents.
You are only likely to get a non-fragmented cluster index if the table is the only table in the file group. With multiple tables, the data for your table is almost certain to be interleaved with extents for other tables. Even if your table is the only one in the filegroup, your non-cluster indexes will have to compete for extents with each other, resulting in fragmentation.
If you have multiple files in the filegroup, then the fragmentation figure will always be very high. This is because the extents for your object are allocated across all the files. Because the extents are physically sequenced in the optimum way, you get best performance even though the fragmentation figure is high.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 19, 2007 at 3:29 am
OK, thanks to everybody for the quick responses. I guess I was a bit optimistic about the powers of alter index. 🙂
October 19, 2007 at 4:11 am
Especially if you use incorrect or incomplete syntax.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply