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.|||per transaction insert only a row,but transactions are very frequently
,
maybe because of I/O system can't keep up with high wirte demands.|||To summarize the information you've supplied:
1) You have a large table (1.8 billion rows).
2) The identity column primary key is the clustered index
3) You insert one row per transaction
4) You have multiple clients inserting simultaneously
5) You observe frequent blocking and sometimes timeouts
Do you have any other indexes on the table other than the clustered index?
Are data and log files on the same disk/array?
Execute sp_lock via query analyzer to identify the blocking resources. Take
a look at the avg. disk queue length via Performance Monitor. This should
average no more that 2 times the number of physical disks in the array.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1144912220.346986.109010@.u72g2000cwu.googlegroups.com...
> per transaction insert only a row,but transactions are very frequently
> ,
> maybe because of I/O system can't keep up with high wirte demands.
>|||thanks you ,
your five point is very right,
this table only index is clustered index.
and data and log on the same disk array raid5.
I thinks IO system is so slowly than it comes out 'time out'.
by the way, I have two ways:
1) drop this clustered index ,maybe I avoid this question.
2) export part of rows and delete part of rows,reduce rows of this
table ,maybe I avoid this question.
do you think so ?
Dan Guzman =E5=86=99=E9=81=93=EF=BC=9A
> To summarize the information you've supplied:
> 1) You have a large table (1.8 billion rows).
> 2) The identity column primary key is the clustered index
> 3) You insert one row per transaction
> 4) You have multiple clients inserting simultaneously
> 5) You observe frequent blocking and sometimes timeouts
> Do you have any other indexes on the table other than the clustered index?
> Are data and log files on the same disk/array?
> Execute sp_lock via query analyzer to identify the blocking resources. Ta=ke
> a look at the avg. disk queue length via Performance Monitor. This should
> average no more that 2 times the number of physical disks in the array.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <lovexueer@.gmail.com> wrote in message
> news:1144912220.346986.109010@.u72g2000cwu.googlegroups.com...
> > per transaction insert only a row,but transactions are very frequently
> > ,
> > maybe because of I/O system can't keep up with high wirte demands.
> >|||> and data and log on the same disk array raid5.
This is very bad for performance when you do a lot of inserts/updates. Data
and log should be separated, especially when you do a lot of writes.
Furthermore, since log I/O are mostly writes, the write penalty incurred by
RAID-5 is bad for performance. I strongly suggest you move the log
somewhere else. Moving the log to a mirrored drive shared with the OS would
be much better.
Also, be sure you have pre-allocated the needed space. Do not rely on
autogrow to expand your files during application processing.
>1) drop this clustered index ,maybe I avoid this question.
This will eliminate I/O for the non-leaf index pages but that's a relatively
small percentage of the I/O needed by the insert. There are many more data
pages than index pages. I suggest you keep the primary key in place. The
clustered index key is sequentially assigned (identity) so the index inserts
are sequential and efficient.
> 2) export part of rows and delete part of rows,reduce rows of this
> table ,maybe I avoid this question.
I doubt this will help much either. The number of I/Os needed to write data
will be nearly constant with a sequential clustered index key regardless of
table size. Although you will have fewer non-leaf index pages with fewer
rows, this is fairly insignificant with your narrow index key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1144979250.304108.153570@.j33g2000cwa.googlegroups.com...
thanks you ,
your five point is very right,
this table only index is clustered index.
and data and log on the same disk array raid5.
I thinks IO system is so slowly than it comes out 'time out'.
by the way, I have two ways:
1) drop this clustered index ,maybe I avoid this question.
2) export part of rows and delete part of rows,reduce rows of this
table ,maybe I avoid this question.
do you think so ?
Dan Guzman ':
> To summarize the information you've supplied:
> 1) You have a large table (1.8 billion rows).
> 2) The identity column primary key is the clustered index
> 3) You insert one row per transaction
> 4) You have multiple clients inserting simultaneously
> 5) You observe frequent blocking and sometimes timeouts
> Do you have any other indexes on the table other than the clustered index?
> Are data and log files on the same disk/array?
> Execute sp_lock via query analyzer to identify the blocking resources.
> Take
> a look at the avg. disk queue length via Performance Monitor. This should
> average no more that 2 times the number of physical disks in the array.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <lovexueer@.gmail.com> wrote in message
> news:1144912220.346986.109010@.u72g2000cwu.googlegroups.com...
> > per transaction insert only a row,but transactions are very frequently
> > ,
> > maybe because of I/O system can't keep up with high wirte demands.
> >|||thanks greate!
so Dan Guzman ,
what on hell shoulld I do?|||The first step is to move the log to a separate drive. Consider using a
bulk insert technique as Greg suggested earlier. Another option is to
increase the application command timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1145266019.471977.168970@.t31g2000cwb.googlegroups.com...
> thanks greate!
> so Dan Guzman ,
> what on hell shoulld I do?
>|||thank you ,Dan Guzman
I will try it|||>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 too late, but..
I had yersterday a problem very similar, I solved running a
maintenance Plan, thta means...reorganize dta and index pages...
As was told before , your problems sound as a index pagination
problem....That will solve your problem
regards
ALberto