SQL Timeout

Question

Post reply

SQL Timeout

andysfreeemail

Mr or Mrs. 500

Points: 563
More actions
February 4, 2010 at 2:30 am

#216417

I have a VB.NET application that is timing out on a query.
When a user changes data in my application usignt he front end, a notification mechanism tells all other clients (4 running in this scenario) to refresh their copy of the record just changed.
So the process is
Main client issues an update statement via stored proc (in a transaction which it closes)
main client refrehses its local copy of its data (call to stored proc containing a single select statement)
All othe clients issue the same SQL (call to the same stored proc)
I would have thought there was no problem in severla users querying exactly the same data in the same way at the 'same' time (or microseconds after each other).
The query times out immediately (i.e. in VB the timeout is set to 30 seconds but it never gets close to this before returning the error)
Any ideas?
Cheers,
Andrew

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Can you go into break mode and capute your query and try ti execute it from query analyzer?

Ford you query execute from SQL, if so something may be wrong with you code?

Are you using embebed SQL or are you calling a Stored procedure and/or Function? If using Embeded SQL convert the SQL to a Stored Procedure.

Also I would advise you to check for locking and blocking to determine who is interfering with whom. Check the DMV's to determine the actually Statements that are being executed, i.e SP_who2, sp_lock3 & DBCC(INPUTBUFFER(SPID) in pre-SQL Server 2005 versions.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Fazalul Haque SSC-Addicted Points: 401 More actions · Answer 2

Fazalul Haque

SSC-Addicted

Points: 401

February 4, 2010 at 3:09 am

#1113575

Try adding WITH (NOLOCK) in your SELECT query

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

The WITH (NOLOCK) can be very helpful. It is equizilant to the Read Uncomitted Mode REad Committed is the default mode.

WITH (NOLOCK) will return all uncommitted records, so if returing dirty read is not a concern then this can be very helpful.

If however you do not want to include records that could be rolled back then go for it.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

andysfreeemail Mr or Mrs. 500 Points: 563 More actions · Answer 4

Thanks for all the replies. As a testing exercise I added NOWAIT but that didn't sort the problem. I've done some more investigating and found that every now and then the query that notrmally takes <1s is taking 47s.

When I run sp_who2 the waiting session is showing as SUSPENDED. There is no blocking recorded.

I've selected from sys.dm_os_waiting_tasks and my process is waiting for IO_Completion (as are the other sessions that are querying hte same data at the same time)

Given that the query runs so well 99% of the time I don't think the query is at fault. Any further ideas anyone?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

The DB server, is it hosting only the Database or does it have other applications as well? You could probably be experiencing IO bottleneck. Not sure though...

-Roy

andysfreeemail Mr or Mrs. 500 Points: 563 More actions · Answer 6

Hi,

The server hosts only DB - no applications. It is Server 2008 running SQL 2008 (64bit).More monitoring shows that it is only ever this query that hangs - a number of other queries are also called by several users at once and these complete without incident.

The only difference with the problem query is the use of a #Temp table and also the use of a FOR XML PATH construct. Wonder if either of these are known to cause issue?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

In SQL 2008 the TempDB usuage is very high compared to SQL 2000. If your disks are not configured properly you might find some IO bottlenecks. Couple of other thing you could check is parallelism and execution plan. If the table is getting updated/inserted at a high rate, there is a chance that it could take a bad plan.

-Roy

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

It sounds like the optimizer is using a cached plan which doesn't necessarily meet the needs of the criteria for the current query. If you're using a stored proc as the interface, try adding the WITH RECOMPILE hint. If you're using embedded SQL in your app, shame on you. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andysfreeemail Mr or Mrs. 500 Points: 563 More actions · Answer 9

Jeff/Roy,

Thanks for the suggestions. Will try them out and let you know how I get on. I'm suprised that any of these things can intermittently add 40 secs to the execution time though.

(and yes I'm using Stored procedures)

Thanks,

Andrew