Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2007-08-16 : 13:08:00
|
| I have a table with 50% Logical Scan Fragmentation. [ according to Dbcc Showcontig (myTable) ]Why after running DBCC INDEXDEFRAG (myDB,myTable) does it still sit at 50%.Why isn't it lower? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-16 : 14:43:51
|
| use alter indexand see what the results are then._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-16 : 22:48:51
|
| Need rebuild clustered index. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-08-17 : 06:36:50
|
| thought it was that. I guess I've hoped all this time that defragging can do a bit better of a job than that...ALTER INDEX ALL ON myTableREBUILD WITH (FILLFACTOR = 100) |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-08-17 : 07:34:57
|
| FILLFACTOR = 100....now you are inviting more fragmentation straight away afterwards....would you not have gone for for 95,96,97?...i.e. to leave some minor expansion room before the 1st hit for new space on the 1st insert of a new record. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-17 : 07:38:35
|
| that depends opn his PK type._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-08-17 : 11:10:07
|
| in this particular case 100 is the correct value because data in this table changes once a year.i choose fillfactors on a table by table basis and always pad themmy pk is a single column guid non-clustered |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-17 : 11:13:06
|
| my bad. depends on you Clustered Index datatype not PK.i always somehow assume that the PK is the clustered index._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
Logical Scan Fragmentation