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 of length xx exceeds the maximum length

Hi,
my program inserts data into a table and it's throwing an exception (below).
I'm not a SQL Server bod so can anyone tell me if there is an alter statemen
t
to increase the size allocated to the index or otherwise what I should do.
The index entry of length 1235 bytes for the index 'ixPDM_ActionDescription'
exceeds the maximum length of 900 bytes.Nope, 900 is max, maybe you should look at Full Text Search if you need
bigger sizes than 900 bytes
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||The maximum length of any index is 900 bytes per row. There's no way around
that. When I say "no way", I'm not entirely truthful - in SQL 2005
'nonessential' columns (i.e. not part of a constraint enforced by the index)
that would cause the index to be larger than 900 bytes (and thus preventing
data from being inserted) can still be included in the index.
Look up INCLUDE in Books Online.
In SQL 2000 you'd have to re-think the fact that you need indexes on columns
larger than 900 bytes.
What version are you using? What's the table DDL? What's the business
requirement? Why do these large columns need to be indexed?
ML
http://milambda.blogspot.com/|||Thank you for your replies. I think I might just drop the index as to my
knowledge I don't think the field is used in a join anywhere just the creato
r
of the db went a bit over the top with indexing.
"ML" wrote:

> The maximum length of any index is 900 bytes per row. There's no way aroun
d
> that. When I say "no way", I'm not entirely truthful - in SQL 2005
> 'nonessential' columns (i.e. not part of a constraint enforced by the inde
x)
> that would cause the index to be larger than 900 bytes (and thus preventin
g
> data from being inserted) can still be included in the index.
> Look up INCLUDE in Books Online.
> In SQL 2000 you'd have to re-think the fact that you need indexes on colum
ns
> larger than 900 bytes.
> What version are you using? What's the table DDL? What's the business
> requirement? Why do these large columns need to be indexed?
>
> ML
> --
> http://milambda.blogspot.com/

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.On Apr 26, 10:05 pm, phancey <d...@.2bytes.co.uk> wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
actually the database file was created without a size, then changed to
500MB, then changed again to 3.5GB, all before the majority of the
data was written to the tables (and certainly before any data was
written to this particular table).|||Can you give the exact statement you are executing to get this information,
and the exact, complete output you receive? Perhaps (hopefully) you are
misinterpreting the data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"phancey" <deja@.2bytes.co.uk> wrote in message
news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>|||Using SQL 2005, I view the properties of the index, then
"Fragmentation".
Also, my data load (which does a check on existing records before
inserting) grinds to a halt presumably because of this query. I have
managed to run the data load a few times without any problem but
unfortunately did not make enough notes on the exact setup because I
did not think there was going to be a problem (I'm pretty sure I did
check fragmentation of the indexes though and am pretty sure that
fragmentation was less than 10%). Now I cannot recreate that fast load
and am at my wits end to figure out what is different.
The database file sizes again are got from the properties page of the
database in Management Studio.
thanks
On Apr 26, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Can you give the exact statement you are executing to get this information,
> and the exact, complete output you receive? Perhaps (hopefully) you are
> misinterpreting the data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "phancey" <d...@.2bytes.co.uk> wrote in message
> news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> > I've got 99% fragmentation on a clustered index of a table with 29699
> > rows (there have been just 29699 inserts, no updates, no deletes).
> > There are 8 columns on the table with an int and a char(32) being the
> > unique clustered index - in that order - and I'm pretty sure that the
> > rows are inserted in int,char(32) order. Average row size is 852,
> > there are 3769 pages with average fullness being 83%.
> > The database file was created as 3.5GB and has 51% space free. The
> > data file itself is only fragmented into 3 pieces according to Disk
> > Fragmenter.
> > I don't really understand why it has fragmented so much. All index
> > rows should be appended because they are inserted in key order, there
> > is plenty of space in the file - would it have been better to have
> > made the file smaller and done AutoGrow by 1MB'
> > The table also has a non-clustered index, which is hardly fragmented
> > at all.
> > The insert transaction consisted of many inserts on many different
> > tables, all with similar indexes, all inserted in key value order.
> > There are maybe 100 tables - inserts take place across the 100 tables,
> > then another group of inserts take place against all the tables. But
> > for any specific table any insert will be in key value order and
> > should therefore be appended (which seems to be supported by the page
> > fullness being 83%). But how can I avoid the logical fragmentation? I
> > know I can use DBCC DBREINDEX but this is happening so quickly in the
> > system that it is not reasonable to have to do it this soon (and it's
> > actually happening three quarters of the way through a data load).
> > There would have been hundreds of thousands of records inserted during
> > the transaction.
> > Thanks for any help.

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.Hi
"phancey" wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
>
Could you post DDL and the command(s) you have used to show the
fragmentation with their output?
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
Disc fragmentation may have an effect on performance, but is not really
related to index fragmentation which could have a more critical effect. To
remove disc fragmentation you would need to stop SQL Server or detatch the
database and run the defragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
No, incrementing with smaller amounts increases the risk of disc
fragmentation, and slows down the process of inserting rows as you will have
to wait while the file expands. Planned pre-allocation of the data files is
the best option. Do you have autoshrink on? Have you shrunk the files
manually?
Is the log file on the same drive?
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
You don't mention the value for extent fragmentation! What you could do is
load into a staging table, then do the inserts into each table in one go
rather than on a row per row basis. Could you drop the clustered index during
the upload?
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
What fragmentation value do you get after the DBREINDEX?
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>
John|||Hi
I have just seen the other post, and seen that you are using SQL 2005, look
at the function sys.dm_db_index_physical_stats, information can be obtained
from books online. You may also want to read about index fragmentation in
Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
0735621055
John|||Doh! The application (which I have no control over), despite asking
them to insert in int,char(32) asc order, was actually inserting in
int,char(32) desc order. So that would probably explain it.
Thanks anyway
On 27 Apr, 08:36, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I have just seen the other post, and seen that you are using SQL 2005, look
> at the function sys.dm_db_index_physical_stats, information can be obtained
> from books online. You may also want to read aboutindexfragmentationin
> Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
> 0735621055
> John|||Hi
"phancey" wrote:
> Doh! The application (which I have no control over), despite asking
> them to insert in int,char(32) asc order, was actually inserting in
> int,char(32) desc order. So that would probably explain it.
> Thanks anyway
>
In which case dropping the index may be your best option.
John|||Better may be to order the index columns in desc order instead. See BOL for
the CREATE INDEX statement.
--
TheSQLGuru
President
Indicium Resources, Inc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0EFD52C4-245E-4551-BD6E-87E9924C29B4@.microsoft.com...
> Hi
> "phancey" wrote:
>> Doh! The application (which I have no control over), despite asking
>> them to insert in int,char(32) asc order, was actually inserting in
>> int,char(32) desc order. So that would probably explain it.
>> Thanks anyway
> In which case dropping the index may be your best option.
> John|||On 27 Apr 2007 05:34:09 -0700, phancey <deja@.2bytes.co.uk> wrote:
>Doh! The application (which I have no control over), despite asking
>them to insert in int,char(32) asc order, was actually inserting in
>int,char(32) desc order. So that would probably explain it.
>Thanks anyway
:)
J.

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.Hi
"phancey" wrote:

> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
>
Could you post DDL and the command(s) you have used to show the
fragmentation with their output?

> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
Disc fragmentation may have an effect on performance, but is not really
related to index fragmentation which could have a more critical effect. To
remove disc fragmentation you would need to stop SQL Server or detatch the
database and run the defragmenter.

> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
No, incrementing with smaller amounts increases the risk of disc
fragmentation, and slows down the process of inserting rows as you will have
to wait while the file expands. Planned pre-allocation of the data files is
the best option. Do you have autoshrink on? Have you shrunk the files
manually?
Is the log file on the same drive?

> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
You don't mention the value for extent fragmentation! What you could do is
load into a staging table, then do the inserts into each table in one go
rather than on a row per row basis. Could you drop the clustered index durin
g
the upload?

> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
What fragmentation value do you get after the DBREINDEX?

> There would have been hundreds of thousands of records inserted during
> the transaction.

> Thanks for any help.
>
John|||Hi
I have just seen the other post, and seen that you are using SQL 2005, look
at the function sys.dm_db_index_physical_stats, information can be obtained
from books online. You may also want to read about index fragmentation in
Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
0735621055
John|||Doh! The application (which I have no control over), despite asking
them to insert in int,char(32) asc order, was actually inserting in
int,char(32) desc order. So that would probably explain it.
Thanks anyway
On 27 Apr, 08:36, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I have just seen the other post, and seen that you are using SQL 2005, loo
k
> at the function sys.dm_db_index_physical_stats, information can be obtaine
d
> from books online. You may also want to read aboutindexfragmentationin
> Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
> 0735621055
> John|||Hi
"phancey" wrote:

> Doh! The application (which I have no control over), despite asking
> them to insert in int,char(32) asc order, was actually inserting in
> int,char(32) desc order. So that would probably explain it.
> Thanks anyway
>
In which case dropping the index may be your best option.
John|||Better may be to order the index columns in desc order instead. See BOL for
the CREATE INDEX statement.
TheSQLGuru
President
Indicium Resources, Inc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0EFD52C4-245E-4551-BD6E-87E9924C29B4@.microsoft.com...
> Hi
> "phancey" wrote:
>
> In which case dropping the index may be your best option.
> John|||On 27 Apr 2007 05:34:09 -0700, phancey <deja@.2bytes.co.uk> wrote:

>Doh! The application (which I have no control over), despite asking
>them to insert in int,char(32) asc order, was actually inserting in
>int,char(32) desc order. So that would probably explain it.
>Thanks anyway

J.

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.On Apr 26, 10:05 pm, phancey <d...@.2bytes.co.uk> wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
actually the database file was created without a size, then changed to
500MB, then changed again to 3.5GB, all before the majority of the
data was written to the tables (and certainly before any data was
written to this particular table).|||Can you give the exact statement you are executing to get this information,
and the exact, complete output you receive? Perhaps (hopefully) you are
misinterpreting the data.
TheSQLGuru
President
Indicium Resources, Inc.
"phancey" <deja@.2bytes.co.uk> wrote in message
news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>|||Using SQL 2005, I view the properties of the index, then
"Fragmentation".
Also, my data load (which does a check on existing records before
inserting) grinds to a halt presumably because of this query. I have
managed to run the data load a few times without any problem but
unfortunately did not make enough notes on the exact setup because I
did not think there was going to be a problem (I'm pretty sure I did
check fragmentation of the indexes though and am pretty sure that
fragmentation was less than 10%). Now I cannot recreate that fast load
and am at my wits end to figure out what is different.
The database file sizes again are got from the properties page of the
database in Management Studio.
thanks
On Apr 26, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Can you give the exact statement you are executing to get this information
,
> and the exact, complete output you receive? Perhaps (hopefully) you are
> misinterpreting the data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "phancey" <d...@.2bytes.co.uk> wrote in message
> news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
>
>
>
>
>
>
>

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

Index aginst table having lot of inserts

Hi ,
i have a question reg index on my table which is having somany inserts. is
that good design to have index on table table which has lot of inserts?
Thanks
BhaskarLike many design considerations, it depends. Virtually all tables should
have a primary key and corresponding index. Additional indexes are often
appropriate to speed up query performance. Of course, the benefits of these
indexes need to be weighted against the associated costs of slower inserts
and space requirements. Since data is usually read more times than written,
the improved data retrieval performance often justifies the write
performance penalty. Storage is inexpensive nowadays.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:D91C16BA-D172-4929-81AB-E92C17F0F8FE@.microsoft.com...
> Hi ,
> i have a question reg index on my table which is having somany inserts. is
> that good design to have index on table table which has lot of inserts?
> Thanks
> Bhaskar|||When you say that the table has a lot of inserts, are you speaking of
transactional inserts or bulk inserting?
http://www.microsoft.com/technet/pr...br />
4fec.asp
If you are concerned about fragmentation of indexes as a result of inserts:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:D91C16BA-D172-4929-81AB-E92C17F0F8FE@.microsoft.com...
> Hi ,
> i have a question reg index on my table which is having somany inserts. is
> that good design to have index on table table which has lot of inserts?
> Thanks
> Bhaskar