index and key: load

Showing posts with label load. Show all posts

Friday, March 30, 2012

Index tuning under heavy load...

I am trying to find the best way to tune/defrag the indexes on a database
which stays under heavy load 24/7/365
About 1-5 million records added daily...By the end of the day query
performance decreses dramatically.
DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
unacceptable in my case as the table becomes offline and users get hung...
ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running slower
during this operation and it does not do as good of a job as DBREINDEX.
What are my options? Is there a solution to this...?I should add that we are on SQL Server 2005
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>I am trying to find the best way to tune/defrag the indexes on a database
>which stays under heavy load 24/7/365
> About 1-5 million records added daily...By the end of the day query
> performance decreses dramatically.
> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
> unacceptable in my case as the table becomes offline and users get hung...
> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
> slower during this operation and it does not do as good of a job as
> DBREINDEX.
> What are my options? Is there a solution to this...?
>|||If you are on 2005 you should be using ALTER INDEX not DBCC xxx. Reindexing
is a very resource intensive operation and if you want to do this in a 24x7
operation you need to have the hardware to support it or performance will
suffer. But I would argue that your fill factors are not properly set to
limit fragmentation if by the end of the day performance suffers from
fragmentation. But fragmentation should not impact a properly tuned OLTP
system that much anyway. If you do a lot of scans it can hurt you but you
should find out why you are scanning and address that. Perhaps partitioning
is called for here as well. If you can partition such that most of the new
rows are in a different partition than the rest you won't need to rebuild
all the indexes.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23LrM9cM6HHA.3940@.TK2MSFTNGP05.phx.gbl...
>I should add that we are on SQL Server 2005
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>|||I submit that it isn't index frag that is hurting here, but rather the
statistics. New records won't be reflected in the stats (assuming a large
table in which 1-5M rows doesn't trigger an automatic stats update). Thus
queries involving the newly inserted rows won't have optimal query plans
(such as index seeks).
Set up a job to refresh your stats several times throughout the day. Be
careful of the type of scan performed. You need the scan to go quickly.
For those indexes that receive values throughout the range of the index
(like on LastName for example), fragmentation can become an issue. Pick a
reasonable fillfactor to avoid lots of page splits between rebuilds.
Picking this number is part art, but mostly science.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>I am trying to find the best way to tune/defrag the indexes on a database
>which stays under heavy load 24/7/365
> About 1-5 million records added daily...By the end of the day query
> performance decreses dramatically.
> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
> unacceptable in my case as the table becomes offline and users get hung...
> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
> slower during this operation and it does not do as good of a job as
> DBREINDEX.
> What are my options? Is there a solution to this...?
>|||I think you are absolutely right...
And i do need a really fast scan while updating stats because i think it
places share locks on the table..
Will this be the fastest scan i can achieve:
UPDATE STATISTICS Database.Table
WITH SAMPLE 5 PERCENT;
Thank you,
michael
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:uNHfyoX6HHA.5160@.TK2MSFTNGP05.phx.gbl...
>I submit that it isn't index frag that is hurting here, but rather the
>statistics. New records won't be reflected in the stats (assuming a large
>table in which 1-5M rows doesn't trigger an automatic stats update). Thus
>queries involving the newly inserted rows won't have optimal query plans
>(such as index seeks).
> Set up a job to refresh your stats several times throughout the day. Be
> careful of the type of scan performed. You need the scan to go quickly.
> For those indexes that receive values throughout the range of the index
> (like on LastName for example), fragmentation can become an issue. Pick a
> reasonable fillfactor to avoid lots of page splits between rebuilds.
> Picking this number is part art, but mostly science.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>|||Be a little careful of too low of a sample. If the problem is due to rows
that the optimizer doesn't know about you may get similar results with only
a 5% sample. This is usually a try and see, then adjust methodology.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:OD5EkRY6HHA.5844@.TK2MSFTNGP02.phx.gbl...
>I think you are absolutely right...
> And i do need a really fast scan while updating stats because i think it
> places share locks on the table..
> Will this be the fastest scan i can achieve:
> UPDATE STATISTICS Database.Table
> WITH SAMPLE 5 PERCENT;
> Thank you,
> michael
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:uNHfyoX6HHA.5160@.TK2MSFTNGP05.phx.gbl...
>>I submit that it isn't index frag that is hurting here, but rather the
>>statistics. New records won't be reflected in the stats (assuming a large
>>table in which 1-5M rows doesn't trigger an automatic stats update). Thus
>>queries involving the newly inserted rows won't have optimal query plans
>>(such as index seeks).
>> Set up a job to refresh your stats several times throughout the day. Be
>> careful of the type of scan performed. You need the scan to go quickly.
>> For those indexes that receive values throughout the range of the index
>> (like on LastName for example), fragmentation can become an issue. Pick
>> a reasonable fillfactor to avoid lots of page splits between rebuilds.
>> Picking this number is part art, but mostly science.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
>> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>>
>

