Time Out & Long Run Processes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Time Out & Long Run Processes

Author

Topic

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-08-07 : 10:58:47

Hi,

We are experiencing issue with one of our Production Servers. Whenever we run some tests on application query response times the production server timed out repeatedly. Whenever there is a heavy load on the server, we continue to have these issues (since the combination of all databases onto a single cluster). It is not a single query or stored procedure, on busy production days we notice time out and long running processes on multiple applications. The same queries and stored procedures which time out the connection from the application always run fine and fast through Sql Server management studio. so it is not a query construction issue.

Could you please help me, as it is creating us much problems.

Sreenivasulu.p,
Mail ID: sreenivas.puliverthi@gmail.com

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-07 : 11:14:13

Common reasons I've seen for this sort of problem are
1) The application starts using a query not optimal for the index
2) Hardware changes\ Configuration changes
3) Increased load

Are you seeing memory pressure at the same time?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-08-07 : 11:46:50

Your 1st point 1) The application starts using a query not optimal for the index, but the Query running fine on SSMS. Coming to H/W changes\ Configuration changes, means whether we need to increase the RAM or anything. Query Timeout has alredy been set to '0' (Unlimited)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-07 : 12:22:43

Parameter sniffing?

Could be that the sp or whatever has optimized itself for a poor choice of params.

Post the code?
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-08 : 01:50:01

jayam.cnu, You may have set Query Timeout on SQL Server to 0 - but the application may have an application timeout.
Could you run the query both from SSMS and the app - and compare the Execution plans - are they the same?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources