Index Scanning

Please read following statements. statement 1 runs very faster and uses index scan. The statement 2 runs very slow uses table scan.

The Following things are already done.

DBCC DBREINDEX
Sp_updatestats
update statistics

It is only affecting one column (date_added) and also other statistic name start with _WA_SYS_* but date column starts as statistic_date_added. This has happened only for the last two days. All the production store procedure accessing date_columns now running longer.

Is there anybod can explain this and please post a solution. ?

Raj Sankar

Statement 1.
select * from rx_control where store_id = @.store_id and date_added between '08/01/02' and '08/20/02'
------------------------
statement 2.
------------
declare @.bdate as datetime
declare @.edate as datetime
declare @.rxid as int

set @.store_id = '52'
set @.rxid = '158315'
set @.bdate = '08/01/02'
set @.edate = '08/10/02'

select * from rx_control where store_id = @.store_id and date_added between @.bdate and @.edate
------------------------check with dbcc showcontig report and depending on this u should go for dbcc indexfrag on respective fields.

if still the problem persists send me the showcontig report for the respectve table if u can.|||I had problem like this or very similar.
Query optimizer creates an execution plan based on conditions. For the first statement optimizer knows date range exactly and creates the best execution plan.
The second statement is using local variables and optimizer creates common execution plan (without using particular conditions).
How to resolve this problem? In my situation I created additional table where parameters were saved and I was using join. It looks like a stupid decision but it works.
You can try to use this idea.

Good luck

Index Performance

Hi,

I am trying to improve performance in some statements i run against a 15M rows table.

I have batchs that inserts about 6M rows and some that insert 500K to 1M rows...

Statistically i have a match of 1 to 3.

I am currently disabling all indexes in the destination table and running the insert batch's.

After it finishes the statements i execute the rebuild of all indexes...

This takes up to 4 hours to run... ( 3 of them used just to rebuild the indexes )

My question is: Am i using the best aproach? Or is it better to leave the indexes enabled and do the inserts batch with them?

I Hope i was clear enough

Best Regards,

Yes, that is most often the best approach. It is much faster to add data to a heap, and then to reorganize the data just once. Generally speaking, doing large quantities of inserts would already likely fragment the indexes and need to be rebuilt anyhow.

|||

No, you do NOT want to leave the indexes 'enabled' during the imports.

You may wish to try the following to determine if performance can be improved.

If your situation allows:

switch the database RECOVERY model to 'Simple',

Drop the indexes,

Make the imports in smaller 'batches' (Perhaps 100K rows), Looping until complete

ReBuild the Indexes

switch back to FULL recovery when finished,

and then make a FULL BACKUP.

Be sure that you Build any Clustered Indexex first!

|||

Thats exactly what i am doing...

DB's are in simple mode forever.. because we dont need transactions.

Indexes are not droped but instead are disabled...

I execute the batch (dont use loops because performance is ok - i only use loops for delete's )

I execute Rebuild with ALL on Table_name parameter.

So i guess theres no way around.... I have to wait 3 hours for the indexes to get rebuild all the time... even if i only insert 1 row...

Also the post above speakes on Reorganizing indexes but i guess he meant Rebuilding no? Changing that amount of rows would definitly need a rebuild right?

Regards,

|||

You can also try, if possible, importing to a dummy table, leaving the original table in tact, then later insert into the real table.

In this case, you have 1 hour to import + 1 hour to INSERT = 2 hours

You've just saved 2 hours on waiting for the indexes to be re-enabled.

Just my twist on it,

Adamus

|||

Sorry?

But my batch is already inserting from a temporary table into a final one... So you would do it with the indexes up?

Regards

|||

So you are bulk INSERTING not bulk IMPORTING.

Correct?

Adamus

|||

Yes sorry... it was my english that was right in the first post and wrong in the second when i mentioned import instead of insert

To take out any doubts i am bulk inserting from one temporary table into a final one...

You are true if i was importing anyway

Best Regards,

|||

Are are overwriting or appending to the existing records in the final table?

What I'm getting at is, instead of touching the records for the sake of reports and processing, why not rename the temp table to the name of the final table, if it exists in SQL, and create a new temp table?

If this is possible, add the indexes to the new table.

If you are overwriting:

|

V

Drop Final Table

|

V

Rename Temp Table to Final Table

|

V

Create New Temp Table

|

V

Apply indexes to new final table

Adamus

|||

>>Also the post above speakes on Reorganizing indexes but i guess he meant Rebuilding no? Changing that amount of rows would definitly need a rebuild right?<<

No,if you are just adding rows to a table, you can let SQL Server reorganize the pages of the index instead of doing a full rebuild. It can be done while others are using the table. In 2005, ALTER INDEX...REORGANIZE, in 2000, it is a DBCC.

>>So i guess theres no way around.... I have to wait 3 hours for the indexes to get rebuild all the time... even if i only insert 1 row... <<

Now here is a tricky question. You would be silly to drop all of the indexes and rebuild the table for a single row insert (unless your only job is to do this, and while you wait you can read books on database design. Then it would be a good thing

Seriously, all the answers we gave you were based on a BULK load of the table. Meaning you were the only user at the time, pumping in a very high percentage of rows. Your original question stated:

>>

I am trying to improve performance in some statements i run against a 15M rows table.

I have batchs that inserts about 6M rows and some that insert 500K to 1M rows..<

You are significantly changing the shape of the table if you are adding or even changing keys of .1 of the table. So the fastest thing to do in this case is like to employ one of the techniques mentioned, and use a BULK operation and rebuild indexes.

We haven't even talked about what you are clustering on, or how many indexes, all important things to discuss, if you are only adding (or changing) far fewer rows. Testing is the key. I would suggest you write at least two or three scenarios and test.

If rowcount < N, then just insert directly.

If rowcount >= N and < M, disable these indexes, insert your rows, then add them back,

if rowcount >= M, disable/drop all indexes (including Clustered) and SSIS the rows in.

Especially if you are running short of time for your (presumably) off hours processing to take place

|||

Hi Arnie,

I can see the benefits of switching to "bulk-logged" recovery model, but siwtching to "simple" will break the sequence of transaction log backups.

Switching from the Full or Bulk-Logged Recovery Model

http://msdn2.microsoft.com/en-us/library/ms190203.aspx

AMB

|||

Alejandro,

I didn't indicate, but assumed (and yes, I know that one shouldn't assume) that a full backup would be made before changing the recovery model

I should have been more directly clear on that point.

Your point about the chain of backups is very important. Thanks for adding that to clarify the conversation.

|||

I guess you are using SS 2005. Do a test without disabling the indexes, because SS can decide to use a strategy sometimes

called index-at-a-time, were instead updating each index by each row inserted, SS gather a group of rows, sort them per each

index and merge them. The advantage is that SS will not access same index page more than once.

You can read about this estrategy in the book:

Inside Microsoft SQL Server 2005: The Storage Engine

by Kalen Delaney

Chapter 7 - Index Internals and Management

Table-Level vs. Index-Level Data Modification

Tame Bulky Inserts

By: Kalen Delaney

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=25521&DisplayTab=Article

AMB

|||

Well my table suffers changes in adding and removing records... so this is alot of change happening here... a complete transformation of the final result.

So i think rebuild is definitly a must :/ I didn't thought it would take that amount of time!

Regards

index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5
Are you getting an error? If so it would be nice to know what.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5
|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>
|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:

> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...[vbcol=seagreen]
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:

index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5Are you getting an error? If so it would be nice to know what.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Are you getting an error? If so it would be nice to know what.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:
> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> > Msg 1069, Level 15, State 1, Line 3
> > Index hints are only allowed in a FROM clause.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> >> Are you getting an error? If so it would be nice to know what.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Hassan" <hassan@.test.com> wrote in message
> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> >> Can I not use index hints on delete statements as below ? Using SQL 2005
> >>
> >> delete from dbo.table1 WITH (index(idx_test))
> >> where col1 <= 5
> >>
> >
>|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:
>> How about this:
>> delete a
>> from dbo.table1 AS a WITH (index(idx_test))
>> where a.col1 <= 5
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> > Msg 1069, Level 15, State 1, Line 3
>> > Index hints are only allowed in a FROM clause.
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> >> Are you getting an error? If so it would be nice to know what.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Hassan" <hassan@.test.com> wrote in message
>> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> >> Can I not use index hints on delete statements as below ? Using SQL
>> >> 2005
>> >>
>> >> delete from dbo.table1 WITH (index(idx_test))
>> >> where col1 <= 5
>> >>
>> >
>>

Index Creation Before Bulk Insert

This weekend, I tried to run some large bulk insert statements followed by create index statements on the tables I inserted the data into. For some reason, it didn't finish the largest insert statement. However, the index for that table started creating. Then my log file grew to a half a terabyte. Now the cancel statement is taking forever. What am I doing wrong? Here is the part of the code.

..........

BULK INSERT dbo.bigtable FROM 'data' WITH (TABLOCK);

GO

USE [Database]

GO

/****** Object: Index [ix_ID] Script Date: 04/27/2007 14:34:41 ******/

CREATE CLUSTERED INDEX [ix_ID] ON [dbo].[bigtable]

(

[ID] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PS_Year]([DatafileYear])

GO

On a huge table, such as you seem to be describing, I think there is a lot of effort required to order the table data to conform to a CLUSTERED INDEX.

You may want to try a non-clustered index and see what sort of performance penalty you incur.

Just a suggestion...

Dan

|||

Are there any reason why create clustered index after bulk insert ?

If create clustered index after bulk insert, SQL Server need a lot of data and log space for sort/move data.

|||

To try to assess the performance gains of having a CLUSTERED index versus a Non-CLUSTERED index on the same columns, I made some changes today to some of my data tables, on which I did not earlier have a CLUSTERED index.

I used the same columns, same column order, in the CLUSTERED index as in a Non-CLUSTERED index that I deleted.

A typical table size for the 6 tables I changed is 100MB and 1,000,000 rows.

I then ran a process that has maybe 50-100 independent SQL queries that perform various SUMs and other aggregations of the data in these tables, subject to different WHERE clauses, etc. The GROUP BY aspects of these SUMs use the columns specified in the CLUSTERED index. (I have run this process many, many times, so I have reasonable statistics on its typical duration.)

The CLUSTERED indexes seemed to give a performance gain of approximately 10%.

So there seems to be some benefit, for tables of this size, to have a CLUSTERED index that is reasonably well designed. But the benefit seems to be modest, i.e., around 10%, instead of the substantial benefit gained by having ANY appropriate index, whether CLUSTERED or Non-CLUSTERED.

FYI.

Dan

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