Index tuning

Hi all,
I created load file (with few test queries) via Profiler and then I tried to
run tuning wizard. Wizards gave message "Workload does not contain any
events or queries that can be tuned against current database ..."
It's strange because load file contains events with queries and this queries
were run over current database (I also set filter condition for database
name to be sure ...)..
Any ideas what am I doing wrong ?
Regards,
Jan
Jan Hruz wrote:
> Hi all,
> I created load file (with few test queries) via Profiler and then I
> tried to run tuning wizard. Wizards gave message "Workload does not
> contain any events or queries that can be tuned against current
> database ..." It's strange because load file contains events with
> queries and this
> queries were run over current database (I also set filter condition
> for database name to be sure ...)..
> Any ideas what am I doing wrong ?
> Regards,
> Jan
I think the ITW requires SQL:BatchCompleted and RPC:Completed events
with the necessary columns. Please post the events and columns that were
captured as well as any filters you used during the trace.
David Gugick
Imceda Software
www.imceda.com
|||I used ProfilerTuning template. it is setup to trace events :
- RPC:Completed
- SQL:BatchCompleted
and data columns :
- EventClass
- TextData
- Duration
- BinaryData
- SPID
I added new filted condition (to track only my requests):
- DBUserName
- DatabaseName
Regards,
Jan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O604jpHRFHA.3288@.TK2MSFTNGP14.phx.gbl...
> Jan Hruz wrote:
> I think the ITW requires SQL:BatchCompleted and RPC:Completed events with
> the necessary columns. Please post the events and columns that were
> captured as well as any filters you used during the trace.
> --
> David Gugick
> Imceda Software
> www.imceda.com

Monday, March 19, 2012

Index name conflict

i have a dts package that is going to run at night and pull data from
2 tables in an Oracle db and load it to 2 staging tables that my
package creates in sql server 2000. i then execute a few sprocs to
and columns, indexes, etc. after that all happens successfully i want
to delete the two production tables and rename the staging tables to
take their place.

the problem is that when i run the sproc to add the indexes,
constraints, etc i get an error because objects with the same name
exist on the 2 production tables. i can't delete the production
tables until the very last step. i thought about generating a random
number in my sproc and using it as the name of the index but that
seems crude and messy. any ideas?teddy_theo@.yahoo.com (Ted Theodoropoulos) wrote in message news:<f5682868.0308141318.7b16a1db@.posting.google.com>...
> i have a dts package that is going to run at night and pull data from
> 2 tables in an Oracle db and load it to 2 staging tables that my
> package creates in sql server 2000. i then execute a few sprocs to
> and columns, indexes, etc. after that all happens successfully i want
> to delete the two production tables and rename the staging tables to
> take their place.
> the problem is that when i run the sproc to add the indexes,
> constraints, etc i get an error because objects with the same name
> exist on the 2 production tables. i can't delete the production
> tables until the very last step. i thought about generating a random
> number in my sproc and using it as the name of the index but that
> seems crude and messy. any ideas?

It's not entirely clear from your email what your process is, but you
could use one set of names for the constraints etc. when they're on
the staging tables, then rename them after you rename the tables:

exec sp_rename 'StagingTable', 'ProductionTable'
exec sp_rename 'ProductionTable.StagingIndexName',
'ProductionIndexName', 'INDEX'
exec sp_rename 'StagingPrimaryKeyName', 'ProductionPrimaryKeyName',
'OBJECT'
etc.

If this isn't helpful, perhaps you could clarify the order of events
in your process, and give an example of where the names clash.

Simon|||i fixed the problem by creating another database and naming it
[dbname]_staging. not very elegant but it works.

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 tuning under heavy load...

Index tuning

Monday, March 19, 2012

Index name conflict

Wednesday, March 7, 2012

Index Error - Re-Post

Index Error - Re-Post

Index Error

Index Error

index and key

Blog Archive

About Me