index not used when column is nullable and using jdbc

I have a table with a few columns, and one of the non-PK columns is indexed
and defined as nullable. Most of my selects happen on this column, but I
notice that as the no. of rows in this table increase, the time for selects
keeps going up. This happens while accessing this table using jdbc from a
java program.
I guess this is due to the index not being used by the server (query
optimizer?). When I look at the query execution plan in Query Analyzer, it
shows that the index will be used. What's going on? Is this a quirk of using
a jdbc driver?
thanks & regards,
Palani
Palaniappan N wrote:

> I have a table with a few columns, and one of the non-PK columns is indexed
> and defined as nullable. Most of my selects happen on this column, but I
> notice that as the no. of rows in this table increase, the time for selects
> keeps going up. This happens while accessing this table using jdbc from a
> java program.
> I guess this is due to the index not being used by the server (query
> optimizer?). When I look at the query execution plan in Query Analyzer, it
> shows that the index will be used. What's going on? Is this a quirk of using
> a jdbc driver?
> thanks & regards,
> Palani
It *probably* has nothing to do with the driver. You can verify that by
timing the query from a non-jdbc application. The only possibility is
if the column is a varchar and you are parameterizing the search value.
Try timing a select where the search value is specified in the SQL, like
"select * from mytable where mycol = 'myval'". If that's much faster,
try adding the property SendStringParametersAsUnicode=false to your
connection.
Joe Weinstein at BEA
|||Thanks for the reply. I already had set 'SendStringParametersAsUnicode=false'
in the connection string.
I found out that on installing SP3a on SqlServer 2000, the issue disappeared.
regards,
Palani
"Joe Weinstein" wrote:

>
> Palaniappan N wrote:
>
> It *probably* has nothing to do with the driver. You can verify that by
> timing the query from a non-jdbc application. The only possibility is
> if the column is a varchar and you are parameterizing the search value.
> Try timing a select where the search value is specified in the SQL, like
> "select * from mytable where mycol = 'myval'". If that's much faster,
> try adding the property SendStringParametersAsUnicode=false to your
> connection.
> Joe Weinstein at BEA
>

Index Locking Operation Questiong

Given a table with a clustered index and several other nonclustered indexes
which references the column defined in the clustered index, what type of
locks should I expect to be placed on all of nonclustered index? For example,
create table tblTest
(
col1 int not null,
col2 varchar(10) not null,
col3 varchar(10) not null
)
create unique clustered index (idx1_name) on tblTest (col1)
create nonclustered index (idx2_name) on tblTest(col1, col2)
create nonclustered index (idx3_name) on tblTest(col3)
Assuming the table has been populated with some data, I execute the
following statement within an explicit transaction.
begin tran
update t
set col1 = col1
from tblTest t
where col1 = 1234
end tran
(Yes, I know...as silly as this looks this is what our vendor product does
based on the profile trace we captured.)
Given this situation, I expect that SQL server would exclusive lock TAB,
PAG, RID on the tblTest object. Where PAG implicitly is locking the
(idx1_name) which is the clustered index. Also, I would expect an exclusive
lock on (idx2_name) since it needs to be "updated" however I do not expect
any lock on (idx3_name) since it would not be needed in the transaction. Is
this correct? I would really like to understand the locking mechanism in
this situation.
Thanks,
Mike MHello, Mike
In a table that has a clustered index, any non-clustered index contains
(at the leaf-level) the clustered index key, as a row locator. This is
why you see the exclusive lock for the idx3_name index. For more
informations, see "nonclustered indexes, architecture" in Books Online.
Razvan|||Ok, thank you for the info.
Mike M

Index Locking Operation Questiong

Given a table with a clustered index and several other nonclustered indexes
which references the column defined in the clustered index, what type of
locks should I expect to be placed on all of nonclustered index? For exampl
e,
create table tblTest
(
col1 int not null,
col2 varchar(10) not null,
col3 varchar(10) not null
)
create unique clustered index (idx1_name) on tblTest (col1)
create nonclustered index (idx2_name) on tblTest(col1, col2)
create nonclustered index (idx3_name) on tblTest(col3)
Assuming the table has been populated with some data, I execute the
following statement within an explicit transaction.
begin tran
update t
set col1 = col1
from tblTest t
where col1 = 1234
end tran
(Yes, I know...as silly as this looks this is what our vendor product does
based on the profile trace we captured.)
Given this situation, I expect that SQL server would exclusive lock TAB,
PAG, RID on the tblTest object. Where PAG implicitly is locking the
(idx1_name) which is the clustered index. Also, I would expect an exclusive
lock on (idx2_name) since it needs to be "updated" however I do not expect
any lock on (idx3_name) since it would not be needed in the transaction. Is
this correct? I would really like to understand the locking mechanism in
this situation.
Thanks,
Mike MHello, Mike
In a table that has a clustered index, any non-clustered index contains
(at the leaf-level) the clustered index key, as a row locator. This is
why you see the exclusive lock for the idx3_name index. For more
informations, see "nonclustered indexes, architecture" in Books Online.
Razvan|||Ok, thank you for the info.
Mike M

index defrag rebuild

