Transaction timeout happening on inserting huge data

Question

Transaction timeout happening on inserting huge data

sunil88_pal88

SSC Eights!

Points: 834
More actions
March 15, 2014 at 6:47 am

#305299

Hi Guys,
I am stuck with a issue . I am using transaction scope for transaction purpose(that's a business requirement).There are a two tables in which i am inserted huge data at a time.In table 1 i am inserting 90,000 records and in table 2 5000 record at a time.I have created stored procedures for inserting data.I am using UDT's for passing data to stored procedures.Inside stored procedures i am using 'insert statement with select from UDT's' to perform insertion operation.As the data is increasing in the tables ,the time to insert data is increasing ,resulting in timeout of transaction(transaction scope max time is 10 min).
After doing some research i have come to two solutions ,one is to disable the indexes before insertion and enabling after insertion.The other is to use tablock hint on table during insertion
Is this the correct way to do ,will they work in transaction .And if the i disable the index and after that during insertion some error occurs,will the indexes will be enabled automatically.
Please guide me

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

For insert operations, frequently disabling the indexes is a good idea.

If there's a failure, no, the indexes won't automatically reenable. You'll need to make sure you have something in place to ensure they get turned back on, a second script, a step that runs on failure of the first, something.

As for speeding up inserts, I'd suggest looking to see what is causing them to run slow in order to understand what you do to speed them up. Look at the wait statistics to see what's slowing things down. Look at the execution plan to see how the inserts are occurring. You may even find that the right clustered index can speed up insert operations (yeah, sometimes having the right index there is better than disabling). But, you have to identify why it's running slow before you can make any of these types of choices.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

CKX SSCrazy Points: 2519 More actions · Answer 2

If you can, post the actual execution plan.

You might get a few pointers here as to why the code might be slow.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 3

Even on a low end server, it shouldn't take 10 minutes to insert 90,000 rows into one table and 5,000 rows into another one. I suspect what's holding it up is blocking. Attempting to perform mass insert operations on a clustered table with a non-sequential key (ex: clustered on customer_id) will result in an order of magnitude more I/O and locking on both the table and non-clustered indexes.

What is the clustered key on the tables you're inserting?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

sunil88_pal88 SSC Eights! Points: 834 More actions · Answer 4

Hi,

There is one clustered index on each table .These clustered indexes are also the identity columns

sunil88_pal88 SSC Eights! Points: 834 More actions · Answer 5

Hi Guys ,

There is one more thing I forget to tell that on development server ,everything is fine .Data is uploaded in almost 3 minutes, but on production it is causing timeout issues.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 6

sunil88_pal88 (3/18/2014)
Hi Guys ,
There is one more thing I forget to tell that on development server ,everything is fine .Data is uploaded in almost 3 minutes, but on production it is causing timeout issues.

You need to examine process status during this insert operation. For this you can use Activity Monitor (a SSMS toolbar button) or there is also a stored procedure written by Adam Machanic.

http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx

Identify which session is your insert operation, look for any processes that are blocking it. Also make note of the 'Task State' and 'Wait Type'. Maybe it's not blocked waiting for a lock but rather by CXPACKET (blocking itself while waiting for parallel thread completion). Or just waiting for IO_*.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 7

sunil88_pal88 (3/18/2014)
Hi Guys ,
There is one more thing I forget to tell that on development server ,everything is fine .Data is uploaded in almost 3 minutes, but on production it is causing timeout issues.

That can be attributed to all sorts of things. Differences in the data, differences in the statistics, differences in the load and blocking processes, different resource contention... It could be even more things. I'd focus on identifying why it's running slow in production and not try to compare between two systems too much unless they're both extremely well known systems.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sunil88_pal88 SSC Eights! Points: 834 More actions · Answer 8

Thanks for your suggestions guys .My problem is solved .Actually on production there was a memory issue on application side which was causing transaction timeout .