August 22, 2008 at 3:07 am
We have a setup whereby some server software is constantly feeding a database transactions with X number of threads. In the application error log, quite frequently we get SQL Server does not exist or access denied, or a timeout exception. The 2 machines are sat next to eachother with a high speed direct connection.
When the number of threads in the server exceeds 130, these timeouts occur. I'm trying to find the root cause of the issue. Basically, for each thread the following happens:
Establish comms
Create temp table
Bulk import into 3 tables
Run stored procedures to update certain columns
The entire process normally lasts around 2 seconds. There can be a maximum of 150 threads hitting the database from the application.
I have a feeling that if the transactions are taking around 2 seconds then there must be some X locks held while the inserting/updating is going on. If there are 130 connections then maybe im getting timeouts from some connections.
I want to ascertain whether this is indeed the problem, or whether some hardware is letting the side down or another issue. The problem will only get worse as the number of connections is set to double.
Can anyone give me advice? If you need further info please let me know!
August 22, 2008 at 3:31 am
You are facing a capacity planning issue.
Just in case, what's your timeout setup for the offending environment?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 22, 2008 at 3:38 am
Hi Paul,
The server software is set to timeout after 30 seconds.
Ive noticed that when there is a high number of connections, the perfmon counter Lock timeouts/sec sometimes shoots up to over 100. Is this something to be worried about?
Sometimes the server will log a timeout error, but just 15 minutes ago the Lock timeouts/sec shot up to 50 but there were no error messages in the log file. Would SQL Server return an error to the server during this time?
Thanks,
August 22, 2008 at 5:45 am
Have you checked the state of the indexes and stats on the tables that are being inserted to? What is the fill factor?
If you have high levels of fragmentation or out of date statistics, your transactions will take longer, thus hold locks for longer and you'll see an increase in your lock counters.
Also is there any optimization you can do on the code?
August 22, 2008 at 5:50 am
Hi Clive,
We recently rebuilt every index. The main table used has the following for showcontig:
DBCC SHOWCONTIG scanning 'Snapshots' table...
Table: 'Snapshots' (2055678371); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 91560
- Extents Scanned..............................: 11456
- Extent Switches..............................: 11666
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 98.10% [11445:11667]
- Logical Scan Fragmentation ..................: 0.95%
- Extent Scan Fragmentation ...................: 5.28%
- Avg. Bytes Free per Page.....................: 894.7
- Avg. Page Density (full).....................: 88.95%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I think the code could be optimised a little, but 150 threads appears a very low amount to cause the calling application to result timeouts.
Maybe something like service broker would help...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply