October 13, 2010 at 5:55 pm
Hi,
We have newly implemented Fast track architecture for SQL Server 2008.
I have created a table with partition function and scheme. I am loading data "Contiguously" to the table like "Order by key_Column option(MAXDOP 1). So the data loaded, but when I check the fragment values at the sys.dm_db_index_physical_stats table , I am getting low numbers for the avg_fragment_size_in_pages column. As microsoft recommed that this column value should be more than 400 , But I am not getting those numbers. I am getting less than 300.
when you look at sys.dm_db_index_physical_stats, a high value number for fragment_count or a low value for avg_fragment_size_in_pages can indicate lack of contiguousness.
My question is , Is there any option or way to increase the avg_fragment_size_in_pages columns values either in load or rebuild index ?
Note : I have drop end re-create index on a table , still numbers are getting lower.
Sample Code :
Insert into F_New Select * from F_Old Order by Day_ID Option (maxdop 1)
CREATE CLUSTERED INDEX CLX_Table
ON F_NEW (DAY_ID) WITH (DATA_COMPRESSION = PAGE)
ON F_PartitionScheme (DAY_ID)
Thanks for your valuable help.
Dhanapal Selvaraj
October 14, 2010 at 2:23 am
Two things come to my mind
1. the table may be low in volume( small data)
2. if it is fresh insertion which results in contiguous page filing/less page split
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 14, 2012 at 7:07 pm
We have rebuild with bigger partition like having 100 million data in one partition,still we are not able to get good fragment numbers for the partitioned table
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply