Re-Indexing / Fragmented Indexes 1

[Zen216]

Hi All,
I had a question about indexes and re-indexing.. I kinda stepped into this job, and have been reading up...lol
Anyways, performance is down, and I followed some of the steps outlined in this thread,, thread962-1370668

I created a maintenence plan to re-index, and the log looks like it did.

On the showcontig reports created from the thread mentioned, I have a few that whow both logical scan fragmentation and extent scan fragmentation..

On the ones where it did not show the logical scan fragmentation,, there was no change,, on the ones where there were both listed,, there was a huge improvement,,,

I was hoping that someone could tell me what the difference is between the logical scan and extent scan fragmentation,,,

also, why one would be successful in re-indexing,, and why the other would not make a difference..

and what, if anything, I can do to fix the ones that didn't change...

here is the before and after of 2 tables...

Before...

DBCC SHOWCONTIG scanning 'WORKPENDHISTORY' table...
Table: 'WORKPENDHISTORY' (1300915706); index ID: 0, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 161948
- Extents Scanned..............................: 20337
- Extent Switches..............................: 20336
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.54% [20244:20337]
- Extent Scan Fragmentation ...................: 54.46%
- Avg. Bytes Free per Page.....................: 329.0
- Avg. Page Density (full).....................: 95.94%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


after...

DBCC SHOWCONTIG scanning 'WORKPENDHISTORY' table...
Table: 'WORKPENDHISTORY' (1300915706); index ID: 0, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 161948
- Extents Scanned..............................: 20337
- Extent Switches..............................: 20336
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.54% [20244:20337]
- Extent Scan Fragmentation ...................: 54.46%
- Avg. Bytes Free per Page.....................: 329.0
- Avg. Page Density (full).....................: 95.94%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.




that one there did not have the logical scan fragmentation,,, and there really was no difference...



before....

DBCC SHOWCONTIG scanning 'CUSACCT' table...
Table: 'CUSACCT' (29243159); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 63152
- Extents Scanned..............................: 7940
- Extent Switches..............................: 7939
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.42% [7894:7940]
- Logical Scan Fragmentation ..................: 2.03%
- Extent Scan Fragmentation ...................: 47.96%
- Avg. Bytes Free per Page.....................: 154.6
- Avg. Page Density (full).....................: 98.09%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.




after...

DBCC SHOWCONTIG scanning 'CUSACCT' table...
Table: 'CUSACCT' (29243159); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 67377
- Extents Scanned..............................: 8444
- Extent Switches..............................: 8443
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.75% [8423:8444]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 3.16%
- Avg. Bytes Free per Page.....................: 652.5
- Avg. Page Density (full).....................: 91.94%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



but this one did,, and there was a huge improvement...


Thanks everyone.

 

[gmmastros]

I think the problem you have is because there are no indexes at all on the table. This is considered a Heap table. On way to know for sure is...

[tt][blue]
sp_helpindex 'WORKPENDHISTORY'
sp_helpindex 'CUSACCT'
[/blue][/tt]

I suspect that the one for WorkPendHistory returns nothing, and the one for CusAcct shows at least one index.

It makes sense that reindexing a table without indexes will not actually do anything. Right?

If it's possible, I would encourage you to put an index on the WorkPendingHistory table. You should take a look at the queries that use this table and add appropriate indexes.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Zen216]

Thanks!!..

That helped me alot..
I ran both, ,and workpending only has non-clustered indexes,, and the cusacct has a bunch of non-cluctered indexes,, and one clustered index..

So, I am assuming that the dbcc showcontig command reports back a logical scan fragemtation only if there are clustered indexes..?

Which would make alot of sense,,, so I guess the next question is..

How do I change a non-clustered index to a clustered index?


Thanks again.

 

[gmmastros]

How do I change a non-clustered index to a clustered index?

If it were me... I would probably drop one of the indexes and recreate it (so there are the same columns in it), but specifying clustered.

Or... right click the table (in SQL Server Management Studio).
Click Design.
On the tool bar, click "Manage Indexes and Keys"
Pick one, and change it to clustered.

If you use SSMS (the click-click method), it actually does the same thing. It drops the index and re-creates it clustered.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom