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

Raziq Shekha

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: 'PlannedTransfe rArchive' (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 'PlannedTransfe rArchive' table...
Table: 'PlannedTransfe rArchive' (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

Subscribe Reply

8064

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: 'PlannedTransfe rArchive' (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 'PlannedTransfe rArchive' table...
Table: 'PlannedTransfe rArchive' (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

Erland Sommarskog

Raziq Shekha (ra**********@a nadarko.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: 'PlannedTransfe rArchive' (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 'PlannedTransfe rArchive' table...
Table: 'PlannedTransfe rArchive' (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****@sommarsk og.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

Raziq Shekha

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

Similar topics

1713

I dont understand..

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 my books that requires a view? I can add a view to my project but i dont see where i add a frame and doc? Or can i add a view to a normal dialog box? (I dont think i can do this but if i could it would be handy Im using visual c++ 6.0...

.NET Framework

1594

Dont understand why this does not work

by: Mark Goldin | last post by:

Very simplified: namespace humanres { public class main2 : System.Web.UI.Page {

ASP.NET

1926

i dont understand the undefind index error in my code.can check for me?

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" action="search.php"> <input type="text" name="search" size=25 maxlength=25> <input type="Submit" name="Submit" value="Submit"> </form> <? //connect to server mysql_pconnect("localhost","root","password");

PHP

1835

I dont understand this!!

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 then (one at a time) from SandBox" and to turn in only one sandbox and one kittykitty, how if there are 6 assignments??? I dont understand!!!

Java

2264

i dont understand why this wont run

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); error_reporting (E_ALL & ~E_NOTICE); // Connect and select.

PHP

2341

i write code but dont understand the error please help me

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 ;

C / C++

1484

Newbie - Dont' understand << 8

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 but I don't understand what the << 8 does.

C# / C Sharp

1739

i dont understand the use of cin

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 <iostream.h> int main (void) { int day, month, year; char ch; cout << "Input a date as dd/mm/yy: "; cin >> day >> ch >> month >> ch >> year;

C / C++

2448

redirect to other page if id is missing!!

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 change the id by hand to id that is not exist ,then all the page is getting a big mess because there is no data to show!!

PHP

9706

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9579

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10330

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10076

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6851

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5651

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4297

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3816

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2990

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General