insert operation time out

I got the following trobule shooing:
when I insert a huge table , I usually got ' time out ' error,
to this ,I find the question SQL through SQL profile as follows:
insert into hit(.....)
and this find insert operation is blocking
this table include identified column and it's primary key,
It has about1800 million records.
and now how to slove this time out question?
thanks for any ideas!you have 1800 milion records to insert? How are you doing it now? What is
the source? Please post DDL script for target.
MC
<lovexueer@.gmail.com> wrote in message
news:1144749849.058444.299630@.u72g2000cwu.googlegroups.com...
>I got the following trobule shooing:
> when I insert a huge table , I usually got ' time out ' error,
> to this ,I find the question SQL through SQL profile as follows:
> insert into hit(.....)
> and this find insert operation is blocking
> this table include identified column and it's primary key,
> It has about1800 million records.
>
> and now how to slove this time out question?
>
> thanks for any ideas!
>|||> and now how to slove this time out question?
When you have timeouts due to blocking, the first step is to identify and
correct the cause of the blocking.
One possible cause is long-running SELECT queries. Keeping in mind that
performance and concurrency go hand-in-hand, you might find that tuning
queries improves query performance and reduces blocking episodes.
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1144749849.058444.299630@.u72g2000cwu.googlegroups.com...
>I got the following trobule shooing:
> when I insert a huge table , I usually got ' time out ' error,
> to this ,I find the question SQL through SQL profile as follows:
> insert into hit(.....)
> and this find insert operation is blocking
> this table include identified column and it's primary key,
> It has about1800 million records.
>
> and now how to slove this time out question?
>
> thanks for any ideas!
>|||maybe I mistake it, I meant this table total records is 1800 milion.|||I am sure I have no long-running SELECT queries,
only a lot of running insert operation,
this table total records is 1800 milion, when I inset a record ,it need
much time unitl time out,
And I viewed all blocks and found only insert block as SQL statement
'insert into hit.......',
anyone have other solutions?
anyway ,best regards!|||<lovexueer@.gmail.com> wrote in message
news:1144765954.572739.26760@.z34g2000cwc.googlegroups.com...
> I am sure I have no long-running SELECT queries,
> only a lot of running insert operation,
> this table total records is 1800 milion, when I inset a record ,it need
> much time unitl time out,
> And I viewed all blocks and found only insert block as SQL statement
> 'insert into hit.......',
>
Can you post the full syntax.
It looks like it may be trying to insert 1800 million rows and commit it as
a single batch.
Look into using BULK INSERT or BCP with a smaller batch size.

> anyone have other solutions?
> anyway ,best regards!
>|||timeouts with inserts to large tables is very often index issues.
what are your indexes? do you have clustered indexes?
can you get rid of indexes?|||> And I viewed all blocks and found only insert block as SQL statement
> 'insert into hit.......',
How many rows are you inserting per transaction? I wouldn't expect an
atomic inserts to block each other for long periods of time. However, you
might have timeout problems if your I/O system is can't keep up with high
write demands. Make sure that data and log are on separate disks.
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1144765954.572739.26760@.z34g2000cwc.googlegroups.com...
>I am sure I have no long-running SELECT queries,
> only a lot of running insert operation,
> this table total records is 1800 milion, when I inset a record ,it need
> much time unitl time out,
> And I viewed all blocks and found only insert block as SQL statement
> 'insert into hit.......',
> anyone have other solutions?
> anyway ,best regards!
>|||thanks everyone!
this table is full of 1800 milion,
and it include Identified column as primay key ,it's clustered index
only when client program post a SQL statemt as follows:
insert into hit ........
running this SQL statemnt in client program is very frequently,
I pay attention to many blocks in this insert operation.
and sometimes it post error 'time out'
can anyone help me?|||per transaction is only one rows.
but per transaction is very frequently,
maybe I/O system really can't keep up with demands.