Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction timeout happening on inserting huge data Expand / Collapse
Author
Message
Posted Saturday, March 15, 2014 6:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 11:29 PM
Points: 26, Visits: 69
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
Post #1551470
Posted Sunday, March 16, 2014 4:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 15,524, Visits: 27,905
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1551551
Posted Monday, March 17, 2014 4:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 69, Visits: 456

If you can, post the actual execution plan.
You might get a few pointers here as to why the code might be slow.
Post #1551682
Posted Monday, March 17, 2014 7:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
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?
Post #1551743
Posted Tuesday, March 18, 2014 8:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 11:29 PM
Points: 26, Visits: 69
Hi,
There is one clustered index on each table .These clustered indexes are also the identity columns
Post #1552198
Posted Tuesday, March 18, 2014 8:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 11:29 PM
Points: 26, Visits: 69
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.
Post #1552201
Posted Tuesday, March 18, 2014 8:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
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_*.
Post #1552214
Posted Tuesday, March 18, 2014 10:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 15,524, Visits: 27,905
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1552296
Posted Friday, March 21, 2014 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 11:29 PM
Points: 26, Visits: 69
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 .
Post #1553563
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse