About indexes

Dear MSSQL DBAs,
I am trying to research about indexes and find out performance issues
related with indexes. My Questions are :
1) are indexes useful if we are using Bulk operations?
2) does indexes have any effect on simple INSERT or DELETE statements?
3) Do we need to drop indexes before Bulk operations
4) Is individual insert statement in a loop treated as a bulk insertion?
Regards,
Abdul-Rahman
1) No, they have overhead. You could play with having the data sorted as per the clustered index and
specifying such an hint for the BULK load. Something you have to test for yourself.
2) Yes, they are maintained.
3) Not needed, but most often beneficial. Test and see.
4) No.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Abdul-Rahman" <rahman.mahmood@.pk.softecheww.com> wrote in message
news:uH$mJvHCFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear MSSQL DBAs,
> I am trying to research about indexes and find out performance issues
> related with indexes. My Questions are :
> 1) are indexes useful if we are using Bulk operations?
> 2) does indexes have any effect on simple INSERT or DELETE statements?
> 3) Do we need to drop indexes before Bulk operations
> 4) Is individual insert statement in a loop treated as a bulk insertion?
> Regards,
> Abdul-Rahman
>
|||Please see my reply to your previous post on the subject.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abdul-Rahman" <rahman.mahmood@.pk.softecheww.com> wrote in message
news:uH$mJvHCFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear MSSQL DBAs,
> I am trying to research about indexes and find out performance issues
> related with indexes. My Questions are :
> 1) are indexes useful if we are using Bulk operations?
> 2) does indexes have any effect on simple INSERT or DELETE statements?
> 3) Do we need to drop indexes before Bulk operations
> 4) Is individual insert statement in a loop treated as a bulk insertion?
> Regards,
> Abdul-Rahman
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJozkCJCFHA.4004@.tk2msftngp13.phx.gbl...
> 1) No, they have overhead. You could play with having the data sorted as
per the clustered index and
> specifying such an hint for the BULK load. Something you have to test for
yourself.
> 2) Yes, they are maintained.
> 3) Not needed, but most often beneficial. Test and see.
For example, on a quarterly load we do, dropping the indices makes a HUGE
difference (on the order of 6 hours vs. 48 hours).
In other cases, it's not worth the trouble. So, as Tibor says, test.