Index causes INSERTs to fail

Has anyone ever had an non-clustered index on a table cause INSERT statements to fail. I ran into a peculiar issue where the composite index defined suddenly caused INSERTs to fail on a table where it had existed for more than 2.5 years.
Once the index was removed, the INSERTs began working again.
Let me know if anyone else has run into this.What do you mean by "caused"? Are you receiving any specific errors when the
insert fails?
Deadlocks can occur when indexes are being updated during insert operations.
Are you getting deadlocks perhaps?
Regards,
Greg Linwood
SQL Server MVP
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:BBAD0449-A137-41D3-AA43-A26256BF4A81@.microsoft.com...
> Has anyone ever had an non-clustered index on a table cause INSERT
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.|||On Wed, 4 Aug 2004 15:07:01 -0700, rowentx wrote:
>Has anyone ever had an non-clustered index on a table cause INSERT statements to fail. I ran into a peculiar issue where the composite index defined suddenly caused INSERTs to fail on a table where it had existed for more than 2.5 years.
>Once the index was removed, the INSERTs began working again.
>Let me know if anyone else has run into this.
Ho rowentx,
What exactly do you mean by "cause INSERT statements to fail"? Did you get
any error messages? Did SQL Server silently discard the data? Did your
server start to emit grey smoke? Please be more specific.
Also, I'd like to know if the non-clustered index you mention is defined
as nonunique or unique.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Was the index defined as unique, and were you getting duplicate key errors?
What does it mean that the inserts 'failed'. Did you get an error message?
Was the data just not inserted?
What version are you running?
How are you performing the inserts?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:BBAD0449-A137-41D3-AA43-A26256BF4A81@.microsoft.com...
> Has anyone ever had an non-clustered index on a table cause INSERT
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.|||After reviewing my post, and some of the replies, I realized my information was not detailed enough and was a little misleading.
We are currently running the Enterprise Edition of SQL Server 2000. In my previous post I said that the INSERT statement fails, well that is completely true. The statement actually never fails, but it never completes either. The INSERT statement that failed was being executed via a stored procedure. Data in the table could be viewed with simple selects, but nothing could be inserted at least within a resonable time frame.
The the table has approximately 7 million rows of data and the index that appears to have been the issue was non-unique. The index was comprised of four fields, two ints, and two varchar 255s. We also never experienced any deadlocks and the insert statements never seemed to complete.
However, once I removed the Index, the insert statement completed in milliseconds.
Hopefully this helps clear up my previous post.
"rowentx" wrote:
> Has anyone ever had an non-clustered index on a table cause INSERT statements to fail. I ran into a peculiar issue where the composite index defined suddenly caused INSERTs to fail on a table where it had existed for more than 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.|||Is the insert a single record insert or an insert / select type operation?
There are many possible causes, so I'd suggest narrowing things further by:
(a) Provide the SQL DDL (create table , index etc) for the table & indexes
(b) Provide the sp code
(b) See if the process that "never fails" is blocked by another process
(c) Inspect at least some basic performance counters - is the disk being
accessed heavily, is the CPU maxed & memory usage.
(d) Profile the stored proc's i/o usage (reads)
(e) Check execution plans
(f) Inspect locks taken (sp_lock)
Some of this information would help narrow things down & avoid speculation..
Regards,
Greg Linwood
SQL Server MVP
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:966D1F5C-465D-465F-8A2F-77B8D319B65B@.microsoft.com...
> After reviewing my post, and some of the replies, I realized my
information was not detailed enough and was a little misleading.
> We are currently running the Enterprise Edition of SQL Server 2000. In my
previous post I said that the INSERT statement fails, well that is
completely true. The statement actually never fails, but it never completes
either. The INSERT statement that failed was being executed via a stored
procedure. Data in the table could be viewed with simple selects, but
nothing could be inserted at least within a resonable time frame.
> The the table has approximately 7 million rows of data and the index that
appears to have been the issue was non-unique. The index was comprised of
four fields, two ints, and two varchar 255s. We also never experienced any
deadlocks and the insert statements never seemed to complete.
> However, once I removed the Index, the insert statement completed in
milliseconds.
> Hopefully this helps clear up my previous post.
> "rowentx" wrote:
> > Has anyone ever had an non-clustered index on a table cause INSERT
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.
> >
> > Once the index was removed, the INSERTs began working again.
> >
> > Let me know if anyone else has run into this.|||If that's the case try changing the FILL FACTOR for
indexes:
sp_configure 'allow updates',1
go
sp_configure 'fill factor', 60
go
sp_configure 'allow updates',0
go
The best way to see if you nead to change that value is
to check in the perfmon if the counter Page Splits is to
high
>--Original Message--
>What do you mean by "caused"? Are you receiving any
specific errors when the
>insert fails?
>Deadlocks can occur when indexes are being updated
during insert operations.
>Are you getting deadlocks perhaps?
>Regards,
>Greg Linwood
>SQL Server MVP
>"rowentx" <rowentx@.discussions.microsoft.com> wrote in
message
>news:BBAD0449-A137-41D3-AA43-
A26256BF4A81@.microsoft.com...
>> Has anyone ever had an non-clustered index on a table
cause INSERT
>statements to fail. I ran into a peculiar issue where
the composite index
>defined suddenly caused INSERTs to fail on a table where
it had existed for
>more than 2.5 years.
>> Once the index was removed, the INSERTs began working
again.
>> Let me know if anyone else has run into this.
>
>.
>|||And don't forget about any triggers that may be executed as a part of the
insert.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23ZVaEFpeEHA.372@.TK2MSFTNGP12.phx.gbl...
> Is the insert a single record insert or an insert / select type operation?
> There are many possible causes, so I'd suggest narrowing things further
by:
> (a) Provide the SQL DDL (create table , index etc) for the table & indexes
> (b) Provide the sp code
> (b) See if the process that "never fails" is blocked by another process
> (c) Inspect at least some basic performance counters - is the disk being
> accessed heavily, is the CPU maxed & memory usage.
> (d) Profile the stored proc's i/o usage (reads)
> (e) Check execution plans
> (f) Inspect locks taken (sp_lock)
> Some of this information would help narrow things down & avoid
speculation..
> Regards,
> Greg Linwood
> SQL Server MVP
> "rowentx" <rowentx@.discussions.microsoft.com> wrote in message
> news:966D1F5C-465D-465F-8A2F-77B8D319B65B@.microsoft.com...
> > After reviewing my post, and some of the replies, I realized my
> information was not detailed enough and was a little misleading.
> >
> > We are currently running the Enterprise Edition of SQL Server 2000. In
my
> previous post I said that the INSERT statement fails, well that is
> completely true. The statement actually never fails, but it never
completes
> either. The INSERT statement that failed was being executed via a stored
> procedure. Data in the table could be viewed with simple selects, but
> nothing could be inserted at least within a resonable time frame.
> >
> > The the table has approximately 7 million rows of data and the index
that
> appears to have been the issue was non-unique. The index was comprised of
> four fields, two ints, and two varchar 255s. We also never experienced
any
> deadlocks and the insert statements never seemed to complete.
> >
> > However, once I removed the Index, the insert statement completed in
> milliseconds.
> >
> > Hopefully this helps clear up my previous post.
> >
> > "rowentx" wrote:
> >
> > > Has anyone ever had an non-clustered index on a table cause INSERT
> statements to fail. I ran into a peculiar issue where the composite index
> defined suddenly caused INSERTs to fail on a table where it had existed
for
> more than 2.5 years.
> > >
> > > Once the index was removed, the INSERTs began working again.
> > >
> > > Let me know if anyone else has run into this.
>