Hi,
I have a table that has one clustered index and one non-clustered index
defined. What difference would it be if you do the following actions:
DBCC INDEXDEFRAG
DBCC DBREINDEX
REBUILD INDEX
Also, if the table had 100 rows in the beginning when the indexes were
created. After creating the indexes 100 more rows were inserted into the
table. When would these newly inserted rows gets included in the index?
Thanks in advance.Yes, after you create the index, this index will be updated when records are
inserted or deleted.
If your table only has a few hundred records you do not need to worry about
reindexing. Even if you have big tables you need to check the level of index
fragmentation first. Start by looking at DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats on BOL.
Hope this helps,
Ben Nevarez
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.|||> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
When they are inserted. Thatâ's when the database will touch the indexes. You
have the clustered index in there. That clustered index is your table however
wired it sounds, it is your physical order and the physical pages can be
organized only one way. BOL for sql 2005 has pretty good description what a
clustered index is. Have questions, askâ?¦
> DBCC INDEXDEFRAG
That is a low priority operation. Pretty much it will reorganize the index
for you, will shuffle pages in place. It will take in consideration the fill
factor of the index and compact the pages, if there are any empty ones as a
result of the compaction, they will be freed (removed that is). This is an
on-line operation and it does not lock other processes and is not locked by
them, does not create long living locks, will skip pages locked by other
processes. What does it do?
DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the
physical order of the pages matches the left-to-right logical order of the
leaf nodes, therefore improving index-scanning performance. When DBCC
INDEXDEFRAG is run, index de-fragmentation occurs serially. This means that
the operation on a single index is performed using a single thread. No
parallelism occurs. Also, operations on multiple indexes from the same DBCC
INDEXDEFRAG statement are performed on one index at a time.
Unlike DBCC DBREINDEX is the real rebuild. It is not an on-line operation
and holds long term locks. DBCC DBREINDEX in other words will and should
conflict with any dml on the same table, DBCC INDEXDEFRAG will not
lock/conflick as it was mentioned earlier.
The chose if -time and the fragmentation level you have. If your
maintenance windows allows and your fragmentation can not be fixed by
INDEXDEFRAG, then rebuild that thing.
Does not matter which sql command is used to de-fragment the index, it is
always fully logged, does not matter what mode is your database in. Therefore
do not run INDEXDEFRAG when your database is active period, all you are going
to get is about 1.4 size of your data in tlogs and you did not de-fragment
your index anyways, because the applications were 'dancing' on it locking
pages, INDEXDEFRAG has skipped those, but it was really busy trying to do
the job you have asked for. BOL says that defragmentation of a very
fragmented index can generate more log than a fully logged index creation.
I've seen that one not once, not a joke :). The defragmentation, is performed
as a series of short transactions, but still tlog gets quite big (esp when
you have fast SAN under the tlogs).
DBREINDEX is not good to run when the database is active, pick your quiet
time if you can. Otherwise you will see spike in locks and dead locks. What
will it do to your system depends how fault tolerant your applications are
and if they can handle dead lock errors (many developers just do not code
such a stuff)
When to choose what:
If the index is interleaved with other indexes on disk, running DBCC
INDEXDEFRAG against that index does not make all leaf pages in the index
contiguous. Therefore if clustering is what you are after, rebuild.
Maintenance plans:
Native Rebuild Index Task uses the ALTER INDEX statement when connected to
SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL
Server 2000
If you are using 3-rd party backup/maintenance read carefully their doc and
just run good old trace to see what exactly are they calling. The trace will
show you what is it, it may be not what the doc hasâ?¦
> DBCC DBREINDEX
> REBUILD INDEX
On index rebuild BOL has pretty good description.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
Making a decision if you have fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS
Avg. Page Density (full):
average level how filled the pages are. The value should be compared to
the fill factor setting of the particular index, then you look and decide
whether or not the index is internally fragmented.
Scan Density:
the ratio between the Best Count (what was necessary to read when
scanning all the pages of the index) and Actual Count of extents (what did
it actually read). You want this to be as close to 100 as possible. 75% and
below definitely indicates external fragmentation. It is a fine line to
deside what is fragmented what is not. you may have a system that just
creates such a number because it is it's nature and this is how your data are
insrterd,deleted,updated and you have to live with it.
Logical Scan Fragmentation:
shows the ratio of pages out of logical order. You want this close to 0.
10% + indicates external fragmentation.
I've seen references that "Extent Scan Fragmentation" is not really
important. This is because the algorithm that calculates it does not work for
indexes that span over multiple files. According to some Microsoft sources,
in the next version of SQL Server, DBCC SHOWCONTIG will not even include this
metric.
I did not mess with 2008 to test that one to be honest.
--
Thanks, Liliya
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.|||> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
When they are inserted. Thatâ's when the database will touch the indexes. You
have the clustered index in there. That clustered index is your table however
wired it sounds, it is your physical order and the physical pages can be
organized only one way. BOL for sql 2005 has pretty good description what a
clustered index is. Have questions, askâ?¦
> DBCC INDEXDEFRAG
That is a low priority operation. Pretty much it will reorganize the index
for you, will shuffle pages in place. It will take in consideration the fill
factor of the index and compact the pages, if there are any empty ones as a
result of the compaction, they will be freed (removed that is). This is an
on-line operation and it does not lock other processes and is not locked by
them, does not create long living locks, will skip pages locked by other
processes. What does it do?
DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the
physical order of the pages matches the left-to-right logical order of the
leaf nodes, therefore improving index-scanning performance. When DBCC
INDEXDEFRAG is run, index de-fragmentation occurs serially. This means that
the operation on a single index is performed using a single thread. No
parallelism occurs. Also, operations on multiple indexes from the same DBCC
INDEXDEFRAG statement are performed on one index at a time.
Unlike DBCC DBREINDEX is the real rebuild. It is not an on-line operation
and holds long term locks. DBCC DBREINDEX in other words will and should
conflict with any dml on the same table, DBCC INDEXDEFRAG will not
lock/conflick as it was mentioned earlier.
The chose if -time and the fragmentation level you have. If your
maintenance windows allows and your fragmentation can not be fixed by
INDEXDEFRAG, then rebuild that thing.
Does not matter which sql command is used to de-fragment the index, it is
always fully logged, does not matter what mode is your database in. Therefore
do not run INDEXDEFRAG when your database is active period, all you are going
to get is about 1.4 size of your data in tlogs and you did not de-fragment
your index anyways, because the applications were 'dancing' on it locking
pages, INDEXDEFRAG has skipped those, but it was really busy trying to do
the job you have asked for. BOL says that defragmentation of a very
fragmented index can generate more log than a fully logged index creation.
I've seen that one not once, not a joke :). The defragmentation, is performed
as a series of short transactions, but still tlog gets quite big (esp when
you have fast SAN under the tlogs).
DBREINDEX is not good to run when the database is active, pick your quiet
time if you can. Otherwise you will see spike in locks and dead locks. What
will it do to your system depends how fault tolerant your applications are
and if they can handle dead lock errors (many developers just do not code
such a stuff)
When to choose what:
If the index is interleaved with other indexes on disk, running DBCC
INDEXDEFRAG against that index does not make all leaf pages in the index
contiguous. Therefore if clustering is what you are after, rebuild.
Maintenance plans:
Native Rebuild Index Task uses the ALTER INDEX statement when connected to
SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL
Server 2000
If you are using 3-rd party backup/maintenance read carefully their doc and
just run good old trace to see what exactly are they calling. The trace will
show you what is it, it may be not what the doc hasâ?¦
> DBCC DBREINDEX
> REBUILD INDEX
On index rebuild BOL has pretty good description.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
Making a decision if you have fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS
Avg. Page Density (full):
average level how filled the pages are. The value should be compared to
the fill factor setting of the particular index, then you look and decide
whether or not the index is internally fragmented.
Scan Density:
the ratio between the Best Count (what was necessary to read when
scanning all the pages of the index) and Actual Count of extents (what did
it actually read). You want this to be as close to 100 as possible. 75% and
below definitely indicates external fragmentation. It is a fine line to
deside what is fragmented what is not. you may have a system that just
creates such a number because it is it's nature and this is how your data are
insrterd,deleted,updated and you have to live with it.
Logical Scan Fragmentation:
shows the ratio of pages out of logical order. You want this close to 0.
10% + indicates external fragmentation.
I've seen references that "Extent Scan Fragmentation" is not really
important. This is because the algorithm that calculates it does not work for
indexes that span over multiple files. According to some Microsoft sources,
in the next version of SQL Server, DBCC SHOWCONTIG will not even include this
metric.
I did not mess with 2008 to test that one to be honest.
--
Thanks, Liliya
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.

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 f
ailed 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 state
ments 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.[vbcol=seagreen]
> Hopefully this helps clear up my previous post.
> "rowentx" wrote:
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.[vbcol=seagreen]
|||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...[vbcol=seagreen]
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[vbcol=seagreen]
>more than 2.5 years.
again.
>
>.
>
|||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..[vbcol=seagreen]
> Regards,
> Greg Linwood
> SQL Server MVP
> "rowentx" <rowentx@.discussions.microsoft.com> wrote in message
> news:966D1F5C-465D-465F-8A2F-77B8D319B65B@.microsoft.com...
> information was not detailed enough and was a little misleading.
my
> previous post I said that the INSERT statement fails, well that is
> completely true. The statement actually never fails, but it never
completes[vbcol=seagreen]
> 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.
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.
> milliseconds.
> 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.
>

Index causes INSERTs to fail

Has anyone ever had an non-clustered index on a table cause INSERT statement
s to fail. I ran into a peculiar issue where the composite index defined su
ddenly 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 statemen
ts to fail. I ran into a peculiar issue where the composite index defined s
uddenly 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 p
revious 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 f
ailed 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 ap
pears to have been the issue was non-unique. The index was comprised of fou
r fields, two ints, and two varchar 255s. We also never experienced any dea
dlocks and the insert state
ments never seemed to complete.
However, once I removed the Index, the insert statement completed in millise
conds.
Hopefully this helps clear up my previous post.
"rowentx" wrote:

> Has anyone ever had an non-clustered index on a table cause INSERT stateme
nts 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 tha
n 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.[vbcol=seagreen]
> Hopefully this helps clear up my previous post.
> "rowentx" wrote:
>
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.[vbcol=seagreen]|||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...
cause INSERT[vbcol=seagreen]
>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.
again.[vbcol=seagreen]
>
>.
>|||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...
> information was not detailed enough and was a little misleading.
my[vbcol=seagreen]
> 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.
that[vbcol=seagreen]
> 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.
> milliseconds.
> 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.
>

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