Insert query timing out

This questions pertains to the administration aspect.
I have a table with 7 million records. We have defined about 5 indexes
according to our
reporting needs.The queries are performing satisfactorily.But offlate,we
have observed
that our insert queries are timing out. we are trying to insert a record
into the table
three times with a time out of 15 seconds everytime.the insert query is
timing out even
after three times. we are trying to find if there is anything wrong with the
table
structure.The table has about 30 columns and is designed properly.
I have run dbcc show contig on the table and observed that there is external
fragmentation with the table according to the statistics.
Please let me if my observation is wrong based on the statistics.
DBCC SHOWCONTIG scanning 'xxxx' table...
Table: 'xxxx' (1767677345); index ID: 1, database ID: 24 TABLE
level scan performed.
- Pages Scanned........................: 665043
- Extents Scanned.......................: 84073
- Extent Switches.......................: 181138
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 45.89% [83131:181139]
- Logical Scan Fragmentation ..............: 14.47%
- Extent Scan Fragmentation ...............: 24.48%
- Avg. Bytes Free per Page................: 1130.5
- Avg. Page Density (full)................: 86.03%
there is no fill factor defined on the clustered index.
i have run dbcc reindex with no fill factor but it did not any good w.r.t
insert query
time out.
Do we need to change the fill factor from o to 80?
another question,why are we having external fragmentation on the table?
and what can be done to stop external fragmentation on the table?
Does backup play any role in the table fragmentation?
please advise us on what can be done to avoid insert query time out?
every month we see about 4 million records in this table.Hi
You don't give the DDL for the table and indexes which would be very useful
information to have when answering this question. You also don't say what
updates/deletes occur on this table, or where you are inserting the new data.
Have you checked for blocking?
John
"Deepak" wrote:
> This questions pertains to the administration aspect.
> I have a table with 7 million records. We have defined about 5 indexes
> according to our
> reporting needs.The queries are performing satisfactorily.But offlate,we
> have observed
> that our insert queries are timing out. we are trying to insert a record
> into the table
> three times with a time out of 15 seconds everytime.the insert query is
> timing out even
> after three times. we are trying to find if there is anything wrong with the
> table
> structure.The table has about 30 columns and is designed properly.
> I have run dbcc show contig on the table and observed that there is external
> fragmentation with the table according to the statistics.
> Please let me if my observation is wrong based on the statistics.
> DBCC SHOWCONTIG scanning 'xxxx' table...
> Table: 'xxxx' (1767677345); index ID: 1, database ID: 24 TABLE
> level scan performed.
> - Pages Scanned........................: 665043
> - Extents Scanned.......................: 84073
> - Extent Switches.......................: 181138
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 45.89% [83131:181139]
> - Logical Scan Fragmentation ..............: 14.47%
> - Extent Scan Fragmentation ...............: 24.48%
> - Avg. Bytes Free per Page................: 1130.5
> - Avg. Page Density (full)................: 86.03%
> there is no fill factor defined on the clustered index.
> i have run dbcc reindex with no fill factor but it did not any good w.r.t
> insert query
> time out.
> Do we need to change the fill factor from o to 80?
> another question,why are we having external fragmentation on the table?
> and what can be done to stop external fragmentation on the table?
> Does backup play any role in the table fragmentation?
> please advise us on what can be done to avoid insert query time out?
> every month we see about 4 million records in this table.
>|||Hello John
Thanks for replying. I have checked for blocking and there are no blocks.I
have run sql profiler and have not observed any locks during the time period
on this table .
There are only updates and inserts. there are no delete operations on this
table.
Updates are very minimum. inserts happen every other second during the peak
times.we have about 4 million transactions per month on this table. the issue
is only on inserting . do you believe that there is external fragmentation on
this table?
what is your take on changing the fill factor for the clustered index? this
is a normal table with 30 or more columns. the issue is only with inserts and
not with select queries.
Please advise us.
"John Bell" wrote:
> Hi
> You don't give the DDL for the table and indexes which would be very useful
> information to have when answering this question. You also don't say what
> updates/deletes occur on this table, or where you are inserting the new data.
> Have you checked for blocking?
> John
>
> "Deepak" wrote:
> > This questions pertains to the administration aspect.
> > I have a table with 7 million records. We have defined about 5 indexes
> > according to our
> >
> > reporting needs.The queries are performing satisfactorily.But offlate,we
> > have observed
> >
> > that our insert queries are timing out. we are trying to insert a record
> > into the table
> >
> > three times with a time out of 15 seconds everytime.the insert query is
> > timing out even
> >
> > after three times. we are trying to find if there is anything wrong with the
> > table
> >
> > structure.The table has about 30 columns and is designed properly.
> > I have run dbcc show contig on the table and observed that there is external
> >
> > fragmentation with the table according to the statistics.
> > Please let me if my observation is wrong based on the statistics.
> > DBCC SHOWCONTIG scanning 'xxxx' table...
> > Table: 'xxxx' (1767677345); index ID: 1, database ID: 24 TABLE
> > level scan performed.
> > - Pages Scanned........................: 665043
> > - Extents Scanned.......................: 84073
> > - Extent Switches.......................: 181138
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 45.89% [83131:181139]
> > - Logical Scan Fragmentation ..............: 14.47%
> > - Extent Scan Fragmentation ...............: 24.48%
> > - Avg. Bytes Free per Page................: 1130.5
> > - Avg. Page Density (full)................: 86.03%
> >
> > there is no fill factor defined on the clustered index.
> > i have run dbcc reindex with no fill factor but it did not any good w.r.t
> > insert query
> >
> > time out.
> > Do we need to change the fill factor from o to 80?
> > another question,why are we having external fragmentation on the table?
> > and what can be done to stop external fragmentation on the table?
> > Does backup play any role in the table fragmentation?
> > please advise us on what can be done to avoid insert query time out?
> > every month we see about 4 million records in this table.
> >|||"Deepak" <Deepak@.discussions.microsoft.com> wrote in message
news:28B684B3-DE3F-44BF-AEF5-39512DEAEBBD@.microsoft.com...
> Hello John
> Thanks for replying. I have checked for blocking and there are no blocks.I
> have run sql profiler and have not observed any locks during the time
> period
> on this table .
> There are only updates and inserts. there are no delete operations on this
> table.
> Updates are very minimum. inserts happen every other second during the
> peak
> times.we have about 4 million transactions per month on this table. the
> issue
> is only on inserting . do you believe that there is external fragmentation
> on
> this table?
> what is your take on changing the fill factor for the clustered index?
> this
> is a normal table with 30 or more columns. the issue is only with inserts
> and
> not with select queries.
What do you mean by "timeout". SQL Server doesn't time-out queries, client
programs do. So what's the client program and how long is the timeout?
What else is going on? Are you very, very sure there's no blocking. This
sounds very much like a blocking problem.
The measures you propose might decrease transaction times slightly, but it's
unlikely that they will resolve your timeout issue.
David|||Hello
The client program is a vb component. the time out is 15 seconds. I try the
insert query three times . I am positive that there is no blocking.
thanks
Deepak
"David Browne" wrote:
> "Deepak" <Deepak@.discussions.microsoft.com> wrote in message
> news:28B684B3-DE3F-44BF-AEF5-39512DEAEBBD@.microsoft.com...
> > Hello John
> > Thanks for replying. I have checked for blocking and there are no blocks.I
> > have run sql profiler and have not observed any locks during the time
> > period
> > on this table .
> > There are only updates and inserts. there are no delete operations on this
> > table.
> > Updates are very minimum. inserts happen every other second during the
> > peak
> > times.we have about 4 million transactions per month on this table. the
> > issue
> > is only on inserting . do you believe that there is external fragmentation
> > on
> > this table?
> > what is your take on changing the fill factor for the clustered index?
> > this
> > is a normal table with 30 or more columns. the issue is only with inserts
> > and
> > not with select queries.
> What do you mean by "timeout". SQL Server doesn't time-out queries, client
> programs do. So what's the client program and how long is the timeout?
> What else is going on? Are you very, very sure there's no blocking. This
> sounds very much like a blocking problem.
> The measures you propose might decrease transaction times slightly, but it's
> unlikely that they will resolve your timeout issue.
> David
>
>|||Hi Deepak
I am surprised you say there is no locking or blocking, the process that
inserts should take out locks. Have you looked at lock escalation, lock
timeouts and deadlocks in SQL profiler? You may also want to look at the
errors/warnings and log at statement level to show more information about any
triggers being executed. Also look at the output from sp_who2 when the
process is running. You may want to try sp_blocker_pss80 see
http://support.microsoft.com/kb/271509.
I don't think fragmentation is the issue, only one in 4 extents are not
contiguous and you still have the problem after doing a re-index. You don't
say when the DBCC SHOWCONTIG information was taken! You may want to try
dropping the indexes to see what effect that has.
Have you checked perfmon information regarding the number of read/writes and
their duration and current disc queue lengths? You should also look at CPU
and memory usage to see if there are bottle necks there. Check out articles
on http://www.sql-server-performance.com/ on how to detect hardware
bottlenecks.
Are your log and data files on different spindles? Also see if you can
separate your indexes onto their own spindles as well!
If the source of these inserts is a data file you may want to review if you
can delay them until a quiet period and use BULK INSERT or BCP to populate
the information. You may also wish to look at partitioning the table.
Make sure that you are not continually expanding the data and log files, if
you expansion is too often you may wish to increase how much they expands.
Also if you continually shrink the data/log files there may be fragmentation
of the files on disc, check with the windows defragmentation program to see
if this is the case.
Check that your transactions are not too long. Make sure you are not
dependent on user input for them to complete. DBCC OPENTRAN will show open
transactions.
John
"Deepak" wrote:
> Hello
> The client program is a vb component. the time out is 15 seconds. I try the
> insert query three times . I am positive that there is no blocking.
> thanks
> Deepak
> "David Browne" wrote:
> >
> > "Deepak" <Deepak@.discussions.microsoft.com> wrote in message
> > news:28B684B3-DE3F-44BF-AEF5-39512DEAEBBD@.microsoft.com...
> > > Hello John
> > > Thanks for replying. I have checked for blocking and there are no blocks.I
> > > have run sql profiler and have not observed any locks during the time
> > > period
> > > on this table .
> > > There are only updates and inserts. there are no delete operations on this
> > > table.
> > > Updates are very minimum. inserts happen every other second during the
> > > peak
> > > times.we have about 4 million transactions per month on this table. the
> > > issue
> > > is only on inserting . do you believe that there is external fragmentation
> > > on
> > > this table?
> > > what is your take on changing the fill factor for the clustered index?
> > > this
> > > is a normal table with 30 or more columns. the issue is only with inserts
> > > and
> > > not with select queries.
> >
> > What do you mean by "timeout". SQL Server doesn't time-out queries, client
> > programs do. So what's the client program and how long is the timeout?
> > What else is going on? Are you very, very sure there's no blocking. This
> > sounds very much like a blocking problem.
> >
> > The measures you propose might decrease transaction times slightly, but it's
> > unlikely that they will resolve your timeout issue.
> >
> > David
> >
> >
> >