Performance Tuning Indexes

Question

Performance Tuning Indexes

W R

Mr or Mrs. 500

Points: 523
More actions
January 4, 2007 at 9:35 am

#171727

When I execute dbcc showcontig(xxx) I get results:
DBCC SHOWCONTIG scanning 'XXX' table...
Table: 'XXX' (1029578706); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 3084
- Extents Scanned..............................: 389
- Extent Switches..............................: 388
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.23% [386:389]
- Extent Scan Fragmentation ...................: 80.98%
- Avg. Bytes Free per Page.....................: 387.2
- Avg. Page Density (full).....................: 95.22%
I tried dbcc indexdefrag and dbcc reindex with a fill factor of 80 but I cannot seem to improve the Scan Density or Extent Scan fragmentation. The table has about 1,020,384 rows.
Is there someone else I need to look into?
Thank you.
-W R

Viewing 15 posts - 1 through 15 (of 16 total)

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

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 1

The scan density is about as good as it gets. Closer to 100% the better. I'm not sure the syntax in the command your using but the avg. page density being 95% indicates your fill factor of 80% is not being used. I would try dbcc dbreindex (xxx,'',80). It will rebuild all indexes on the xxx table to 80% fill factor.

Tom

MohammedU SSCoach Points: 19644 More actions · Answer 2

It is already 99% and I don't think you can improve anything else...

I don't remember it where I read but you may not get 100% even after reindexing in some cases...

MohammedU
Microsoft SQL Server MVP

W R Mr or Mrs. 500 Points: 523 More actions · Answer 3

Tom,

Thank you for your post. How does the avg. page density being 95% indicate that fill factor of 80% is not being used.

Thanks.

-W R

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 4

Page density is fill factor, at least to begin with. If I build an index and specify a fill factor of 80 then my page density should be 80 to start. Depending on how many pages/extents are used. As insert/update/delete's happen that page density %tage will change. The more data you have stuffed on a page the quicker your scans are going to be, potentially slowing inserts/updates because of page splitting. I like to run most of my indexes at 95% fill factor because most of my processes I tune for are select queries. Faster reading through fewer pages/extents. I am also able to rebuild my indexes nightly if I need to. I'll show you an example.

-- dbcc showcontig (krdlypch)

-- original

-- DBCC SHOWCONTIG scanning 'KRDLYPCH' table...

-- Table: 'KRDLYPCH' (1124915079); index ID: 1, database ID: 21

-- TABLE level scan performed.

-- - Pages Scanned................................: 51124

-- - Extents Scanned..............................: 6426

-- - Extent Switches..............................: 7975

-- - Avg. Pages per Extent........................: 8.0

-- - Scan Density [Best Count:Actual Count].......: 80.13% [6391:7976]

-- - Logical Scan Fragmentation ..................: 1.98%

-- - Extent Scan Fragmentation ...................: 12.28%

-- - Avg. Bytes Free per Page.....................: 331.8

-- - Avg. Page Density (full).....................: 95.90%

-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- dbcc dbreindex (krdlypch,'',80)

-- dbcc showcontig (krdlypch)

-- DBCC SHOWCONTIG scanning 'KRDLYPCH' table...

-- Table: 'KRDLYPCH' (1124915079); index ID: 1, database ID: 21

-- TABLE level scan performed.

-- - Pages Scanned................................: 61256

-- - Extents Scanned..............................: 7688

-- - Extent Switches..............................: 7687

-- - Avg. Pages per Extent........................: 8.0

-- - Scan Density [Best Count:Actual Count].......: 99.60% [7657:7688]

-- - Logical Scan Fragmentation ..................: 0.00%

-- - Extent Scan Fragmentation ...................: 5.76%

-- - Avg. Bytes Free per Page.....................: 1616.1

-- - Avg. Page Density (full).....................: 80.03%

-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- dbcc dbreindex (krdlypch,'',95)

-- dbcc showcontig (krdlypch)

-- DBCC SHOWCONTIG scanning 'KRDLYPCH' table...

-- Table: 'KRDLYPCH' (1124915079); index ID: 1, database ID: 21

-- TABLE level scan performed.

-- - Pages Scanned................................: 50118

-- - Extents Scanned..............................: 6290

-- - Extent Switches..............................: 6289

-- - Avg. Pages per Extent........................: 8.0

-- - Scan Density [Best Count:Actual Count].......: 99.60% [6265:6290]

-- - Logical Scan Fragmentation ..................: 0.00%

-- - Extent Scan Fragmentation ...................: 1.56%

-- - Avg. Bytes Free per Page.....................: 176.0

-- - Avg. Page Density (full).....................: 97.83%

-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You'll notice the difference in page full density in line with the commands that I ran. You'll also notice that at 95%, my pages was 50118, as opposed to 61256 at 80%. This equates into faster reads and slower writes (depending on page splits and system activity).

Hope that helps.

Tom

W R Mr or Mrs. 500 Points: 523 More actions · Answer 5

Thank you for your clear explanation.

Whatever fill factor I use, the results are the same. I am guessing since the table has a million rows, there is nothing much that can be done except for purging some rows somewhere else.

dbcc dbreindex (xxx, '', 80)

dbcc showcontig(xxx)

TABLE level scan performed.

- Pages Scanned................................: 3084

- Extents Scanned..............................: 389

- Extent Switches..............................: 388

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.23% [386:389]

- Extent Scan Fragmentation ...................: 80.98%

- Avg. Bytes Free per Page.....................: 387.2

- Avg. Page Density (full).....................: 95.22%

dbcc dbreindex (xxx, '', 95)

dbcc showcontig(xxx)

TABLE level scan performed.

- Pages Scanned................................: 3084

- Extents Scanned..............................: 389

- Extent Switches..............................: 388

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.23% [386:389]

- Extent Scan Fragmentation ...................: 80.98%

- Avg. Bytes Free per Page.....................: 387.2

- Avg. Page Density (full).....................: 95.22%

dbcc dbreindex (xxx, '', 50)

dbcc showcontig(xxx)

TABLE level scan performed.

- Pages Scanned................................: 3084

- Extents Scanned..............................: 389

- Extent Switches..............................: 388

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.23% [386:389]

- Extent Scan Fragmentation ...................: 80.98%

- Avg. Bytes Free per Page.....................: 387.2

- Avg. Page Density (full).....................: 95.22%

Thanks.

-W R

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 6

Are you using sql 2000 or 7.0? Is there a clustered index on the table? If not that is probably your problem. It needs to be a clustered index. The new fill factor should be reset reguardless of whether you delete any of your data.

Tom

W R Mr or Mrs. 500 Points: 523 More actions · Answer 7

Thank you for your observations.

It is 2000. There was no clustered index. I dropped the index and created a clustered index. Ran dbcc contig.

Results are much better.

TABLE level scan performed.

- Pages Scanned................................: 3865

- Extents Scanned..............................: 485

- Extent Switches..............................: 484

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.79% [484:485]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 12.58%

- Avg. Bytes Free per Page.....................: 806.8

- Avg. Page Density (full).....................: 90.03%

But, the query time is same when it was a nonclustered index and now when it is a clustered index.

Is this normal.

-W R

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 8

What is the query time. Do some tests. Set your fill factor to something super low. 20%. Execute your query. Then set it back to 90. You'll notice a time change if your doing table/index scans in your query, even a select * from table, will be faster. The reason you're not seeing any performance gains is the fact that the number of extents scanned essentially remained unchanged from the heap to the clustered index.

original- Scan Density [Best Count:Actual Count].......: 99.23% [386:389]

new-- Scan Density [Best Count:Actual Count].......: 99.79% [484:485]

Test out a couple of different fill factors and you'll notice the importance of fill factor and scan density's.

Tom

W R Mr or Mrs. 500 Points: 523 More actions · Answer 9

In the prod box, the time is 11 seconds. On the same database, moved to dev, it is 3 seconds, with non-clustered or clustered. It might be something else that is slowing down the query in prod which might be at the server level.

Thanks. Your observations are very helpful.

-W R

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 10

Are there any other indexes on the prod box. Is it the same class/type machine. How fragmented is the actual database files. When you do a recovery everything is laid out contigually on disk. If the prod database has continuously grown from a small db, it's files could be laid all over on the disk. Is the showcontig from production or dev?

Tom

W R Mr or Mrs. 500 Points: 523 More actions · Answer 11

Good questions.

dbcc showcontig on dev

TABLE level scan performed.

- Pages Scanned................................: 4033

- Extents Scanned..............................: 508

- Extent Switches..............................: 507

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.41% [505:508]

- Extent Scan Fragmentation ...................: 83.86%

- Avg. Bytes Free per Page.....................: 387.9

- Avg. Page Density (full).....................: 95.21%

dbcc showcontig on prod

TABLE level scan performed.

- Pages Scanned................................: 4037

- Extents Scanned..............................: 508

- Extent Switches..............................: 507

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.41% [505:508]

- Extent Scan Fragmentation ...................: 83.86%

- Avg. Bytes Free per Page.....................: 388.9

- Avg. Page Density (full).....................: 95.20%

Thank you.

-W R

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 12

Are these two machines the same hardware? Nic cards the same speed? Have you terminaled in and ran the query local to see if speed is the same? Do other indexes need to be added to speed queries up?

Tom

W R Mr or Mrs. 500 Points: 523 More actions · Answer 13

The 2 m/c's have the same hardware. The Nic cards are of the same speed. I did a rdp into both the servers. I run the query on the boxes. Is this considered local execution? The two databases are the same so indexes should be the same too. I can look into add new indexes but that does not get to the query response difference between the two boxes.

Thanks for your persistant try.

-Jeelani

Minaz Amin SSChampion Points: 11052 More actions · Answer 14

Hi,

Run the and check in the profiler trace for query and check any * Blocking * related issue on the prod box.

Check if parallelism is used, try a non-parallel plan by using the OPTION (MAXDOP 1) hint.
Compare the execution plan of both the box.
Clean the procedure cache DBCC FREEPROCCACHE of the prod box may be ineffecient execution plan is excuted each time you ran the query

Please check the BOL for more information:

Query Tuning

Query Tuning Recommendations

Minaz Amin

"More Green More Oxygen !! Plant a tree today"