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

Drop and create procedure

I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?

Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...

ThanksWhat is the trigger for?

There's no way I know of to disable the trigger...but what's the big deal with

DROP TRIGGER

EXEC Sproc

CREATE TRIGGER

The only thing is, whatever the trigger is for, it's there for a reason, and wouldn't dropping cause you any data integrity issues?|||Actually, it can be done in SQL 2000. I have never tried it, but there is ALTER TABLE syntax for enabling/disabling a trigger.

As Brett pointed out, though, you want to be sure that not only your process but any other process accessing the table will not be adversely affected by the sudden disabling of the trigger.|||No Sheet!

Still, once I've put a trigger in place, I've never had a need (or want) to remove it.

You might want to consider some alternatives

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT
AS
BEGIN
UPDATE t SET t.Col2 = t.Col2 * 2
FROM myTable99 t JOIN inserted i ON t.Col1 = i.Col1
END
GO

INSERT INTO myTable99(Col2) SELECT 1

SELECT * FROM myTable99
GO

ALTER TABLE myTable99
DISABLE TRIGGER myTrigger99
GO

INSERT INTO myTable99(Col2) SELECT 1

SELECT * FROM myTable99
GO

ALTER TABLE myTable99
ENABLE TRIGGER myTrigger99
GO
INSERT INTO myTable99(Col2) SELECT 2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO

Drop all column extended propterties

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)

Drop all column extended propterties

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)