473,378 Members | 1,470 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

dbcc reindex issue - - I don't understand!!

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned................................: 184867
- Extents Scanned..............................: 23203
- Extent Switches..............................: 23324
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..................: 11.13%
- Extent Scan Fragmentation ...................: 35.46%
- Avg. Bytes Free per Page.....................: 60.0
- Avg. Page Density (full).....................: 99.26%
Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned................................: 303177
- Extents Scanned..............................: 37964
- Extent Switches..............................: 42579
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..................: 43.19%
- Extent Scan Fragmentation ...................: 24.78%
- Avg. Bytes Free per Page.....................: 75.1
- Avg. Page Density (full).....................: 99.07%
Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Jun 26 '06 #1
3 8046
Stu
Did you not already post this message before, or am i experiencing deja
vu?

Your database id's are different, which means that you ran the
showcontig command on a different database. Did you make a backup and
restore it?

Raziq Shekha wrote:
Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned................................: 184867
- Extents Scanned..............................: 23203
- Extent Switches..............................: 23324
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..................: 11.13%
- Extent Scan Fragmentation ...................: 35.46%
- Avg. Bytes Free per Page.....................: 60.0
- Avg. Page Density (full).....................: 99.26%
Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned................................: 303177
- Extents Scanned..............................: 37964
- Extent Switches..............................: 42579
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..................: 43.19%
- Extent Scan Fragmentation ...................: 24.78%
- Avg. Bytes Free per Page.....................: 75.1
- Avg. Page Density (full).....................: 99.07%
Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***


Jun 26 '06 #2
Raziq Shekha (ra**********@anadarko.com) writes:
for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned................................: 184867
- Extents Scanned..............................: 23203
- Extent Switches..............................: 23324
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..................: 11.13%
- Extent Scan Fragmentation ...................: 35.46%
- Avg. Bytes Free per Page.....................: 60.0
- Avg. Page Density (full).....................: 99.26%
With this scan density, defragmentation may be no be very useful.
DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned................................: 303177


I've also seen this that the reserved space for the table increases
and almost double. My speculation have been that space is reserved
for future reindex operations, but I have not dug into it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 26 '06 #3
Yes, I did repost this because i did not get any answers the first time.
Yes, I did restore the backup of a database and created a new database.

*** Sent via Developersdex http://www.developersdex.com ***
Jun 26 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Merrua | last post by:
I dont understand how to do this I created a dialog based project and that is fine for most of my programming project, but one dialog box is going to generate a graph, and as far as i know from...
11
by: Mark Goldin | last post by:
Very simplified: namespace humanres { public class main2 : System.Web.UI.Page {
3
by: Sheau Wei | last post by:
This is the code the code that i try to run it as my search engine for my database. But i dont understand why i run a notice that undefind index. Can u help me ? <form method="post"...
5
by: ryann18 | last post by:
I was just wondering, my teacher assigned a project for me and told me to "create one cat in SandBox and put all the methods (for example method_1) you need for the excercises in KittyKitty and call...
19
by: so many sites so little time | last post by:
the table is head the colunm is called body <?php //show_site.php // This script retrieves blog entries from the database. // Address error handing. ini_set ('display_errors', 1);...
8
by: mohammaditraders | last post by:
#include <iostream.h> #include <stdlib.h> #include <conio.h> #include <string.h> class Matrix { private : int numRows, numCols ; int elements ;
6
by: Bishop | last post by:
a += (UInt32)(url + (url << 8) + (url << 16) + (url << 24)); I'm trying to understand what the above line of code does. I understand that the url part of (url << 8) returns part of the string...
5
by: dynamo | last post by:
In the following program i dont how cin knows where day, stops and ch ,starts. The same thing for month and year. When you type the input string without spaces.Can anyone help?thanks #include...
3
by: canabatz | last post by:
Hello all!! i got a small problem ,not big :) i got this example of a link in my site that display everything correctly! http://www.mysite.com/product.php?id=200 now my problem is : if...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.