April 1, 2010 at 5:35 pm
Why would fragmentation increase after index is rebuild :w00t:??
I'm using the sys.dm_db_index_physical_stats DMV to gather fragmentation.
SELECT
db_name(database_id),object_id, index_id, record_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'detailed')
WHERE index_id > 0 and index_level = 0 and avg_fragmentation_in_percent > 10
I generate and execute the following command:
set @SQLcmd =
'ALTER INDEX [' + @Iname + '] ON [' + @Tname + '] REBUILD WITH ( FILLFACTOR = 80,PAD_INDEX = ON)'
exec(@SQLcmd)
Max table size < 20K records.
Current FILLFACTOR = 95,PAD_INDEX = OFF.
All tables have < 20K records.
There are no XML or blob columns.
In about half of the tables ... fragmentation does decrease, but not all the way down to 0.
In the other half the fragmentation increases ... could it be caused by setting the PAD_INDEX to ON ... if not what could it be.
I'm running this on a restored copy of the db ... any help would be greatly appreciated ...
Enjoy
"Give them the tools:crazy: ... Not the keys:smooooth:"
April 1, 2010 at 5:40 pm
What kinds of indexes are you seeing that actually increase in fragmentation?
How many pages are on each of the indexes?
What is the before and after fragmentation % level?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 3:40 am
Can you post the result of query you posted above SELECT
db_name(database_id),object_id, index_id, record_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'detailed')
WHERE index_id > 0 and index_level = 0 and avg_fragmentation_in_percent > 10
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply