June 27, 2002 at 10:26 am
I have been working on the performance tuning
on
our servers and I am looking into data fragmentation using
the
DBCC Showcontig command and found some puzzling results. I was using
the
following command to reindex a table and the command is:
DBCC DBREINDEX ('F57515, ' ', 95)
I did a before and after using the DBCC showcontig to see how the
fragmentation statistics were changed: Here is the result.
Before the dbreindex query
Table: 'F57515' (1636252934); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 47603
- Extents Scanned..............................: 6001
- Extent Switches..............................: 8428
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 70.60% [5951:8429]
- Logical Scan Fragmentation ..................: 17.39%
- Extent Scan Fragmentation ...................: 29.56%
- Avg. Bytes Free per Page.....................: 244.7
- Avg. Page Density (full).....................: 96.98%
DBCC SHOWCONTIG scanning 'F57515' table...
Table: 'F57515' (1636252934); index ID: 2, database ID: 11
LEAF level scan performed.
- Pages Scanned................................: 16834
- Extents Scanned..............................: 2140
- Extent Switches..............................: 2832
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 74.30% [2105:2833]
- Logical Scan Fragmentation ..................: 2.70%
- Extent Scan Fragmentation ...................: 5.14%
- Avg. Bytes Free per Page.....................: 232.8
- Avg. Page Density (full).....................: 97.12%
After the dereindex query,
Table: 'F57515' (1636252934); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 48065
- Extents Scanned..............................: 6051
- Extent Switches..............................: 6050
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.31% [6009:6051]
- Logical Scan Fragmentation ..................: 3.16%
- Extent Scan Fragmentation ...................: 40.60%
- Avg. Bytes Free per Page.....................: 320.1
- Avg. Page Density (full).....................: 96.04%
DBCC SHOWCONTIG scanning 'F57515' table...
Table: 'F57515' (1636252934); index ID: 2, database ID: 11
LEAF level scan performed.
- Pages Scanned................................: 17076
- Extents Scanned..............................: 2165
- Extent Switches..............................: 2164
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.61% [2135:2165]
- Logical Scan Fragmentation ..................: 12.66%
- Extent Scan Fragmentation ...................: 18.20%
- Avg. Bytes Free per Page.....................: 344.3
- Avg. Page Density (full).....................: 95.75%
Index ID 1 is a cluster index which contain three fields, numeric,
float,
and char(30) fields.
Index ID 2 has five fields, float, char(25), float, float, char(30).
As you can see after the DBReindex, the Logical Scan Fragmentation was
improved but the Extent Scan Fragmentation went worse on Index ID 1.
For
the index ID 2, both Logical and Extent Scan Fragmentation went worse.
Why is that?
Thanks in advance for any help on this.
Wingman
July 12, 2002 at 9:48 pm
Could need some extra cleanup. Try sp_updatestats and DBCC UPDATEUSAGE, then DBCC DBREINDEX, and finally try shrink the database. Then run SHOWCONTIG and see if changes. Also what version of SQL and Serivce Pach level are you running?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 15, 2002 at 8:03 am
Sorry, I forgot the most important details. We are using SQL 2000 SP1. The database has been set for the 'Auto update Statistics' to be on and Auto Shrink on during the weekend and off during the week day for over four months. With that in mind, do I still do what you suggest.
quote:
Could need some extra cleanup. Try sp_updatestats and DBCC UPDATEUSAGE, then DBCC DBREINDEX, and finally try shrink the database. Then run SHOWCONTIG and see if changes. Also what version of SQL and Serivce Pach level are you running?"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 15, 2002 at 8:31 am
Turning Auto Update Stats and Suto Shrink on and off does not always perform the action as expected, especially Auto update stats as it is based on percentage of change which is monitiored I believe only while set on.
It is better to do these by code or hand and leave Auto Shirnk off and Auto update stats on (leaving off is ok for extremely large databases).
Try my suggestions to be sure these are not issues in you situations.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 15, 2002 at 10:25 am
Ok, I will do what you suggested. DBCC updateusage allows me to do it per table basis but not for sp_updatestats. Our database is at about 56 Gbytes and we are a 7x24 shop. Will I have any performance or locking issues if I run sp_updatestats in our live database.
In addition, do you think that I should schedule the DBCC updateuage and sp_updatestats once a month in addition to the auto update statisics? Are there any other SQL commands I should schedule.
Wing
July 15, 2002 at 11:22 am
Depends on you situation and yes you can see blocking. Look at each in SQL BOL and they will tell you. UPDATE STATISTICS stats can be used in place of sp_updatestates but you will have to specify the table. I personally update mine once a week but you need to guage for your system to get the proper reoccurrance.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply