Heavy bulk- insert task - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Heavy bulk- insert task

Author

Topic

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-18 : 17:12:29

I am doing heavy Bulk insert task to one Database.When i checked the Activity Monitor I saw it was suspended and Wait type:PAGEIOLATCH_EX
So it stopped itself.Do any experts have good idea about what is going on?

Appreciate for help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-18 : 17:24:12

try using your bul insert with tablock option

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-18 : 17:29:14

Can you explain what is the purpose of TABLOCK option?
And why i am getting Wait type:PAGEIOLATCH_EX

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-18 : 17:41:41

tablock locks the whole table while insert takes place.
so what does a simple search on google tell you what PAGEIOLATCH_EX is?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-18 : 17:45:30

Thanks Spirit,

but i haven't the information regarding Wait type:PAGEIOLATCH_EX

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-18 : 17:49:09

I don't have Clustered index in the table i am doing bulk insert but i have 2 non-clustered index. May be because of that you think it got
locked.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-18 : 17:51:51

http://www.examnotes.net/archive79-2002-10-72747.html

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-18 : 17:54:19

Thanks spirit.

So Do you think using TABLOCK will solve my issues? or i have to defrag the index. It is 64 GB table.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-18 : 18:00:58

first try the tablock and see.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

ns_nataly
Starting Member

13 Posts

Posted - 2008-01-21 : 08:24:18

if you can afford to disable indexes :
simple recovery mode
disable indexes
set tablock

all this together will reduce Tran.Log + faster
then rebuild indexes - check defragmentation with DBCC Showcontig

Natalia

ns_nataly
Starting Member

13 Posts

Posted - 2008-01-21 : 08:26:00

or you can try to aply partitioning : create extra partition for "insert" and then merge it to the main table

Natalia

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-21 : 09:51:06

Sorry Natalie, I didn't get what you meant. Ofcourse, Insert will take more time if you disable index .

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-21 : 11:43:59

insert will take less time if you disable index, not more.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-21 : 12:47:02

Thanks Spirit. I have non-clustered index in that database.So how it affect regard to Bulk-inserting? faster than having CLustered index.
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-21 : 12:52:27

that depends on the data you insert and how is this data ordered when inserted.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-21 : 12:53:16

Thanks Spirit

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-21 : 14:27:30

Basically if it's a massive Bulk Insert - do a TABLOCK and Bulk Insert the data in smaller batches. Put the DB into Simple Recovery Mode (if acceptable) , disable the indices and rebuild the index at the end

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-21 : 15:24:48

http://weblogs.sqlteam.com/mladenp/archive/2006/07/22/10742.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources