Application Timeouts
-
Hello everyone,
My company has decided to write our system from DOS to a web based system. The whole system hasn't been released yet, just certain modules as they are re-written. I have been seeing more and more timeouts from users when using the new application. The COM objects are set to timeout after 30 seconds which should be plenty of time. The SQL Server is set to not timeout on a query.
Sometimes I am seeing application timeouts on a call to a single record update, in the stored procedure, I am using with (ROWLOCK) for my locking hint. I am also seeing application timeouts from issuing a simple select based on an ID field (clustered) , there are only 58 records in this table. These are not the only times I am getting application timeouts, but these are the most confusing to me.
Can anyone shed any light on how I could combat these application timeouts?
Thanks.
-
Is there any particular reason you are using the ROWLOCK hint?
If not remove it and let the server handle the locking.
From Books Online,
quote:
Note The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Disallowing a locking level can affect concurrency adversely.Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Here the Solution of Hang:
When you want a report which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!
the best thing is to run the query on the server (by making View on SQl Server) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) 🙂 .
you will write something like this in your View:
select Invoices.*, Situation.*
from Situation with (nolock) INNER JOIN Invoices with (nolock)
ON Situation.SituationID = Invoices.Situation
where Situation.SituationID =1
Disadv:
- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.
Adv:
- no hang at all
- very fast Response
- Little summary for Locks in SQL Log file.
also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.
ALSO:
try to put the DATA and the LOG file of SQL Database on a partition that is Formatted with 32 K or 64 K Cluster Size (not the defalut = 4 K)
because the default Page size in SQL in memory is 8K ,
And If u use the defulat Cluster Size while formating partioin, then to get 1 page (8K), you need 2 hits for the HardDisk (4 K each)
But If u use 32 K Cluster Size while formatiing partioin, then on 1 hit for the HardDisk (32 K each) , you get 4 pages into Memory (more faster ... because the slowest thing that slow the whole system is to read/write from Hard Disk)
I use 32 K not 64 K.. because with 64 K you may have a lot of empty pages in memory ( lost in memeory and your DB may be graw more)
this of course will help you to solve the problem of hang more and more .. bye
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com -
quote:
Is there any particular reason you are using the ROWLOCK hint?The reason I added the ROWLOCK hint is because I don't want SQL Server to issue a page lock. The application is only updating a single record at any time, so a rowlock should be sufficient.
Any idea about the simple select statement on the table with 58 records?
-
quote:
Disadv:- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.
Adv:
- no hang at all
- very fast Response
- Little summary for Locks in SQL Log file.
I was toying with the idea of letting some stored procedures read dirty data. Of course, this would only be the modules that are not “Mission Critical”.
The next step in this is to determine which modules I can do this for, then I will have to sell my boss on the idea of viewing dirty data.
-
Trust me, jarretg, you should get rid of rowlock, bet your problems will go away.
However, I DO use nolock whenever the consequences (not the risk) of a dirty read is low.
nolock is always much less dangerous than rowlock, though.
Signature is NULL
-
quote:
Trust me, jarretg, you should get rid of rowlock, bet your problems will go away.I don't see how the problem will go away, I always want it to do a rowlock since it is only updating one row. Isn't ROWLOCK the most granular lock SQL Server can issue? I used to have the update statement without any locking hints and it would sometimes use page locks. I don't need page locks, therefore I override it to use ROWLOCK.
quote:
However, I DO use nolock whenever the consequences (not the risk) of a dirty read is low.nolock is always much less dangerous than rowlock, though.
This is what I am going to have to present to my boss, the pro's and con's of allowing dirty reads.
Do anyone have any more suggestions on how to handle application timeouts?
-
You can also take a look at you COM components - if you have for instance a server-side recordset and for each record in it openning another recordset - they might colide and then you will have these timeouts for even 2 or 3 rows of data. In such a case make the first recordset client side and disconnected.
Stanislav Petkov
Stanislav Petkov
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply