|
|
Posted by Neil on 12/28/07 11:46
>>> Or put in another way: do you have tables without a clustred index?
>>
>> Yes, apparently so. The query returned 51 rows.
>
> And that included your big tables?
>
> I think we on to something here. Your "optimization job" is in vain;
> you cannot defragment a heap (heap = table without a clustered index).
>
> Adding clustered indexes on all tables takes a little thought to
> make the best choice for the index. Then again, if you only have
> one index on a table, that is probably the best choice.
>
> But you could also just add a clustered index on any column and
> then drop it. That will defragment the table.
>
> While I know a few who disagrees, I think it's best practice to
> have a clustered index on all tables.
OK, yes, none of the five tables I previously reported on had clustered
index. I had created primary keys on those tables, but not clustered
indexes. For some reason, I think I misunderstood this blurb from BOL:
"PRIMARY KEY constraints create clustered indexes automatically if no
clustered index already exists on the table and a nonclustered index is not
specified when you create the PRIMARY KEY constraint."
I think I assumed that the clustered index would automatically be created on
the pk index.
OK, so I added clustered indexes to those five tables (either on the primary
key or on an index that I felt approximated the order that the data would be
used in; or, in the case of child tables, on the foreign key field). I also
added clustered indexes on about 10 other tables that stood out as tables of
significant size.
For the five large tables, the before->after on Reserved was as follows:
CustomerMerges: 226661->959
ImageFilesProcessed: 72333->1727
CustActivity: 38034->303
CustomerEvents: 28018->367
ImageFileErrors: 24391->2046
So, some very dramatic changes there!
In terms of fragmentation, also some very dramatic changes. Here is the
"after" data for those five tables:
DBCC SHOWCONTIG scanning 'CustomerMerges' table...
Table: 'CustomerMerges' (709733731); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 952
- Extents Scanned..............................: 119
- Extent Switches..............................: 118
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [119:119]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 6.72%
- Avg. Bytes Free per Page.....................: 12.3
- Avg. Page Density (full).....................: 99.85%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'ImageFilesProcessed' table...
Table: 'ImageFilesProcessed' (1992550332); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 1763
- Extents Scanned..............................: 221
- Extent Switches..............................: 220
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [221:221]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.90%
- Avg. Bytes Free per Page.....................: 48.1
- Avg. Page Density (full).....................: 99.41%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'CustActivity' table...
Table: 'CustActivity' (1006730739); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 277
- Extents Scanned..............................: 35
- Extent Switches..............................: 34
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [35:35]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 2.86%
- Avg. Bytes Free per Page.....................: 41.6
- Avg. Page Density (full).....................: 99.49%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'CustomerEvents' table...
Table: 'CustomerEvents' (1029734871); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 337
- Extents Scanned..............................: 43
- Extent Switches..............................: 42
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 100.00% [43:43]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 6.98%
- Avg. Bytes Free per Page.....................: 38.6
- Avg. Page Density (full).....................: 99.52%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'ImageFileErrors' table...
Table: 'ImageFileErrors' (69067482); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 1968
- Extents Scanned..............................: 246
- Extent Switches..............................: 245
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [246:246]
- Logical Scan Fragmentation ..................: 2.74%
- Extent Scan Fragmentation ...................: 23.58%
- Avg. Bytes Free per Page.....................: 68.1
- Avg. Page Density (full).....................: 99.16%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I then ran the "sp_spaceused NULL, true" command again. But the results were
pretty much the same as they had been previously. I then ran the
optimization job and then reran the "sp_spaceused NULL, true" command, and
the results were still pretty much the same.
In any case, there does seem to be much improvement by adding the clustered
index. So thank you for that!
I was wondering if there's a way to have the query: SELECT * FROM sysindexes
WHERE indid = 0 list the actual table names instead of the IDs of the tables
without clustered indexes?
Thanks, Erland!
Neil
[Back to original message]
|