June 17, 2010 at 3:32 pm
I have a table with a status column. This column is heavily updated as a records status can change over time. There are a set of queries that access this table and include status as part of the T-SQL criteria. It makes sense to include this column as part of an already existing index. What I’ve noticed is even though no other data changes in the table, both the clustered primary key index and the before mentioned non clustered index both become fragmented as the status values are updated. I’ve even seen that the rowmodctr for these indexes remain 0 as the fragmentation percentage rises.
I’m curios if anyone else has seen this and if the consensus thought is that this is working as designed? Unfortunately we are a Standard Edition shop so online indexing is not an option.
June 18, 2010 at 3:22 am
Are you shrinking the database (manual or auto shrink) That'll cause fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2010 at 6:22 am
Howmany rows the specific table contain? what is the size of the table?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 18, 2010 at 7:05 am
The tests I ran did not include shrinking the database.
The size of the table varies from a few hundred thousand to 20 million.
The test I ran involved creating a table similar to the one below with around 170,000 rows in it.
CREATE TABLE Test_Table
(col1 INT NOT NULL,
col2 varchar(80) NULL,
col3 varchar(100) NULL)
alter table test_table add constraint pk_test_table primary key (col1)
create index idx_test_table on test_table
(col2 asc)
include (col3)
then i ran a loop through each record in the table only updating the col3 column. With each update within the loop, the fragmentation increased.
June 18, 2010 at 7:22 am
Are you increasing the size of the varchar? (null to non-null)? That will fragment the index because you're increasing the size of the index row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2010 at 7:39 am
I had thought that maybe the variable length was causing the fragmentation as well. The status column values range from 5 to 12 characters. In my test table, I ran two tests. Both tests were preceeded with an index rebuild.
The first test, I updated every row in the table, setting col3 = col3... no value change, just an update on the table. This didn't introduce any fragmentation on the index but caused the primary key index to become 97% fragmented.
update Test_Table
set col3 = col3
Test 2, I updated every row in the table setting col3 = 'a'. This set both indexes to near 100% fragmentation.
update Test_Table
set col3 = 'a'
Script used to evaluate fragmentation:
Select
Object_Schema_Name(DMV.Object_ID,7)+'.'+Object_Name(DMV.Object_ID,7) as Table_Name
,str(DMV.Object_id) as Table_ID
,i.name AS Index_Name
,i.indid as Index_ID
,'Not Processed' as Index_Type_Desc
,dmv.avg_fragmentation_in_percent as Avg_Fragment_In_Percent
,dmv.page_count as Page_Count
,dmv.record_count as Record_Count
,i.rowmodctr as Records_Modified
From sys.sysindexes i inner join master.sys.dm_db_index_physical_stats (7, NULL, NULL , NULL, 'SAMPLED') as DMV
on DMV.Object_ID = i.id
and DMV.Index_ID = i.indid
Where Object_Name(DMV.Object_ID,7) = 'Test_Table'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply