Dropping indexes before bulk insert

Hi all,

I have a huge table 170 Gb of size. On that table we have 10 indexes
of around 12 GB in size.

The application is designed such that it bulk inserts the file in to
sql server. But , often we are getting time outs and some latching
isssues ( as can be seen in activity monitor).

So, will this be a good idea of dropping those indexes and then
recreating them again for better performance.

1) Its SQL 2005 Standard Edition SP1

2) Databases are in SIMPLE Recovery mode.

3) Database is not OLTP.

Thanks.

//N

Hi Naj,

Do you see any blocking on the server while the bulk insert is running?

How do you mean latches issues ?

Could you please look for waittime and waitypes in the following view and let us know what is there.

select * from sys.dm_os_waiting_tasks where session_id > 50

Jag

|||

you should be better off by dropping indexes and after bulk insert creating them off line. indexes created offline have very small footprint on log and much faster then indexes created online. as a trade off your table will be offline for a while. how long it depends on clustered key as with large table clustered key plays very vital role when rebuilding indexes.

another point is to consider switching off AUTO_UPDATE_STATISTICS while inserting rows into your table. this will kill the performance while inserting data and updating staticstics at the same time. also there is a new option AUTO_UPDATE_STATISTICS_ASYNC which provide background statistics update. use with extra care as they can hinder performance significatly.

see article http://www.mssqltips.com/tip.asp?tip=1193

Dropping Clustered index associated with Primary Key.

Hi all,

I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.

So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.

(a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.

Thanks.

From BOL.

"When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes."

so basically you can't have a primary key without any indexes. Although you can have PK with a non-clustered index.

I am curious what made you to go down this path to remove the indexes on this table. What are you trying to achieve here?

|||

Actually, there is a big file, which bulk inserts into that huge table,and that table is very rarely used for selects statements.

So, i was thinking , if we could remove the indexes before bulk inserting the data and then rebuild them ( as we build , every week), we can improve the performance of bulk inserting the data into that table.

If you/anyone have any other idea about dealing with this kind of scenario, plz let me know.

Your help is greatly appreciatied.

Thanks.

|||In this scenario, whether to drop the indexes and re-create them after bulk insert or bulk insert data without dropping indexes depends on the ratio of data in the table vs the data coming to the table. If there are already millions of rows, and lot more millions to come then I think its best to drop the indexes and recreate them. Also you can pick up some performance, if you can split the one big file into smaller files and importing them concurrently by specifying TABLOCK after dropping the indexes. Make sure 'select into/bulkcopy' option is set to true.
|||

Along with the other suggestions, also consider changing the recovery model of the database to BULK LOGGED or SIMPLE recovery.

HTH!

|||

Thanks sankar and rich for your suggestions.

Actually there are 9 Non-Clustered indexes and a Clustered index associated with the Primary key on that huge table.

I can drop the non-clustered indexes, But, the Clustered index is associated with Primary key. How can i drop the clustered index, by having primary key on the table.

If i drop the Primary key consraint , then clustered index will be removed. But inserting data on a table without primary key , can lead into data inconsistency ( i mean, some duplicates/null can come into the table).

Thanks for your help.

|||

As Sankar mentioned, if you want a primary key or indeed any constraint to enforce uniqueness you will have an index. This is not something you can change.

You are right in saying that having it protects the integrity of your data, so unless you are able to drop the primary key, insert the data, tidy up any dupes and then reapply the primary key you're going to have to live with the fact that you have an index.

Drop automatically-created indexes

Hi
We have a SQL Server 2000 database. On three of the tables (used by an
ERP-system) there are a huge number of automatically-created indexes (we are
hitting the maximum limit). These indexes slow down inserts and updates, and
prevent us from create other (more meaningful) indexes. We have tried to drop
these indexes, but we can’t.
Is there a way to drop/delete automatically-created indexes, or to turn this
functionality off?
Or to you have other suggestions?
Here is some more information about automatically-created indexes:
http://www.microsoft.com/sql/techinf...utoindexes.asp
--JeyLey
Jey
There are called Statistics. SQL Server creates statistics on columns in
order to
retrieve the data in more efficient way. This article makes it clear how
does it work.You can run sp_updatestatistics system stored procedure to
update automatically reated statistics.For more details please refer to BOL.
eyLey" <JeyLey@.nospam.nospam> wrote in message
news:D288B8DC-2E00-4E68-ADAC-7DBD3DF16837@.microsoft.com...
> Hi
> We have a SQL Server 2000 database. On three of the tables (used by an
> ERP-system) there are a huge number of automatically-created indexes (we
are
> hitting the maximum limit). These indexes slow down inserts and updates,
and
> prevent us from create other (more meaningful) indexes. We have tried to
drop
> these indexes, but we cant.
> Is there a way to drop/delete automatically-created indexes, or to turn
this
> functionality off?
> Or to you have other suggestions?
> Here is some more information about automatically-created indexes:
> http://www.microsoft.com/sql/techinf...utoindexes.asp
> --JeyLey
|||In addition to Uri's post:
You can drop these auto-created statistics using the DROP STATISTICS command. But note that
auto-created statistics can benefit performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JeyLey" <JeyLey@.nospam.nospam> wrote in message
news:D288B8DC-2E00-4E68-ADAC-7DBD3DF16837@.microsoft.com...
> Hi
> We have a SQL Server 2000 database. On three of the tables (used by an
> ERP-system) there are a huge number of automatically-created indexes (we are
> hitting the maximum limit). These indexes slow down inserts and updates, and
> prevent us from create other (more meaningful) indexes. We have tried to drop
> these indexes, but we can't.
> Is there a way to drop/delete automatically-created indexes, or to turn this
> functionality off?
> Or to you have other suggestions?
> Here is some more information about automatically-created indexes:
> http://www.microsoft.com/sql/techinf...utoindexes.asp
> --JeyLey

Drop automatically-created indexes

Hi
We have a SQL Server 2000 database. On three of the tables (used by an
ERP-system) there are a huge number of automatically-created indexes (we are
hitting the maximum limit). These indexes slow down inserts and updates, and
prevent us from create other (more meaningful) indexes. We have tried to dro
p
these indexes, but we can’t.
Is there a way to drop/delete automatically-created indexes, or to turn this
functionality off?
Or to you have other suggestions?
Here is some more information about automatically-created indexes:
http://www.microsoft.com/sql/techin...autoindexes.asp
--JeyLeyJey
There are called Statistics. SQL Server creates statistics on columns in
order to
retrieve the data in more efficient way. This article makes it clear how
does it work.You can run sp_updatestatistics system stored procedure to
update automatically reated statistics.For more details please refer to BOL.
eyLey" <JeyLey@.nospam.nospam> wrote in message
news:D288B8DC-2E00-4E68-ADAC-7DBD3DF16837@.microsoft.com...
> Hi
> We have a SQL Server 2000 database. On three of the tables (used by an
> ERP-system) there are a huge number of automatically-created indexes (we
are
> hitting the maximum limit). These indexes slow down inserts and updates,
and
> prevent us from create other (more meaningful) indexes. We have tried to
drop
> these indexes, but we cant.
> Is there a way to drop/delete automatically-created indexes, or to turn
this
> functionality off?
> Or to you have other suggestions?
> Here is some more information about automatically-created indexes:
> http://www.microsoft.com/sql/techin...autoindexes.asp
> --JeyLey|||In addition to Uri's post:
You can drop these auto-created statistics using the DROP STATISTICS command
. But note that
auto-created statistics can benefit performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JeyLey" <JeyLey@.nospam.nospam> wrote in message
news:D288B8DC-2E00-4E68-ADAC-7DBD3DF16837@.microsoft.com...
> Hi
> We have a SQL Server 2000 database. On three of the tables (used by an
> ERP-system) there are a huge number of automatically-created indexes (we a
re
> hitting the maximum limit). These indexes slow down inserts and updates, a
nd
> prevent us from create other (more meaningful) indexes. We have tried to d
rop
> these indexes, but we can't.
> Is there a way to drop/delete automatically-created indexes, or to turn th
is
> functionality off?
> Or to you have other suggestions?
> Here is some more information about automatically-created indexes:
> http://www.microsoft.com/sql/techin...autoindexes.asp
> --JeyLey

Drop automatically-created indexes

Hi
We have a SQL Server 2000 database. On three of the tables (used by an
ERP-system) there are a huge number of automatically-created indexes (we are
hitting the maximum limit). These indexes slow down inserts and updates, and
prevent us from create other (more meaningful) indexes. We have tried to drop
these indexes, but we canâ't.
Is there a way to drop/delete automatically-created indexes, or to turn this
functionality off?
Or to you have other suggestions?
Here is some more information about automatically-created indexes:
http://www.microsoft.com/sql/techinfo/tips/administration/autoindexes.asp
--JeyLeyJey
There are called Statistics. SQL Server creates statistics on columns in
order to
retrieve the data in more efficient way. This article makes it clear how
does it work.You can run sp_updatestatistics system stored procedure to
update automatically reated statistics.For more details please refer to BOL.
eyLey" <JeyLey@.nospam.nospam> wrote in message
news:D288B8DC-2E00-4E68-ADAC-7DBD3DF16837@.microsoft.com...
> Hi
> We have a SQL Server 2000 database. On three of the tables (used by an
> ERP-system) there are a huge number of automatically-created indexes (we
are
> hitting the maximum limit). These indexes slow down inserts and updates,
and
> prevent us from create other (more meaningful) indexes. We have tried to
drop
> these indexes, but we can?t.
> Is there a way to drop/delete automatically-created indexes, or to turn
this
> functionality off?
> Or to you have other suggestions?
> Here is some more information about automatically-created indexes:
> http://www.microsoft.com/sql/techinfo/tips/administration/autoindexes.asp
> --JeyLey|||In addition to Uri's post:
You can drop these auto-created statistics using the DROP STATISTICS command. But note that
auto-created statistics can benefit performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JeyLey" <JeyLey@.nospam.nospam> wrote in message
news:D288B8DC-2E00-4E68-ADAC-7DBD3DF16837@.microsoft.com...
> Hi
> We have a SQL Server 2000 database. On three of the tables (used by an
> ERP-system) there are a huge number of automatically-created indexes (we are
> hitting the maximum limit). These indexes slow down inserts and updates, and
> prevent us from create other (more meaningful) indexes. We have tried to drop
> these indexes, but we can't.
> Is there a way to drop/delete automatically-created indexes, or to turn this
> functionality off?
> Or to you have other suggestions?
> Here is some more information about automatically-created indexes:
> http://www.microsoft.com/sql/techinfo/tips/administration/autoindexes.asp
> --JeyLey