Connection/Command Timeout
-
Hi,
I have a number of applications and the webserver accessing a database. Suddenly I have been experiencing a lot of timeouts.
The database is not very large. Most of the tables are less than 500 rows , with the exception of one table which has a couple of 1000's of rows.
I experience this timeout from all the applications and clients. Is this a network problem or a database problem. Also noticed that the memory usage on the database server is very high 50%. The only thing this server is running is SQL Server 2000.
-
Hi binduram, I have the same problem only with one table of the database, I think that it is a database problem, you have to check your commandtimeouts.
Ana
Edited by - anamoji on 10/24/2001 12:30:56 PM
Ana -
You may have locking issues. You can find out, if you are not running this on your production system, by starting Profiler and then running some of the SQL statements that are timing out.
quote:
Hi,I have a number of applications and the webserver accessing a database. Suddenly I have been experiencing a lot of timeouts.
The database is not very large. Most of the tables are less than 500 rows , with the exception of one table which has a couple of 1000's of rows.
I experience this timeout from all the applications and clients. Is this a network problem or a database problem. Also noticed that the memory usage on the database server is very high 50%. The only thing this server is running is SQL Server 2000.
Patrick Birch
Quand on parle du loup, on en voit la queue
-
But will lock problems effect the connection timeouts
-
Hi Binduram, did you resolve the problem??
Have a nice day 😀
quote:
But will lock problems effect the connection timeoutsAna
Ana -
No , but I do see a high memory usage. I wonder if this is what is causing the problem
-
I really don´t know but I see it high too.
quote:
No , but I do see a high memory usage. I wonder if this is what is causing the problemAna
Ana -
High memory usage shouldn't be a factor, unless it is so high that you're hitting your paging file. Using ADO, there are two different timeouts - one for the connection and one for the command. Usually once you're connected you'll be dealing with command timeouts -- running a query that takes longer than whatever timeout you have set.
Here are some steps to take:
1) Rebuild ALL your indexes. The easiest way to do this is use the DB Maint Plan Wizard, even if you just use it once.
2) Find a query that fails with the timeout. Drop in Query Analyzer and take a look at the query plan. Run the query - how long does it take? Longer than the timeout in your app? If so, adjust your timeout (and consider modifying your index plan). If it returns in a second or two, the issue is probably blocking.
Blocking can cause a timeout easily - someone else is holding a lock on a row you need to satisfy your query, the query will "hang" until either it can obtain the required locks or it times out. You can finesse this with locking hints, but that is the wrong place to start usually.
3) Start running Perfmon and monitor cpu %, physical disk %, physical disk queue, etc. It could be that the box is just overloaded at certain times and cannot keep up. If that is the case, then you have to figure out what to change!
4) Also, consider what has changed recently? New tables? New app running? Some new job?
This is what DBA's do. It's slow, methodical work. Change one that a time, see what happens.
Andy
-
Andy, the first step I don´t think I can do it because I don´t have that freedom in the server. I am looking the second step, I already identify the query that fails, I run it in the QA and got the query plan, it says:
Query cost: 100%, clustered indexes 22%, index scan 78%, what do I look for??.
How long does it takes to run the DB Maint Plan? do you have an estimate.
Thankds 😀
Ana
Ana -
Hi, Andy do you think that encreasing the LOCK_TIMEOUT in my sp can help?
Thanks 🙂
Ana
Ana -
Time for the index rebuild depends on amount of data and number of indexes. It's fast, all things considered. If you don't have access, can I assume you're not the DBA then? Have you asked your DBA for assistance? I would be greatly surprised if they are not rebuilding the indexes on some scheduled basis.
The query plan is a start. The index scan is ok, not great. May be because your data distribution is poor. In QA, turn on the server trace, lets see how much IO is involved - could post that? It would also be helpful if you could post DDL to create the table(s) involved, plus the code to the sp, plus info about what columns you have indexed, how many rows in each table. Could you post a screen shot of the query plan as well?
Andy
-
How can I do this "In QA, turn on the server trace, lets see how much IO is involved - could post that? It would also be helpful if you could post DDL to create the table(s) involved, plus the code to the sp, plus info about what columns you have indexed, how many rows in each table. "
How can I send you the screen shot?? I can not put it from here.
Thanks 😀
quote:
Time for the index rebuild depends on amount of data and number of indexes. It's fast, all things considered. If you don't have access, can I assume you're not the DBA then? Have you asked your DBA for assistance? I would be greatly surprised if they are not rebuilding the indexes on some scheduled basis.The query plan is a start. The index scan is ok, not great. May be because your data distribution is poor. In QA, turn on the server trace, lets see how much IO is involved - could post that? It would also be helpful if you could post DDL to create the table(s) involved, plus the code to the sp, plus info about what columns you have indexed, how many rows in each table. Could you post a screen shot of the query plan as well?
Andy
Ana
Ana -
You can do a SET SHOWPLAN_TEXT ON before running the query to get the query plan.
Or SET STATISTICS IO ON
Steve Jones
-
Thanks Steve, 😀
quote:
You can do a SET SHOWPLAN_TEXT ON before running the query to get the query plan.Or SET STATISTICS IO ON
Steve Jones
Ana
Ana -
Hi!, I got the information:
DELETE FROM DEMAND_LINE_ITEM;
StmtText |--Clustered Index Delete(OBJECT:([adw_fpp].[dbo].[DEMAND_LINE_ITEM].[PK__DEMAND_LINE_ITEM__12149A71]))
StmtId 10
NodeId 46
Parent 45
PhysicalOp Clustered Index Delete
LogicalOp Delete
ArgumentOBJECT:([adw_fpp].[dbo].[DEMAND_LINE_ITEM].[PK__DEMAND_LINE_ITEM__12149A71])
DefinedValues NULL
EstimateRows 1.0
EstimateIO 1.0675675E-2
EstimateCPU 0.000001
AvgRowSize 4
TotalSubtreeCost 4.8334975E-2
OutputList NULL
Warnings
Type PLAN_ROW
Parallel 0
EstimateExecutions 1.0
StmtText |--Top(ROWCOUNT est 0)
StmtId 10
NodeId 48
Parent 46
PhysicalOp Top
LogicalOp Top
ArgumentNULL
DefinedValues NULL
EstimateRows 1.0
EstimateIO 0.0
EstimateCPU 0.000001
AvgRowSize 96
TotalSubtreeCost 0.0376582
OutputList [Bmk1000]
Warnings NULL
Type PLAN_ROW
Parallel 0
EstimateExecutions 1.0
StmtText |--Index Scan(OBJECT:([adw_fpp].[dbo].[DEMAND_LINE_ITEM].[XIF931DEMAND_LINE_ITEM]), ORDERED)
StmtId 10
NodeId 49
Parent 48
PhysicalOp Index Scan
LogicalOp Index Scan
ArgumentOBJECT:([adw_fpp].[dbo].[DEMAND_LINE_ITEM].[XIF931DEMAND_LINE_ITEM]),ORDERED
DefinedValues [Bmk1000]
EstimateRows 1.0
EstimateIO 3.7578501E-2
EstimateCPU 7.9600002E-5
AvgRowSize 96
TotalSubtreeCost 3.7658099E-2
OutputList [Bmk1000]
Warnings NULL
Type PLAN_ROW
Parallel 0
EstimateExecutions 1.0
Andy, I know that I´ve been giving you all a lot of work but I don´t have any one else that could help me, I am beginning with all this even thought I study my carrer, is not the same in the field.
Thanks 😀
Ana
Ana
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply