Rebuild index issue - - strange

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 ***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: '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 ***

Really Need Help In Composite Index

Hi folks, i need an advise.

I've a gerand table customers_orders table with customer_id and order_id.
Whenever we have to find orders, for customer, this table is involved. Hey; i know u'll be angry y the heck this gerand exist but i've to blame the older dudes then.
Now this table has composite clustered index; CUSTOMER_ID+ORDER_ID.
The tables have grown over GB size; i see HASH INNER JOIN rather than MERGE for the GEREND and CUSTOMER table join.

Is it good to use composite clustered index; or should i clustered one the columns in the GEREND and other to normal index. What performance impact it could be.

Howdy!i think you will also need a separate index on order_id alone, if you're joining this table to both the customer table via customer_id and the order table via order_id, and you want orders for more than one customer (e.g. find all customers who have more than $50000 in orders)

joining to the customer table should use the clustered composite index, but joining to the order table would not, so a separate index is necessary|||Thanx, sir, for ur help. It has converted to MERGE join now.

Howdy!

Real Simple Quetion - I think!

Hi Folks!
I am trying to use 1 computer running XP home networked to a machine using
XP Proand SQL Server (Desktop Edition from Developer) on 1 other computer.
My MSACESS ADP refuses to see the SQL server even though I can do file
shares/printer shares between the two. Is XP home or Desktop SQL incapable
of this? I really don't want to spend an exasperating weekend if its
futile!!
Thanks for youe help and advice,,,
JimHi
Try comnection with the IP address, and not the machine name.
Also, if you are running Windows XP SP2:
SQL 2000 and XP SP2:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_7eur.asp
Regards
Mike
"JimD" wrote:
> Hi Folks!
> I am trying to use 1 computer running XP home networked to a machine using
> XP Proand SQL Server (Desktop Edition from Developer) on 1 other computer.
> My MSACESS ADP refuses to see the SQL server even though I can do file
> shares/printer shares between the two. Is XP home or Desktop SQL incapable
> of this? I really don't want to spend an exasperating weekend if its
> futile!!
> Thanks for youe help and advice,,,
> Jim
>
>|||Hi,
If you have the XP SP2 firewall running then it may be blocking traffic to
SQL Server on port 1433.
It will by default. Take a look in the firewall.log file when you try to
connect...
"JimD" <jimd4115@.verizon.net> wrote in message
news:HcI5e.1264$1p4.1219@.trndny06...
> Hi Folks!
> I am trying to use 1 computer running XP home networked to a machine using
> XP Proand SQL Server (Desktop Edition from Developer) on 1 other computer.
> My MSACESS ADP refuses to see the SQL server even though I can do file
> shares/printer shares between the two. Is XP home or Desktop SQL incapable
> of this? I really don't want to spend an exasperating weekend if its
> futile!!
> Thanks for youe help and advice,,,
> Jim
>|||Hi And Thank You!
I have firewall completely shut off on both machines and Norton AutoProtect
shutoff as well on both. I have TCP/IP ports set at 1433 on both as well. I
need to connect with the machine name as the app is written that way. Could
it possibly be something to do with accounts? I've tried Guest both on and
off to no avail on the marching running SQL Developer (Desktop) Edition. I
have tried for the last 12 hours and cannot even get Enterprise Manager to
connect. I'm going crazy and this is business critical. If anyone can
suggest anything else I'm, all ears...I need this connectivity by Monday
morning or I'm in big trouble...I've tried everything I can thing of...
Thanks so much...
Jim
jimd@.aries.com
"Mercury" <me@.spam.com> wrote in message
news:d38h1r$nn8$1@.lust.ihug.co.nz...
> Hi,
> If you have the XP SP2 firewall running then it may be blocking traffic to
> SQL Server on port 1433.
> It will by default. Take a look in the firewall.log file when you try to
> connect...
>
> "JimD" <jimd4115@.verizon.net> wrote in message
> news:HcI5e.1264$1p4.1219@.trndny06...
>> Hi Folks!
>> I am trying to use 1 computer running XP home networked to a machine
>> using
>> XP Proand SQL Server (Desktop Edition from Developer) on 1 other
>> computer.
>> My MSACESS ADP refuses to see the SQL server even though I can do file
>> shares/printer shares between the two. Is XP home or Desktop SQL
>> incapable
>> of this? I really don't want to spend an exasperating weekend if its
>> futile!!
>> Thanks for youe help and advice,,,
>> Jim
>>
>|||JimD wrote:
> I've tried Guest both on and off to no avail on the
> marching running SQL Developer (Desktop) Edition. I have tried for
> the last 12 hours and cannot even get Enterprise Manager to connect.
>
I'm confused. Do you have a SQL account to log in to the server? It
sounds like you may not know a valid account to use. Can you connect
using the IP address?
--
David Gugick
Imceda Software
www.imceda.com|||look at the following MS KB Article:
http://support.microsoft.com/kb/319930/EN-US/
and you may want to look at the following too:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q325003
Good luck!
"JimD" <jimd4115@.verizon.net> wrote in message
news:Kh06e.1815$4v3.304@.trndny03...
> Hi And Thank You!
> I have firewall completely shut off on both machines and Norton
AutoProtect
> shutoff as well on both. I have TCP/IP ports set at 1433 on both as well.
I
> need to connect with the machine name as the app is written that way.
Could
> it possibly be something to do with accounts? I've tried Guest both on and
> off to no avail on the marching running SQL Developer (Desktop) Edition. I
> have tried for the last 12 hours and cannot even get Enterprise Manager to
> connect. I'm going crazy and this is business critical. If anyone can
> suggest anything else I'm, all ears...I need this connectivity by Monday
> morning or I'm in big trouble...I've tried everything I can thing of...
> Thanks so much...
> Jim
> jimd@.aries.com
> "Mercury" <me@.spam.com> wrote in message
> news:d38h1r$nn8$1@.lust.ihug.co.nz...
> > Hi,
> >
> > If you have the XP SP2 firewall running then it may be blocking traffic
to
> > SQL Server on port 1433.
> > It will by default. Take a look in the firewall.log file when you try to
> > connect...
> >
> >
> > "JimD" <jimd4115@.verizon.net> wrote in message
> > news:HcI5e.1264$1p4.1219@.trndny06...
> >> Hi Folks!
> >>
> >> I am trying to use 1 computer running XP home networked to a machine
> >> using
> >> XP Proand SQL Server (Desktop Edition from Developer) on 1 other
> >> computer.
> >> My MSACESS ADP refuses to see the SQL server even though I can do file
> >> shares/printer shares between the two. Is XP home or Desktop SQL
> >> incapable
> >> of this? I really don't want to spend an exasperating weekend if its
> >> futile!!
> >>
> >> Thanks for youe help and advice,,,
> >> Jim
> >>
> >>
> >
> >
>|||You should be able to find odbcping on the bin folder for sql server. lookup
BOL for info on how to use it.
(I didn't see it mentioned in the above links - but I could be blind).
"JimD" <jimd4115@.verizon.net> wrote in message
news:Kh06e.1815$4v3.304@.trndny03...
> Hi And Thank You!
> I have firewall completely shut off on both machines and Norton
> AutoProtect shutoff as well on both. I have TCP/IP ports set at 1433 on
> both as well. I need to connect with the machine name as the app is
> written that way. Could it possibly be something to do with accounts? I've
> tried Guest both on and off to no avail on the marching running SQL
> Developer (Desktop) Edition. I have tried for the last 12 hours and cannot
> even get Enterprise Manager to connect. I'm going crazy and this is
> business critical. If anyone can suggest anything else I'm, all ears...I
> need this connectivity by Monday morning or I'm in big trouble...I've
> tried everything I can thing of...
> Thanks so much...
> Jim
> jimd@.aries.com
> "Mercury" <me@.spam.com> wrote in message
> news:d38h1r$nn8$1@.lust.ihug.co.nz...
>> Hi,
>> If you have the XP SP2 firewall running then it may be blocking traffic
>> to SQL Server on port 1433.
>> It will by default. Take a look in the firewall.log file when you try to
>> connect...
>>
>> "JimD" <jimd4115@.verizon.net> wrote in message
>> news:HcI5e.1264$1p4.1219@.trndny06...
>> Hi Folks!
>> I am trying to use 1 computer running XP home networked to a machine
>> using
>> XP Proand SQL Server (Desktop Edition from Developer) on 1 other
>> computer.
>> My MSACESS ADP refuses to see the SQL server even though I can do file
>> shares/printer shares between the two. Is XP home or Desktop SQL
>> incapable
>> of this? I really don't want to spend an exasperating weekend if its
>> futile!!
>> Thanks for youe help and advice,,,
>> Jim
>>
>>
>|||Hope this helps.
Search for the hosts file in your machine. you edit it with your notepad and
enter the machine name together with its IP address.
The downside of this is when your machine doesn't have a static IP, an
example of this is when it is getting its IP address from DHCP server, unless
you configure the DHCP server to reserve a certain IP address for the
machine, the IP address your machine will get may vary and you may need to
edit your hosts file everytime the IP address changes.
You may want to use DNS server in your network.
"JimD" wrote:
> Hi And Thank You!
> I have firewall completely shut off on both machines and Norton AutoProtect
> shutoff as well on both. I have TCP/IP ports set at 1433 on both as well. I
> need to connect with the machine name as the app is written that way. Could
> it possibly be something to do with accounts? I've tried Guest both on and
> off to no avail on the marching running SQL Developer (Desktop) Edition. I
> have tried for the last 12 hours and cannot even get Enterprise Manager to
> connect. I'm going crazy and this is business critical. If anyone can
> suggest anything else I'm, all ears...I need this connectivity by Monday
> morning or I'm in big trouble...I've tried everything I can thing of...
> Thanks so much...
> Jim
> jimd@.aries.com
> "Mercury" <me@.spam.com> wrote in message
> news:d38h1r$nn8$1@.lust.ihug.co.nz...
> > Hi,
> >
> > If you have the XP SP2 firewall running then it may be blocking traffic to
> > SQL Server on port 1433.
> > It will by default. Take a look in the firewall.log file when you try to
> > connect...
> >
> >
> > "JimD" <jimd4115@.verizon.net> wrote in message
> > news:HcI5e.1264$1p4.1219@.trndny06...
> >> Hi Folks!
> >>
> >> I am trying to use 1 computer running XP home networked to a machine
> >> using
> >> XP Proand SQL Server (Desktop Edition from Developer) on 1 other
> >> computer.
> >> My MSACESS ADP refuses to see the SQL server even though I can do file
> >> shares/printer shares between the two. Is XP home or Desktop SQL
> >> incapable
> >> of this? I really don't want to spend an exasperating weekend if its
> >> futile!!
> >>
> >> Thanks for youe help and advice,,,
> >> Jim
> >>
> >>
> >
> >
>
>|||Are you using MSDE SP3a? If so, network access is turned off by default for
security reasons (slammer is one of the reasons). You'll need to turn it
back on so you can access your MSDE from a remote machine via the network.
joe.
"JimD" <jimd4115@.verizon.net> wrote in message
news:Kh06e.1815$4v3.304@.trndny03...
> Hi And Thank You!
> I have firewall completely shut off on both machines and Norton
AutoProtect
> shutoff as well on both. I have TCP/IP ports set at 1433 on both as well.
I
> need to connect with the machine name as the app is written that way.
Could
> it possibly be something to do with accounts? I've tried Guest both on and
> off to no avail on the marching running SQL Developer (Desktop) Edition. I
> have tried for the last 12 hours and cannot even get Enterprise Manager to
> connect. I'm going crazy and this is business critical. If anyone can
> suggest anything else I'm, all ears...I need this connectivity by Monday
> morning or I'm in big trouble...I've tried everything I can thing of...
> Thanks so much...
> Jim
> jimd@.aries.com
> "Mercury" <me@.spam.com> wrote in message
> news:d38h1r$nn8$1@.lust.ihug.co.nz...
> > Hi,
> >
> > If you have the XP SP2 firewall running then it may be blocking traffic
to
> > SQL Server on port 1433.
> > It will by default. Take a look in the firewall.log file when you try to
> > connect...
> >
> >
> > "JimD" <jimd4115@.verizon.net> wrote in message
> > news:HcI5e.1264$1p4.1219@.trndny06...
> >> Hi Folks!
> >>
> >> I am trying to use 1 computer running XP home networked to a machine
> >> using
> >> XP Proand SQL Server (Desktop Edition from Developer) on 1 other
> >> computer.
> >> My MSACESS ADP refuses to see the SQL server even though I can do file
> >> shares/printer shares between the two. Is XP home or Desktop SQL
> >> incapable
> >> of this? I really don't want to spend an exasperating weekend if its
> >> futile!!
> >>
> >> Thanks for youe help and advice,,,
> >> Jim
> >>
> >>
> >
> >
>