Indexes slowing down BULK INSERT

I've been doing some experiments with speeding up copying tables of
approximately 1 million rows between databases using BCP and BULK INSERT.

I noticed that the total time for removing the indexes (non-clustered) and
then recreating them after the BULK INSERT was significantly less than just
doing the BULK INSERT with the indexes left there, even though I specified
TABLOCK.

I would have expected SQL Server not to update the index until the insert
completed (given the table lock) and so removing the indexes would have no
effect. Can anyone explain why removing the indexes should speed it up?

This is on SQL Server 7.

Cheers
DaveDavid Sharp (no email address supplied) writes:
> I've been doing some experiments with speeding up copying tables of
> approximately 1 million rows between databases using BCP and BULK INSERT.
> I noticed that the total time for removing the indexes (non-clustered)
> and then recreating them after the BULK INSERT was significantly less
> than just doing the BULK INSERT with the indexes left there, even though
> I specified TABLOCK.
> I would have expected SQL Server not to update the index until the insert
> completed (given the table lock) and so removing the indexes would have no
> effect. Can anyone explain why removing the indexes should speed it up?

I have not studied this case very closely. But a few observations: if
you supplied a batch size with /b, SQL Server had no choice but to
maintain the indexes while loading, since each batch is committed
separately.

When running some bulk-loading recently, I notice that when loading on
a completely unindexed table, BCP reported the copied rows swiftly, and
then completely directly, whereas on indexed tables there was a delay
from when all rows had been loaded until the command had completed,
which I supposed was spent on rebuilding indexes. I did not use TABLOCK.

I should add that I was working on SQL 2000, and the behaviour I saw
may reflect an improvement from SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns941B1079D8D3Yazorman@.127.0.0.1...
> David Sharp (no email address supplied) writes:
> > I've been doing some experiments with speeding up copying tables of
> > approximately 1 million rows between databases using BCP and BULK
INSERT.
> > I noticed that the total time for removing the indexes (non-clustered)
> > and then recreating them after the BULK INSERT was significantly less
> > than just doing the BULK INSERT with the indexes left there, even though
> > I specified TABLOCK.
> > I would have expected SQL Server not to update the index until the
insert
> > completed (given the table lock) and so removing the indexes would have
no
> > effect. Can anyone explain why removing the indexes should speed it up?
> I have not studied this case very closely. But a few observations: if
> you supplied a batch size with /b, SQL Server had no choice but to
> maintain the indexes while loading, since each batch is committed
> separately.

I have studied this somewhat closely. :-)

And what Erland says about the /b is a critical part of it. We on a
quarterly basis have to load a multimillion set of rows. As an experiment
recently (to reconfirm my thoughts) I did a test load on a backup server.
It took I believe 3 days to do the load. This was without removing the
non-clustered indices first.

For the actual load I I removed the non-clustered indices (but kept the
clustered index since the data is BCP'd out of another table already in
order). This took well under 12 hours. (Actually not 100% sure how long it
took since I started it around midnight and the scripts finished sometime
before 9:00 AM). This included re-applying the indices to the table.

I don't know how much of a difference there would be if the data had been
completely unordered.

> When running some bulk-loading recently, I notice that when loading on
> a completely unindexed table, BCP reported the copied rows swiftly, and
> then completely directly, whereas on indexed tables there was a delay
> from when all rows had been loaded until the command had completed,
> which I supposed was spent on rebuilding indexes. I did not use TABLOCK.
> I should add that I was working on SQL 2000, and the behaviour I saw
> may reflect an improvement from SQL7.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

indexes are slowing down my query

>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?

>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>Covered queries can improve performance. Covered queries
>are queries where all the columns specified in the query
>are contained within the same index. For example, a query
>retrieving columns a and b from a table that has a
>composite index created on columns a, b, and c is
>considered covered. Creating indexes that cover a query
>can improve performance because all the data for the
query
>is contained within the index itself; only the index
>pages, not the data pages, of the table must be
referenced
>to retrieve the data, thereby reducing overall I/O.
>Although adding columns to an index to cover queries can
>improve performance, maintaining the extra columns in the
>index incurs update and storage costs.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>transdtl
>SERVICE','DTV*DIRECTV
on[vbcol=seagreen]
is[vbcol=seagreen]
this[vbcol=seagreen]
>in
>performance.com/composite_indexes.asp
to[vbcol=seagreen]
>table
>soooooo
>the
index.[vbcol=seagreen]
query[vbcol=seagreen]
>.
>On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:

>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

indexes are slowing down my query

sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.c...ite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisRcomposite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ

indexes are slowing down my query

sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.com/composite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
*** note: someone mentioned yesterday (thank you) that the
results are due to the ITW's index being a covered index.
That makes sense. But I am still not getting why my query
takes 20 seconds longer after adding individual indexes?
***
TIA, ChrisRHi,
Here is a bit of information I found in BOL:
"
Covered queries can improve performance. Covered queries
are queries where all the columns specified in the query
are contained within the same index. For example, a query
retrieving columns a and b from a table that has a
composite index created on columns a, b, and c is
considered covered. Creating indexes that cover a query
can improve performance because all the data for the query
is contained within the index itself; only the index
pages, not the data pages, of the table must be referenced
to retrieve the data, thereby reducing overall I/O.
Although adding columns to an index to cover queries can
improve performance, maintaining the extra columns in the
index incurs update and storage costs.
"
It seems that your individual indexes DO NOT cover your
query. Each time you run your query, the optimizer is
using 4 different index (one at a time) and it has
increased disk i/o. Increased disk i/o means slow query
response.
hth
DeeJay
>--Original Message--
>sql2k sp3
>Ive got a query that takes 30 seconds to run:
>
> select top 50 customerkey as customerkey from
transdtl
> where MerchName in ('DTV*DIRECTV
SERVICE','DTV*DIRECTV
>SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey
> order by CustomerKey,min(TransDate)
>
>Index Tuning Wizard wants to create an Composite Index on
>MerchName, CustomerKey, TranCode, and TransDate. There is
>already an Composite Index on TransDate, TranCode(in this
>order) but for kicks I do the Wizards deal. Pretty cool
in
>that my query speeds up to only 1 second. Any yes, I
>cleared the cache. Ive always followed the advice from
>this article:
>http://www.sql-server-
performance.com/composite_indexes.asp
>and for the most part used single column Indexes instead
>of Composites. So, I drop the newly created Composite
>Indexes, create 4 single coulmn Indexes, and expect it to
>be about the same. Not only was it slower, but it was
>slower now than when there was no Indexes on these
>columns. 50 seconds. Ive tested this alot over the last
>couple of days and its very consistant. Im getting a
table
>scan on the TranCode coulmn I noticed. I used an Index
>Hint and have the same thing. Why is the Composite
soooooo
>much faster than the individuals? Why Is a Table Scan
>occuring? Why is it slower than before? All ideas are
>appreciated.
>*** note: someone mentioned yesterday (thank you) that
the
>results are due to the ITW's index being a covered index.
>That makes sense. But I am still not getting why my query
>takes 20 seconds longer after adding individual indexes?
>***
>TIA, ChrisR
>.
>|||>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?
>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>Covered queries can improve performance. Covered queries
>are queries where all the columns specified in the query
>are contained within the same index. For example, a query
>retrieving columns a and b from a table that has a
>composite index created on columns a, b, and c is
>considered covered. Creating indexes that cover a query
>can improve performance because all the data for the
query
>is contained within the index itself; only the index
>pages, not the data pages, of the table must be
referenced
>to retrieve the data, thereby reducing overall I/O.
>Although adding columns to an index to cover queries can
>improve performance, maintaining the extra columns in the
>index incurs update and storage costs.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>>--Original Message--
>>sql2k sp3
>>Ive got a query that takes 30 seconds to run:
>>
>> select top 50 customerkey as customerkey from
>transdtl
>> where MerchName in ('DTV*DIRECTV
>SERVICE','DTV*DIRECTV
>>SERVICER39')
>> and TranCode not in (7008,7023)
>> Group By CustomerKey
>> order by CustomerKey,min(TransDate)
>>
>>Index Tuning Wizard wants to create an Composite Index
on
>>MerchName, CustomerKey, TranCode, and TransDate. There
is
>>already an Composite Index on TransDate, TranCode(in
this
>>order) but for kicks I do the Wizards deal. Pretty cool
>in
>>that my query speeds up to only 1 second. Any yes, I
>>cleared the cache. Ive always followed the advice from
>>this article:
>>http://www.sql-server-
>performance.com/composite_indexes.asp
>>and for the most part used single column Indexes instead
>>of Composites. So, I drop the newly created Composite
>>Indexes, create 4 single coulmn Indexes, and expect it
to
>>be about the same. Not only was it slower, but it was
>>slower now than when there was no Indexes on these
>>columns. 50 seconds. Ive tested this alot over the last
>>couple of days and its very consistant. Im getting a
>table
>>scan on the TranCode coulmn I noticed. I used an Index
>>Hint and have the same thing. Why is the Composite
>soooooo
>>much faster than the individuals? Why Is a Table Scan
>>occuring? Why is it slower than before? All ideas are
>>appreciated.
>>*** note: someone mentioned yesterday (thank you) that
>the
>>results are due to the ITW's index being a covered
index.
>>That makes sense. But I am still not getting why my
query
>>takes 20 seconds longer after adding individual indexes?
>>***
>>TIA, ChrisR
>>.
>.
>|||On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:
>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

indexes are slowing down my query

sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.com/composite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisRcomposite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ

indexes are slowing down my query

>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?

>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>Covered queries can improve performance. Covered queries
>are queries where all the columns specified in the query
>are contained within the same index. For example, a query
>retrieving columns a and b from a table that has a
>composite index created on columns a, b, and c is
>considered covered. Creating indexes that cover a query
>can improve performance because all the data for the
query
>is contained within the index itself; only the index
>pages, not the data pages, of the table must be
referenced[vbcol=seagreen]
>to retrieve the data, thereby reducing overall I/O.
>Although adding columns to an index to cover queries can
>improve performance, maintaining the extra columns in the
>index incurs update and storage costs.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>transdtl
>SERVICE','DTV*DIRECTV
on[vbcol=seagreen]
is[vbcol=seagreen]
this[vbcol=seagreen]
>in
>performance.com/composite_indexes.asp
to[vbcol=seagreen]
>table
>soooooo
>the
index.[vbcol=seagreen]
query
>.
>
On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:

>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

indexes are slowing down my query

sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.co...te_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisR
composite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon
|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>
|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