| Author |
Topic |
|
tobyc
Starting Member
3 Posts |
 Posted - 2007-04-19 : 09:18:33
|
| We are running a maintenance plan that rebuilds the indexes for all the databases. I have ran showcontig after the maintenance plan and am still seeing fragmentation. Here is one of the master tables that is included in on the maintenance job.sysobjects table: pages scanned - 5extents scanned - 3extents switches - 3avg. pages per extent - 1.7scan density - 25%logical scan fragmentation - 40%extent fragmentation - 66.67%avg.bytes free per page - 1360.4avg. page density 83.19%According to what I have read this still seems to be fragmented. What can be done to improve this? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-19 : 09:34:15
|
| Maintenance plan doesn't handle system tables. Also, sql puts small tables in shared extents. You can't get them to 100% scan density. |
 |
|
|
tobyc
Starting Member
3 Posts |
Posted - 2007-04-19 : 09:40:48
|
| Should the system tables be reindexed? If so would a seperate job that runs dbreindex on the master work to defrag the system tables? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-19 : 09:57:22
|
| Don't have to, and dbreindex doesn't work on sysytem tables. |
|
|
|
tobyc
Starting Member
3 Posts |
Posted - 2007-04-19 : 10:57:49
|
| Okay, I'll quit worring about the system tables. The next question is related. Last night I ran the maintenance plan (options: reorganize data and index pages - change free space per page percentage to 10%). IT still seems to be fragmented this morning. pages scanned - 72extents scanned -18extent switches -17avg. pages per extent - 4Scan Density - 50%Extent scan fragmentation - 94%avg. butes free per page 5124avg. page density 36.68%From what I have read so far, the scan density should be as close to 100% as possible. The extent canc fragmentation should be between 0% and 10%. This table still seems fragmented. IN this case I'm not sure what to do. The maintenance plan uses dbreindex, but does it work differently if you use the maintenance plan or run the command in a job by itself? Any explaination of this would be appreciated. Thanks, |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-19 : 16:54:21
|
| If the table doesn't have clustered index, you can't defrag it. By the way, did you check index fill factor? Too much free space on the page. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-20 : 02:25:38
|
| Apart from the System Table issue I don't think its worth worrying about for small tables.We Defrag big tables, and Reindex small tablesKristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-04-20 : 04:08:45
|
| Kristen in this context what would you define as big?steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-20 : 09:00:51
|
| "in this context what would you define as big?"HiYa!CountPages from DBCC SHOWCONTIG >= 10000Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-04-24 : 03:39:08
|
| Thanks, is this based on any figures or is it a gut feeling sort of thing based on experience?cheerssteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-24 : 04:05:08
|
| Well its the figure we use, can't remember how we came to it!We did find that using IndexDefrag (SQL 2000) was still leaving SHOWCONTIG with poor density results, and we also thought that for small tables REINDEX would not block them for long. However, I was surprised to see a Page Count as high as 10,000 when I went to check for you - so it may be a bit high!Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-04-25 : 03:01:04
|
| Fair enough, Many thanks Kristensteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
|
DB Maintenance -dbreindex