index issue

Question

Post reply

index issue

balaji.ganga

SSCrazy

Points: 2280
More actions
September 13, 2010 at 10:24 pm

#240273

Hi All,
A primary table consists of 1,49,562 rows. i have analysed that table. there is no index at all.
afterwards, i had checked that fragmentations. its says Actual count:BestCount 12.6% and
logical fragmentation 42%.
Afterwards, i decide to create an index for that table. i created a clustered index.
run the update statistics command for that table. Then i checked the fragmentation of that
table. It shows Actual Count:BestCount 20%. Logical fragmentation 80%. (1:5)
My question is. how can i fix this issue(like BestCount:ActualCount) 100%. logical fragmentation 0%
Anybody give me a suggestion. it would be very great helpful
Thanks & Regards
Balaji.G

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Can you provide the DDL for the table?

Or even at least the data type(s) of the column(s) in the Clustered Index?

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

balaji.ganga SSCrazy Points: 2280 More actions · Answer 2

Hi,

create clustered index c_app_xyz on xyz(app) with fillfactor=80

in xyz table, app is int, and also identity field. after creating a clustered index

dbcc showcontig(xyz)

it shows scan density (Bestcount:ActualCount) 84.5

an other table is abc column name same app field type is varchar(50). no identity field

create clustered index c_app_abc on abc(app) with fillfactor=80

dbcc showcontig(abc)

it shows scandensity(Bestcount:ActualCount) 20.00% (1:5)

Logical Fragmentation 80.00%

let me know the reason. and how to fix it..

Thanks

Balaji.G

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

What is the uniqueness of your data in that second CI? Have you examined using a numeric type for your CI?

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

1) how much free space is in your database? If you are like almost every one of my clients then you are using autogrowth to manage your file size and you have essentially no empty space in the database for the index to be laid down sequentially --> fragmentation will exist even for new index.

2) pretty sure that fillfactor of 80 will show up in showcontig, but coffee hasn't kicked in yet... 🙂

3) switch to using the DMV that replaces showcontig: sys.dm_db_index_physical_stats

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Just out of curiosity..have you run a dbcc dbreindex() or alter index rebuild? Also make sure you update statistics with fullscan after creating/dropping/modifying indexes.