question

Lokesh avatar image
Lokesh asked

Facing Problem Request Time Out Error

My Application (Browser based ERP) is Running on IIS 6, Windows Server 2003 Ent, on server1 and Database is on SQL Server 2008, Windows Server 2008 on Server2. both server are interconnected through 1000 mbps switch. when we run the application it takes lot of time to retrive data or sometimes it gives timeout error or some times it runs very smoothly. we checked the connection between two servers it is fine. procedures used in application are runs quckely in SQL Server 2008 (Backend). Internet connectivity is also very good. Previously we were using SQL Server 2005 Database. What May Be the Problem ?

Lokesh

sql-server-2008
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

This could be so many things. Have you checked the HDD performance on the database server, does the application server run any other applications, are they slow? Are there regular maintenance jobs that keep the database optimised - index defrags, etc ...

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

You need to determine if the slow response is on SQL Server or on the application. To determine if it's on SQL Server, you should monitor the system. Use perfmon to gather performance metrics, capture query execution through trace, and look at the wait stats through sys.dm_os_wait_stats. With this information, you can figure out if anything is running slow. If it's running slow, why is it running slow. You can also determine that SQL Server isn't the issue, it may lie elsewhere.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

Another thing to throw into the mix is the to check your SET session options, as these can effect the execution plans that sql server uses

One issue I had is in the past the default setting on my database for the ARITHABORT statement was OFF. In most cases via SSMS it is set to ON therefore this maybe why it is ok through SSMS.

The application that was exectute this query was using the database default of OFF, once I set the default to be ON all work well through the web app.

As a test, change the setting in SSMS to OFF and see what the results are (Tools | Query options). You wil also need to clear the cache for the specfic proc by recomiling it.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.