x

Table Inserts timing out

We have a transacations log table dbo.LOG that has 8M rows in it. We add about 25k rows to the table on a daily basis.

Edit

Ther are no triggers on this table.

Indices:

/* LogId is INT */
ALTER TABLE [dbo].[Log] ADD CONSTRAINT [Log_PK] PRIMARY KEY NONCLUSTERED
(
    [LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/* ClientPostingDateTime is datetime */
CREATE NONCLUSTERED INDEX [IX_Log_ClientPostingDateTime] ON [dbo].[Log]
(
    [ClientPostingDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/* TransactionTypeCode is INT */
CREATE NONCLUSTERED INDEX [IX_Log_TransactionTypeCode] ON [dbo].[Log]
(
    [TransactionTypeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The insert/updates are coming from a ASP.NET web site on a different server. Both the web server and the SQL server are in the same physical location (possibly on the same rack).

The web site call to the database is timing out. Upto 25 edits (INSERTS / UPDATES) are timing out on this every day.

How do I go about identifying and fixing this issue?

more ▼

asked Oct 21 '09 at 04:35 PM in Default

Raj More gravatar image

Raj More
1.7k 77 82 84

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I would collect the wait states and queues performance monitor counters to see specifically where the slow downs are occurring. Also, if you can see when the timeouts are occurring, it's possible to get the execution plans for the queries being executed straight out of the cache using DMV's. That way you can see if there's something up with the execution plans for these queries different than the ones that succeed.

more ▼

answered Oct 21 '09 at 08:02 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.4k 19 21 74

How do I collect wait states and queues performance statistics?
Oct 22 '09 at 09:32 AM Raj More
You can use performance monitor and you can query dynamic management views. I suggest reading articles by Andrew Kelly in SQL Server Magazine. Here's one: http://www.sqlmag.com/Articles/ArticleID/96746/96746.html?Ad=1
Oct 22 '09 at 08:52 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

What isolation level/ table hint are you using ? Read Commited is the default, but read uncommited or using the hint with (nolock) lets you read uncommited 'dirty' data from the destination table ignoring simple locks. ( if locks are your problem, try this).

Does your queries are transactioned ? A safe block of operations within a transaction will place locks on everything you touch, so maybe you dont need this rigid control over a log table.

Check system blocks/ locks and wait types to see if several queries are waiting for any shared resource. Some resource could be locking the table for a long time and causing timeouts.

Maybe you are experiencing IO/CPU/MEMORY/NETWORK bottlenecks ( but usually those problems would afect the whole system.

While updating avoid using any hint like with (tablock).

Check for any pattern of those lost logs, maybe they happen exactly when your full backup operation is running.

Let us know if this help.

more ▼

answered Oct 21 '09 at 05:10 PM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left

Can you give some more info.

Are there any indexes on the table? If so what fields (with data types).

Are there any triggers?

Where are the insert/updates coming from? App on same physical server, another server? What is the connection between the db server and app server?

What is timing out? The query or the call to the database?

more ▼

answered Oct 21 '09 at 04:50 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.5k 43 49 76

i have added the detail that you have asked for
Oct 21 '09 at 05:26 PM Raj More
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x976
x241

asked: Oct 21 '09 at 04:35 PM

Seen: 9203 times

Last Updated: Oct 21 '09 at 05:31 PM