SQL Server Performance Forum – Threads Archive

Resoure Hungry Database

Hi Group, I ran a query via my ASP page first thing in the morning and it worked. A couple of hours later, I ran the SAME query and I got a timeout error. There is another database on the same Server which is fairly large and has a large number of users. I think this database is using up all the resources and slowing things down causing my queries to timeout. How can I prove this? I’ve indexed the fields on the table I’m querying from, I’ve run UPDATE STATISTICS…etc
Is there anything else I need to do on my side before I make suggestions? TIA

You may have a similar problem to me. I’m getting timeouts, though I can’t see any bottlenecks or locks that could explain them. You could tell your connection to wait indefinitely for the query to finish. eg:- objConn.CommandTimeout = 0 I had to do this is some big Excel queries which sometimes took 15 seconds & sometimes took longer depending on system load.

Run Performance Monitor and Profiler.
Find CPU used, Avg. Disk Queue Length, SQL Server Memory, etc.
MrTim allready did that, so I suggest to begin with those monitors. Luis Martin
Moderator
SQL-Server-Performance.com

A handful article about timeouts :http://vyaskn.tripod.com/watch_your_timeouts.htm Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hello MrTim,
I could do that, but we are talking about users who would like to see the results in less than 30 seconds which is I think quite reasonable. Considering the SAME query takes 3 seconds to run in the Query Analyser, 30 seconds is ample time. Thank you for your comments.

quote:Originally posted by MrTim You may have a similar problem to me. I’m getting timeouts, though I can’t see any bottlenecks or locks that could explain them. You could tell your connection to wait indefinitely for the query to finish. eg:- objConn.CommandTimeout = 0 I had to do this is some big Excel queries which sometimes took 15 seconds & sometimes took longer depending on system load.

Confirm the level of service pack on SQL & OS on Server & web server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Some things to check or try:- If possible, run the query in READ UNCOMMITTED mode by issuing a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement before the query. If the query reads a lot of data it will be issuing lots of shared locks which will slow it down (sometimes more than you might think). Use an OLEDB connection instead of an ODBC. This is usually quicker. Check the type of cursor you’re using. You might be able to use a ForwardOnly cursor which should be quicker (if it’s not already). Put the query in a stored procedure, and EXEC it from ASP. Be sure to put SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the query.

If your processor and memory are fine, you might be having trouble with locks. Run a profiler to look at locks and deadlocks. Also look at the query and see if you can use WITH(NOLOCK). This can help if you can deal with the fact that it reads through locks which means at the time you run the query, it could have data that will have changed during the runtime of the query. Derrick Leggett

Hi Derrick,
I tried the WITH(NOLOCK) option but the query still keeps timing out.
The table is static. It is updated once a week at 1.00 am. The table is then used for reporting purposes.
Thank you for your comments. I tried running profiler but I do not understand how to interpret what is being returned or what parameters I should set in order to identity issues… I will look into this further however… Thank you.

quote:Originally posted by derrickleggett If your processor and memory are fine, you might be having trouble with locks. Run a profiler to look at locks and deadlocks. Also look at the query and see if you can use WITH(NOLOCK). This can help if you can deal with the fact that it reads through locks which means at the time you run the query, it could have data that will have changed during the runtime of the query. Derrick Leggett

When the query is running from ASP, run profilr to etch its execution plan and compare with the execution plan generated when the query is run from ISQLW. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

How big is the recordset? Also, have you tried running it from Query Analyzer on the machine the ASP page is being returned to? If it’s possible try to do this on the web server and the client server. Look at the client and server statistics when you run the query and see where your wait times are. Let us know. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>