Hello,

In my datavase all columns have Identity Value as a PK.

Now today I check Index Fragmentation I saw that many cluster and Non cluster Index are avg.Fragmentation is around 99 %

I thought that in Identity column record is always inserted at bottom so there is no fill factor assigned to it.

So in this case Do I need to set Fill factor for Cluster and Non Cluster Index?

If Yes Then For PK How much - 95 % or what? and same for Non cluster or It should around 85 to 90

Hi,

I assume that you have number of VARCHAR columns in your table and those have been updated frequently, with 100% fill factor on PK any additional character in varchar columns could lead to a page split which inturn contributes to fragmentations.

If you have lot of VARCHAR columns then you can consider fill factor which is lower than 100%, so that any update on these column data  would not create page splits.

How big is the table?

SELECT db_name(database_id) AS DatabaseName,
OBJECT_NAME(i.object_id) AS TableName 
,
i.name AS TableIndexName 
,
phystat.avg_fragmentation_in_percent

FROM
sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i 
ON i.object_id = phystat.object_id 
AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40 
and page_count>=1000