index and key: perform

Showing posts with label perform. Show all posts

Friday, March 30, 2012

Index Table

Hi ,
I have created 3 fields in for my table , FieldA , B and C all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Do the performance is the same ?
Travis TanThe optimizer should create the same execution plan for both of these
statements therefore performance should be the same.
--Brian
(Please reply to the newsgroups only.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
> Hi ,
> I have created 3 fields in for my table , FieldA , B and C all asc
> order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
> Do the performance is the same ?
>
> --
> Travis Tan|||Brian ,
Sorry , What I means is I create an index in TableA with FieldA , B and C
all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Travis Tan
"Brian Lawton" wrote:

> The optimizer should create the same execution plan for both of these
> statements therefore performance should be the same.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
>
>|||Same answer:
>The optimizer should create the same execution plan for both of these
>statements therefore performance should be the same.
Uytkownik "Travis" <Travis@.discussions.microsoft.com> napisa w wiadomoci
news:48D82684-DF75-4740-BC4F-9604F33235A8@.microsoft.com...[vbcol=seagreen]
> Brian ,
> Sorry , What I means is I create an index in TableA with FieldA , B and
> C
> all asc order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
>
> Travis Tan
>
> "Brian Lawton" wrote:
>

Index Table

Hi ,
I have created 3 fields in for my table , FieldA , B and C all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Do the performance is the same ?
Travis Tan
The optimizer should create the same execution plan for both of these
statements therefore performance should be the same.
--Brian
(Please reply to the newsgroups only.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
> Hi ,
> I have created 3 fields in for my table , FieldA , B and C all asc
> order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
> Do the performance is the same ?
>
> --
> Travis Tan
|||Brian ,
Sorry , What I means is I create an index in TableA with FieldA , B and C
all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Travis Tan
"Brian Lawton" wrote:

> The optimizer should create the same execution plan for both of these
> statements therefore performance should be the same.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
>
>
|||Same answer:
>The optimizer should create the same execution plan for both of these
>statements therefore performance should be the same.
Uytkownik "Travis" <Travis@.discussions.microsoft.com> napisa w wiadomoci
news:48D82684-DF75-4740-BC4F-9604F33235A8@.microsoft.com...[vbcol=seagreen]
> Brian ,
> Sorry , What I means is I create an index in TableA with FieldA , B and
> C
> all asc order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
>
> Travis Tan
>
> "Brian Lawton" wrote:

Index Spool/Eager Spool

Hey Guys,
I have a good one!
What would cause the server to use a huge index but then perform a index
spool/eager spool action in the query plan and basically recreate the
whole index?
Thanks,
FrankThe query optimizer may have chosen a plan that requires the rows to be in a
different order for two different operations. For example, the optimizer
may choose to process the restrict condition (WHERE restrict_condition)
before processing the join condition (ON (join_condition)), where the index
used to process the restrict condition is in a different order than is
needed to process the join. If the execution plan contains an iteration
step, then the eager spool may be used to minimize the number of reads
required to satisfy the join. The SEEK:() predicate on the Index Spool
operator restricts the rows that are copied into tempdb, which minimizes the
number of reads required for lookups in each iteration.
"Frank W" <me@.frankwisniewski.net> wrote in message
news:8zhUe.245376$gL1.240016@.tornado.texas.rr.com...
> Hey Guys,
> I have a good one!
> What would cause the server to use a huge index but then perform a index
> spool/eager spool action in the query plan and basically recreate the
> whole index?
> Thanks,
> Frank

Wednesday, March 21, 2012

Index or not to index

I have a "Products" table, which currently only has the idProduct primary
key indexed. I now have a requirement to perform searches through an ASP
front end (ASP front end issuing SQL directly against SQL using SQL Server
OLE DB Provider) on
- a details text(16) field
- a description varchar(250) field
- a descriptionLong varchar(250) field
At any time, this table would have around 150-300 records, although records
do get added and deleted from time to time. I am considering Creating an
INDEX on details, description and descriptionLong field. However, I am
concerned
- whether creating an index on such a relatively small table would really
give performance gain or would it just add towards "inefficiency"
- if creating index is deemed a good idea, then what kind of index should I
use?
- Since records get added and deleted from time to time, would it be a good
idea to "re-index" the table (presumably, as part of the DB Maintenance
plan)?
Many thanks in advance!I think you shouldn't create an index because this index will be hardly used
but will decrease performance of DML commands execution
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:OjTbwYXQEHA.620@.TK2MSFTNGP10.phx.gbl...
> I have a "Products" table, which currently only has the idProduct primary
> key indexed. I now have a requirement to perform searches through an ASP
> front end (ASP front end issuing SQL directly against SQL using SQL Server
> OLE DB Provider) on
> - a details text(16) field
> - a description varchar(250) field
> - a descriptionLong varchar(250) field
> At any time, this table would have around 150-300 records, although
records
> do get added and deleted from time to time. I am considering Creating an
> INDEX on details, description and descriptionLong field. However, I am
> concerned
> - whether creating an index on such a relatively small table would really
> give performance gain or would it just add towards "inefficiency"
> - if creating index is deemed a good idea, then what kind of index should
I
> use?
> - Since records get added and deleted from time to time, would it be a
good
> idea to "re-index" the table (presumably, as part of the DB Maintenance
> plan)?
> Many thanks in advance!
>|||Definitelly not to index. It is too small, index fields are too large...|||Hi Patrick,
Alex Cieszinski and Bojidar Alexandrov has give you their suggestions

I
wanted to post a quick note to see if you would like additional assistance
or information regarding this particular issue.
We appreciate your patience and look forward to hearing from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Are you Michael or Mingqing after all

|||Hi Bojidar,
Michael is Mingqing

Thank you :D
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Index or not to index

I have a "Products" table, which currently only has the idProduct primary
key indexed. I now have a requirement to perform searches through an ASP
front end (ASP front end issuing SQL directly against SQL using SQL Server
OLE DB Provider) on
- a details text(16) field
- a description varchar(250) field
- a descriptionLong varchar(250) field
At any time, this table would have around 150-300 records, although records
do get added and deleted from time to time. I am considering Creating an
INDEX on details, description and descriptionLong field. However, I am
concerned
- whether creating an index on such a relatively small table would really
give performance gain or would it just add towards "inefficiency"
- if creating index is deemed a good idea, then what kind of index should I
use?
- Since records get added and deleted from time to time, would it be a good
idea to "re-index" the table (presumably, as part of the DB Maintenance
plan)?
Many thanks in advance!
I think you shouldn't create an index because this index will be hardly used
but will decrease performance of DML commands execution
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:OjTbwYXQEHA.620@.TK2MSFTNGP10.phx.gbl...
> I have a "Products" table, which currently only has the idProduct primary
> key indexed. I now have a requirement to perform searches through an ASP
> front end (ASP front end issuing SQL directly against SQL using SQL Server
> OLE DB Provider) on
> - a details text(16) field
> - a description varchar(250) field
> - a descriptionLong varchar(250) field
> At any time, this table would have around 150-300 records, although
records
> do get added and deleted from time to time. I am considering Creating an
> INDEX on details, description and descriptionLong field. However, I am
> concerned
> - whether creating an index on such a relatively small table would really
> give performance gain or would it just add towards "inefficiency"
> - if creating index is deemed a good idea, then what kind of index should
I
> use?
> - Since records get added and deleted from time to time, would it be a
good
> idea to "re-index" the table (presumably, as part of the DB Maintenance
> plan)?
> Many thanks in advance!
>
|||Definitelly not to index. It is too small, index fields are too large...
|||Hi Patrick,
Alex Cieszinski and Bojidar Alexandrov has give you their suggestions

I
wanted to post a quick note to see if you would like additional assistance
or information regarding this particular issue.
We appreciate your patience and look forward to hearing from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Are you Michael or Mingqing after all

|||Hi Bojidar,
Michael is Mingqing

Thank you :D
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql

Index or not to index

Wednesday, March 7, 2012

Index Error - Re-Post

Apologies for the re-post...
Hello,
We are running SQL 2000 Service Pack 3a.
We are getting the following error when we perform a load..
Error: 8646, Severity: 21, State: 1
The index entry for row ID was not found in index ID 5, of table 933578364,
in database 'MYDBNAME'.
The program that runs the load process is a Java program.
I have run a DBCC CHECK db on the database and it reported no errors.
I have also followed the instructions in
http://support.microsoft.com/kb/822747 however we do not have a unicode
field, and the collation is different.
We backed up the Database and put it onto a different Server and the error
occured again.
The table it points to has just over 1 million records.
Now here is the oddity, if I delete the indexes and put them back in I can
run the load with the same data and it works, but the error occurs after a
couple of loads.
Also if we back up the database and put it onto a completely different box
we get the same error.
So I do not think its a problem with the physical box so it must be
something to do with one of the non clustered indexes.
I am at a loss on how to proceed with this, so I was wondering if some
bright people could help.
Thanks
J
The schema for the table is as follows :-
CREATE TABLE [dbo].[PARCELS] (
[PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CONSIGNMENT_NO] [int] NULL ,
[RETL_CODE] [smallint] NULL ,
[PARCEL_TYPE] [tinyint] NOT NULL ,
[PARCEL_NO] [int] NULL ,
[BOX_NUMBER] [smallint] NULL ,
[BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[CREATED_AT] [bigint] NULL ,
[CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[SIZE] [int] NOT NULL ,
[WEIGHT] [int] NOT NULL ,
[CUSTOMER_CONSIGNMENT] [int] NULL ,
[LAST_TRACK_EVENT] [int] NULL ,
[HELD] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[PARCEL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] ADD
CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
GO
CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
GOHi
When you say you run the load does that mean you are adding/deleting a
significant amount of data? In which case dropping/recreating (or at least
defragging) the indexes would help performance.
The article you refer to was fixed in SP3a therefore would not be applicable.
Have you tried updating statistics
Also you may want to try running the system without the offending index.
John
"Julie" wrote:
> Apologies for the re-post...
> Hello,
> We are running SQL 2000 Service Pack 3a.
> We are getting the following error when we perform a load..
> Error: 8646, Severity: 21, State: 1
> The index entry for row ID was not found in index ID 5, of table 933578364,
> in database 'MYDBNAME'.
> The program that runs the load process is a Java program.
> I have run a DBCC CHECK db on the database and it reported no errors.
> I have also followed the instructions in
> http://support.microsoft.com/kb/822747 however we do not have a unicode
> field, and the collation is different.
> We backed up the Database and put it onto a different Server and the error
> occured again.
> The table it points to has just over 1 million records.
> Now here is the oddity, if I delete the indexes and put them back in I can
> run the load with the same data and it works, but the error occurs after a
> couple of loads.
> Also if we back up the database and put it onto a completely different box
> we get the same error.
> So I do not think its a problem with the physical box so it must be
> something to do with one of the non clustered indexes.
> I am at a loss on how to proceed with this, so I was wondering if some
> bright people could help.
>
> Thanks
> J
> The schema for the table is as follows :-
> CREATE TABLE [dbo].[PARCELS] (
> [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CONSIGNMENT_NO] [int] NULL ,
> [RETL_CODE] [smallint] NULL ,
> [PARCEL_TYPE] [tinyint] NOT NULL ,
> [PARCEL_NO] [int] NULL ,
> [BOX_NUMBER] [smallint] NULL ,
> [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> [CREATED_AT] [bigint] NULL ,
> [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [SIZE] [int] NOT NULL ,
> [WEIGHT] [int] NOT NULL ,
> [CUSTOMER_CONSIGNMENT] [int] NULL ,
> [LAST_TRACK_EVENT] [int] NULL ,
> [HELD] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> PRIMARY KEY CLUSTERED
> (
> [PARCEL_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] ADD
> CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> GO
> CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
> GO
>|||Thank you for you post.
It is more that we are updating rather than adding or deleting, however we
use a lot of joining for other tables to perform the update. The table itself
only contains about 1 million records.
We tried to simply remove the indexes however that caused major locking
problems so we had to put them back in. Therefore we cannot drop and recreate
them. We cannot determine which Index is causing the problem as the query
that references the table uses all of them.
Deleting the indexes and put them back in before the load seems to work once
or twice, then the same problem occurs.
We have also tried updating the statisitcs, however that did not work at all.
Thanks for you help
Julie
"John Bell" wrote:
> Hi
> When you say you run the load does that mean you are adding/deleting a
> significant amount of data? In which case dropping/recreating (or at least
> defragging) the indexes would help performance.
> The article you refer to was fixed in SP3a therefore would not be applicable.
> Have you tried updating statistics
> Also you may want to try running the system without the offending index.
> John
> "Julie" wrote:
> > Apologies for the re-post...
> >
> > Hello,
> > We are running SQL 2000 Service Pack 3a.
> >
> > We are getting the following error when we perform a load..
> >
> > Error: 8646, Severity: 21, State: 1
> > The index entry for row ID was not found in index ID 5, of table 933578364,
> > in database 'MYDBNAME'.
> >
> > The program that runs the load process is a Java program.
> >
> > I have run a DBCC CHECK db on the database and it reported no errors.
> >
> > I have also followed the instructions in
> > http://support.microsoft.com/kb/822747 however we do not have a unicode
> > field, and the collation is different.
> >
> > We backed up the Database and put it onto a different Server and the error
> > occured again.
> >
> > The table it points to has just over 1 million records.
> >
> > Now here is the oddity, if I delete the indexes and put them back in I can
> > run the load with the same data and it works, but the error occurs after a
> > couple of loads.
> >
> > Also if we back up the database and put it onto a completely different box
> > we get the same error.
> >
> > So I do not think its a problem with the physical box so it must be
> > something to do with one of the non clustered indexes.
> >
> > I am at a loss on how to proceed with this, so I was wondering if some
> > bright people could help.
> >
> >
> > Thanks
> > J
> >
> > The schema for the table is as follows :-
> > CREATE TABLE [dbo].[PARCELS] (
> > [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [CONSIGNMENT_NO] [int] NULL ,
> > [RETL_CODE] [smallint] NULL ,
> > [PARCEL_TYPE] [tinyint] NOT NULL ,
> > [PARCEL_NO] [int] NULL ,
> > [BOX_NUMBER] [smallint] NULL ,
> > [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> > [CREATED_AT] [bigint] NULL ,
> > [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> > [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> > [SIZE] [int] NOT NULL ,
> > [WEIGHT] [int] NOT NULL ,
> > [CUSTOMER_CONSIGNMENT] [int] NULL ,
> > [LAST_TRACK_EVENT] [int] NULL ,
> > [HELD] [bit] NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> > PRIMARY KEY CLUSTERED
> > (
> > [PARCEL_ID]
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PARCELS] ADD
> > CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> > GO
> >
> > CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
> > [PRIMARY]
> > GO
> >
> > CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
> > GO
> >|||Hi Julie
The index should be:
SELECT *
FROM sysindexes
WHERE id = 933578364
AND indid = 5
John
"Julie" wrote:
> Thank you for you post.
> It is more that we are updating rather than adding or deleting, however we
> use a lot of joining for other tables to perform the update. The table itself
> only contains about 1 million records.
> We tried to simply remove the indexes however that caused major locking
> problems so we had to put them back in. Therefore we cannot drop and recreate
> them. We cannot determine which Index is causing the problem as the query
> that references the table uses all of them.
> Deleting the indexes and put them back in before the load seems to work once
> or twice, then the same problem occurs.
> We have also tried updating the statisitcs, however that did not work at all.
> Thanks for you help
> Julie
>
> "John Bell" wrote:
> > Hi
> >
> > When you say you run the load does that mean you are adding/deleting a
> > significant amount of data? In which case dropping/recreating (or at least
> > defragging) the indexes would help performance.
> >
> > The article you refer to was fixed in SP3a therefore would not be applicable.
> >
> > Have you tried updating statistics
> >
> > Also you may want to try running the system without the offending index.
> >
> > John
> >
> > "Julie" wrote:
> >
> > > Apologies for the re-post...
> > >
> > > Hello,
> > > We are running SQL 2000 Service Pack 3a.
> > >
> > > We are getting the following error when we perform a load..
> > >
> > > Error: 8646, Severity: 21, State: 1
> > > The index entry for row ID was not found in index ID 5, of table 933578364,
> > > in database 'MYDBNAME'.
> > >
> > > The program that runs the load process is a Java program.
> > >
> > > I have run a DBCC CHECK db on the database and it reported no errors.
> > >
> > > I have also followed the instructions in
> > > http://support.microsoft.com/kb/822747 however we do not have a unicode
> > > field, and the collation is different.
> > >
> > > We backed up the Database and put it onto a different Server and the error
> > > occured again.
> > >
> > > The table it points to has just over 1 million records.
> > >
> > > Now here is the oddity, if I delete the indexes and put them back in I can
> > > run the load with the same data and it works, but the error occurs after a
> > > couple of loads.
> > >
> > > Also if we back up the database and put it onto a completely different box
> > > we get the same error.
> > >
> > > So I do not think its a problem with the physical box so it must be
> > > something to do with one of the non clustered indexes.
> > >
> > > I am at a loss on how to proceed with this, so I was wondering if some
> > > bright people could help.
> > >
> > >
> > > Thanks
> > > J
> > >
> > > The schema for the table is as follows :-
> > > CREATE TABLE [dbo].[PARCELS] (
> > > [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> > > [CONSIGNMENT_NO] [int] NULL ,
> > > [RETL_CODE] [smallint] NULL ,
> > > [PARCEL_TYPE] [tinyint] NOT NULL ,
> > > [PARCEL_NO] [int] NULL ,
> > > [BOX_NUMBER] [smallint] NULL ,
> > > [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> > > [CREATED_AT] [bigint] NULL ,
> > > [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> > > [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> > > [SIZE] [int] NOT NULL ,
> > > [WEIGHT] [int] NOT NULL ,
> > > [CUSTOMER_CONSIGNMENT] [int] NULL ,
> > > [LAST_TRACK_EVENT] [int] NULL ,
> > > [HELD] [bit] NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> > > PRIMARY KEY CLUSTERED
> > > (
> > > [PARCEL_ID]
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > ALTER TABLE [dbo].[PARCELS] ADD
> > > CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> > > GO
> > >
> > > CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
> > > [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON [PRIMARY]
> > > GO
> > >
> > > CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
> > > GO
> > >|||Thanks John
"John Bell" wrote:
> Hi Julie
> The index should be:
> SELECT *
> FROM sysindexes
> WHERE id = 933578364
> AND indid = 5
>
> John
> "Julie" wrote:
> > Thank you for you post.
> >
> > It is more that we are updating rather than adding or deleting, however we
> > use a lot of joining for other tables to perform the update. The table itself
> > only contains about 1 million records.
> >
> > We tried to simply remove the indexes however that caused major locking
> > problems so we had to put them back in. Therefore we cannot drop and recreate
> > them. We cannot determine which Index is causing the problem as the query
> > that references the table uses all of them.
> >
> > Deleting the indexes and put them back in before the load seems to work once
> > or twice, then the same problem occurs.
> >
> > We have also tried updating the statisitcs, however that did not work at all.
> >
> > Thanks for you help
> >
> > Julie
> >
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > When you say you run the load does that mean you are adding/deleting a
> > > significant amount of data? In which case dropping/recreating (or at least
> > > defragging) the indexes would help performance.
> > >
> > > The article you refer to was fixed in SP3a therefore would not be applicable.
> > >
> > > Have you tried updating statistics
> > >
> > > Also you may want to try running the system without the offending index.
> > >
> > > John
> > >
> > > "Julie" wrote:
> > >
> > > > Apologies for the re-post...
> > > >
> > > > Hello,
> > > > We are running SQL 2000 Service Pack 3a.
> > > >
> > > > We are getting the following error when we perform a load..
> > > >
> > > > Error: 8646, Severity: 21, State: 1
> > > > The index entry for row ID was not found in index ID 5, of table 933578364,
> > > > in database 'MYDBNAME'.
> > > >
> > > > The program that runs the load process is a Java program.
> > > >
> > > > I have run a DBCC CHECK db on the database and it reported no errors.
> > > >
> > > > I have also followed the instructions in
> > > > http://support.microsoft.com/kb/822747 however we do not have a unicode
> > > > field, and the collation is different.
> > > >
> > > > We backed up the Database and put it onto a different Server and the error
> > > > occured again.
> > > >
> > > > The table it points to has just over 1 million records.
> > > >
> > > > Now here is the oddity, if I delete the indexes and put them back in I can
> > > > run the load with the same data and it works, but the error occurs after a
> > > > couple of loads.
> > > >
> > > > Also if we back up the database and put it onto a completely different box
> > > > we get the same error.
> > > >
> > > > So I do not think its a problem with the physical box so it must be
> > > > something to do with one of the non clustered indexes.
> > > >
> > > > I am at a loss on how to proceed with this, so I was wondering if some
> > > > bright people could help.
> > > >
> > > >
> > > > Thanks
> > > > J
> > > >
> > > > The schema for the table is as follows :-
> > > > CREATE TABLE [dbo].[PARCELS] (
> > > > [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > [CONSIGNMENT_NO] [int] NULL ,
> > > > [RETL_CODE] [smallint] NULL ,
> > > > [PARCEL_TYPE] [tinyint] NOT NULL ,
> > > > [PARCEL_NO] [int] NULL ,
> > > > [BOX_NUMBER] [smallint] NULL ,
> > > > [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> > > > [CREATED_AT] [bigint] NULL ,
> > > > [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> > > > [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> > > > [SIZE] [int] NOT NULL ,
> > > > [WEIGHT] [int] NOT NULL ,
> > > > [CUSTOMER_CONSIGNMENT] [int] NULL ,
> > > > [LAST_TRACK_EVENT] [int] NULL ,
> > > > [HELD] [bit] NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> > > > PRIMARY KEY CLUSTERED
> > > > (
> > > > [PARCEL_ID]
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > ALTER TABLE [dbo].[PARCELS] ADD
> > > > CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> > > > GO
> > > >
> > > > CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
> > > > GO
> > > >
> > > > CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
> > > > [PRIMARY]
> > > > GO
> > > >
> > > > CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
> > > > GO
> > > >
> > > > CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
> > > > GO
> > > >
> > > > CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON [PRIMARY]
> > > > GO
> > > >
> > > > CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
> > > > GO
> > > >

Index Error

Hello,
We are running SQL 2000 Service Pack 3a.
We are getting the following error when we perform a load..
Error: 8646, Severity: 21, State: 1
The index entry for row ID was not found in index ID 5, of table 933578364,
in database 'MYDBNAME'.
I have run a DBCC CHECK db on the database and it reported no errors.
I have also followed the instructions in
http://support.microsoft.com/kb/822747 however we do not have a unicode
field, and the collation is different.
We backed up the Database and put it onto a different Server and the error
occured again.
The table it points to has just over 1 million records.
Now here is the oddity, if I delete the indexes and put them back in I can
run the load with the same data and it works.
I am at a loss on how to proceed with this, so I was wondering if some
bright people could help.
Thanks
J
The schema for the table is as follows :-
CREATE TABLE [dbo].[PARCELS] (
[PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CONSIGNMENT_NO] [int] NULL ,
[RETL_CODE] [smallint] NULL ,
[PARCEL_TYPE] [tinyint] NOT NULL ,
[PARCEL_NO] [int] NULL ,
[BOX_NUMBER] [smallint] NULL ,
[BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[CREATED_AT] [bigint] NULL ,
[CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[SIZE] [int] NOT NULL ,
[WEIGHT] [int] NOT NULL ,
[CUSTOMER_CONSIGNMENT] [int] NULL ,
[LAST_TRACK_EVENT] [int] NULL ,
[HELD] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[PARCEL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] ADD
CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
GO
CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
GOJulie,
Sounds like a questionable nonclustered index. Since the index doesn't
contain the actual data but pointers to the data the quick fix would
probably be to just drop the index and recreate it. If it occurs again, you
might check the disk (array) if DBCC CHECDB is not reporting anything.
HTH
Jerry
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:813D3A08-56FD-41A8-B307-C66963DCBC40@.microsoft.com...
> Hello,
> We are running SQL 2000 Service Pack 3a.
> We are getting the following error when we perform a load..
> Error: 8646, Severity: 21, State: 1
> The index entry for row ID was not found in index ID 5, of table
> 933578364,
> in database 'MYDBNAME'.
> I have run a DBCC CHECK db on the database and it reported no errors.
> I have also followed the instructions in
> http://support.microsoft.com/kb/822747 however we do not have a unicode
> field, and the collation is different.
> We backed up the Database and put it onto a different Server and the error
> occured again.
> The table it points to has just over 1 million records.
> Now here is the oddity, if I delete the indexes and put them back in I can
> run the load with the same data and it works.
> I am at a loss on how to proceed with this, so I was wondering if some
> bright people could help.
> Thanks
> J
> The schema for the table is as follows :-
> CREATE TABLE [dbo].[PARCELS] (
> [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CONSIGNMENT_NO] [int] NULL ,
> [RETL_CODE] [smallint] NULL ,
> [PARCEL_TYPE] [tinyint] NOT NULL ,
> [PARCEL_NO] [int] NULL ,
> [BOX_NUMBER] [smallint] NULL ,
> [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> [CREATED_AT] [bigint] NULL ,
> [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [SIZE] [int] NOT NULL ,
> [WEIGHT] [int] NOT NULL ,
> [CUSTOMER_CONSIGNMENT] [int] NULL ,
> [LAST_TRACK_EVENT] [int] NULL ,
> [HELD] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> PRIMARY KEY CLUSTERED
> (
> [PARCEL_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] ADD
> CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> GO
> CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
> GO
>
>

Index Error

Hello,
We are running SQL 2000 Service Pack 3a.
We are getting the following error when we perform a load..
Error: 8646, Severity: 21, State: 1
The index entry for row ID was not found in index ID 5, of table 933578364,
in database 'MYDBNAME'.
I have run a DBCC CHECK db on the database and it reported no errors.
I have also followed the instructions in
http://support.microsoft.com/kb/822747 however we do not have a unicode
field, and the collation is different.
We backed up the Database and put it onto a different Server and the error
occured again.
The table it points to has just over 1 million records.
Now here is the oddity, if I delete the indexes and put them back in I can
run the load with the same data and it works.
I am at a loss on how to proceed with this, so I was wondering if some
bright people could help.
Thanks
J
The schema for the table is as follows :-
CREATE TABLE [dbo].[PARCELS] (
[PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CONSIGNMENT_NO] [int] NULL ,
[RETL_CODE] [smallint] NULL ,
[PARCEL_TYPE] [tinyint] NOT NULL ,
[PARCEL_NO] [int] NULL ,
[BOX_NUMBER] [smallint] NULL ,
[BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[CREATED_AT] [bigint] NULL ,
[CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[SIZE] [int] NOT NULL ,
[WEIGHT] [int] NOT NULL ,
[CUSTOMER_CONSIGNMENT] [int] NULL ,
[LAST_TRACK_EVENT] [int] NULL ,
[HELD] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[PARCEL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] ADD
CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
GO
CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSI
GNMENT]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON &
#91;PRIMARY]
GO
CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO
]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE])
ON [PRIMARY]
GOJulie,
Sounds like a questionable nonclustered index. Since the index doesn't
contain the actual data but pointers to the data the quick fix would
probably be to just drop the index and recreate it. If it occurs again, you
might check the disk (array) if DBCC CHECDB is not reporting anything.
HTH
Jerry
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:813D3A08-56FD-41A8-B307-C66963DCBC40@.microsoft.com...
> Hello,
> We are running SQL 2000 Service Pack 3a.
> We are getting the following error when we perform a load..
> Error: 8646, Severity: 21, State: 1
> The index entry for row ID was not found in index ID 5, of table
> 933578364,
> in database 'MYDBNAME'.
> I have run a DBCC CHECK db on the database and it reported no errors.
> I have also followed the instructions in
> http://support.microsoft.com/kb/822747 however we do not have a unicode
> field, and the collation is different.
> We backed up the Database and put it onto a different Server and the error
> occured again.
> The table it points to has just over 1 million records.
> Now here is the oddity, if I delete the indexes and put them back in I can
> run the load with the same data and it works.
> I am at a loss on how to proceed with this, so I was wondering if some
> bright people could help.
> Thanks
> J
> The schema for the table is as follows :-
> CREATE TABLE [dbo].[PARCELS] (
> [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CONSIGNMENT_NO] [int] NULL ,
> [RETL_CODE] [smallint] NULL ,
> [PARCEL_TYPE] [tinyint] NOT NULL ,
> [PARCEL_NO] [int] NULL ,
> [BOX_NUMBER] [smallint] NULL ,
> [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> [CREATED_AT] [bigint] NULL ,
> [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [SIZE] [int] NOT NULL ,
> [WEIGHT] [int] NOT NULL ,
> [CUSTOMER_CONSIGNMENT] [int] NULL ,
> [LAST_TRACK_EVENT] [int] NULL ,
> [HELD] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> PRIMARY KEY CLUSTERED
> (
> [PARCEL_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] ADD
> CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> GO
> CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE])
ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CON
SIGNMENT]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON
[PRIMARY]
> GO
> CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO])
ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_
NO]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]
) ON [PRIMARY]
> GO
>
>

Friday, March 30, 2012

Index Table

Index Table

Index Spool/Eager Spool

Wednesday, March 21, 2012

Index or not to index

Index or not to index

Index or not to index

Wednesday, March 7, 2012

Index Error - Re-Post

Index Error - Re-Post

Index Error

Index Error

index and key

Blog Archive

About Me