SQL Server Performance Forum – Threads Archive

Index defrag seems to not work

As per BOL for 2005 i tried to defrag indexes by both methods 1.reorganizing an index
2.rebuilding an index but still some of my tables indexes are at the same avg_fragmentation_in_percent that they were before i did my defrag using the above 2 method. Any pointers ? thanks John

Can you post DBCC SHOWCONTIG results?
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

well i used sys.dm_db_index_physical_stats instead
database_id object_id indexe_id partition_number alloc_unit_type_desc index_depth index_level avg_frag_in_perfrg_cnt
10118511CLUSTERED INDEXIN_ROW_DATA2058.333333333333381.512NULLNULLNULLNULLNULLNULLNULLNULL
Let me know if you still want me to run DBCC SHOWCONTIG ? thanks

What type of indexes are on that table? http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=0d623c99-2a87-4ea9-886f-bf23da3946e8 for reference. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

There are 3 indexes on the tables. they are nonclustered located on PRIMARY
clustered, unique, primary key located on PRIMARY
nonclustered located on PRIMARY And all of them have same avg_fragmentation_in_percent after defrag.

Did you user DETAILED mode? Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Is there text or ntext column?

i used the LIMITED mode for sys.dm_db_index_physical_stats.
there are no text or ntext column.

The LIMITED mode is the fastest and scans the smallest number of pages. It scans all pages for a heap, but only the parent-level pages for an index, which are the pages above the leaf-level. The DETAILED mode scans all pages and returns all statistics. Try with DETAILED. Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I’m having the same problem, dm_db_index_physical_stats is showing the same info after index rebuild/re-org, under sql 2005 sp1..
Anyone has an answer ?

Can you post DBCC showcontig, please. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

thanks for the quick response, here’re my findings.
When page count is LESS than 8, there’s no difference after rebuild/re-org. the more page count gives the better result after rebuild/re-org.. I also found a interesting table with two NC indexes (no CL) which has 60 pages of Heap w/ 78% frag and 18 pages of NC index w/38% frag and 38 pages of NC w/ 0% frag, this table refused to update the frag pct no matter what I did, and it shows the same results on dbcc showcontig as well..
Here’s my conculsion, in my smart rebuild/re-org scripts I decided to check the page count > 16 and frag pct > 30% and space used > 75% to pick up the tables to work on.
Any more info will be appreciated.

Not sure if this helps or not, but I just found a situation on my system (sql 2000) where dbcc dbreindex was not working to clean up extent fragmentation, so perhaps the same could apply in your situation. My datafile was nearly 100% full when I saw no improvement. I increased the filesize so that new contiquous space was available and sure enough this time the rebuild removed the extent fragmentation. Not sure if the filesize itself could be an issue for you or not. Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.

Also be sure that index is not ID 0. This is table itself no index.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

When you say datafile do you mean the .mdf file? What was it set at orginally and what did you increase it too?
Thanks!

Microsoft has as whitepaper on this.
"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Kalen Delaney has also written a blog post about this.
http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
I think that you shouldn’t rebuild / reorganize these small indexes. I have a stored procedure that you can use if you like. It does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
http://ola.hallengren.com

]]>