Rebuild index issue - - strange - Microsoft SQL Server

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 16 '06 #1

Subscribe Reply

2328

GlennThomas5

if you look at your database ID's, they are different. did you run
this on 2 different databases?

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 19 '06 #2

Similar topics

4747

How to identify fragmented index for rebuild

by: xo55ox | last post by:

Hi, I want to find out what is a good way to identify indexes that need rebuilding. I tried to run DBCC showcontig and identify them based on the scan density %. And according to a Microsoft Tech Net - All About SQL Server Indexes discussion, the expert stated that based on the Density (below 20%), I can pick out those indexes associated to...

Microsoft SQL Server

9412

Problem with Rebuild Index after loading data with DSN1COPY

by: Richard | last post by:

I have been asked to copy a handfull of table spaces in to new identical table spaces so that they can be accessed with out running into the nightly batch update. I first did a FULLCOPY with SHARELEVEL REFERENCE of the table space. Then I copied the DDL used to create the original object and changed the table space name, table name, index...

DB2 Database

5422

type of array index?

by: shmartonak | last post by:

For maximum portability what should the type of an array index be? Can any integer type be used safely? Or should I only use an unsigned type? Or what? If I'm using pointers to access array elements as *(mptr+k) where I've declared MYTYPE *mptr; what should be the type of 'k'? Should it be ptrdiff_t?

C / C++

2187

gist index build produces corrupt result on first access totable.

by: Eric Davies | last post by:

We've implemented a 5D box data type and have implemented both RTree and GiST access methods under PostgresSQL 7.4 and PostgresSQL 7.4.1. The 5D box internally looks like: struct Box5D{ float minBounds; float maxBounds; }; and so takes up 40 bytes and is of fixed length. The GiST access methods are basically a generalization of the 2D box...

PostgreSQL Database

8864

SQL2005 Rebuild Index Not Working

by: wackyphill | last post by:

After rebuilding an index, it still shows as the same amount of fragmentation. ANy ideas what's wrong? I'm determining which indexes to rebuild using the following query: SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,

Microsoft SQL Server

1832

Strange "ambiguous in namespace" complier error disappears with rebuild

by: John Kotuby | last post by:

Hi all. I am using VS 2005 and VB.NET. Lately as my Web Application is getting larger, I have been getting strange compiler messages like the following: --------------------------- Compiler Error Message: BC30560: 'controls_user_createquicksearchbar_ascx' is ambiguous in the namespace 'ASP'. Source Error:

ASP.NET

1543

Rebuild Index Fails, need insight

by: digitalox | last post by:

All efforts to rebuild indexes fails. Not with an error, but they still show high fragmentation. The environment: SQL Server 2005 in 2000 compatibility mode non-clustered indexes on tables that have clustered indexes with unique constraint Tried so far: Rebuild, reorganize, manual drop and recreate, setting the db in 2005 mode This...

Microsoft SQL Server

7163

Disable and Rebuild or Drop and recreate Index

by: dsdevonsomer | last post by:

Hello all, I am new to managing indexes on large tables and need some help. Hopefully, I am not repeating question here. I searched as much as I can, but not finding relatively best answer.. Here is my scenario. I have 2 tables with more than 4 mil rows (1 - 2.8 Mil, 2 2.1 mil). Of these tables, there are about 25 ( select ) queries run to...

Microsoft SQL Server

17443

Script to Rebuild the indexes that really needs to be rebuild .

by: debasisdas | last post by:

This code genenates a script to rebuld all the indexes with height greater than 3 for all the users except for SYS user. The script needs to be executed from SYS schema. This can also be executed from individual schemas by minor changes. If you need to exclude indxes of some other users that can also be added/changed in cGetIdx cursor. To execute...

Oracle Database

7478

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...

General

7410

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...

Windows Server

7668

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

7437

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...

Windows Server

7773

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...

General

4960

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...

C# / C Sharp

3466

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...

Networking - Hardware / Configuration

3448

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

1025

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