Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kenny Blankenship
Starting Member
25 Posts |
 Posted - 2004-10-15 : 16:24:05
|
| I posted this question a week ago or more, but I am still having problems with resource locking terminating a sproc. Originally I posted some of the sprocs that I am using, but they are rather lengthy and somewhat difficult to explain, although they are pretty simple. After much experimentation, I am baffled how to improve SQL Server's performance.For those that are interested, let me provide some background information. The server is a Dell 3.4ghz, dual P4s, 2g memory with hardware RAID 0 on two 40g SCSI harddrives.What I need it to do is execute sprocs for a MES system used at a distributor. After talking to collegues and searching the internet I have not found any useful suggestion to improve the system performance.I have three sprocs that execute randomly, but succesively. In otherwords, as soon as one sproc completes, it is called again. Each sproc executes independent of the other. When the system is not loaded, each sproc's execution time is around 350 ms. When the system is fully loaded, 95% of the time, the execution speed remains the same. Task Manager shows the processors running between 40 - 60%. Occasionaly I will get execution times of 1 sec or more, as viewed using a trace. The execution speed is not so much of a problem, but resource locking and the subsequent sproc termination is. When SQL Server terminates a sproc for a resource lock, it causes other spocs to fail too. I added some error handling to capture these failures, but it appears that the sproc terminates before the error handling can kick in.So my question comes down to this, what SQL Server settings should I adjust, diagnostic tools should I use or hardware could I purchase that might provide some relief? I know that sounds pretty vague, but I don't think this problem has a simple, easy answer. I am looking for suggestions that I might try, alternative perspectives and the thoughts of others who have experienced similar problems?Sean |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-15 : 17:25:28
|
| SQL Server terminates deadlocks in the way you mentioned. When you say you have a "resource locking" problem, do you really mean that you have deadlocks occuring?here is some info on how to reduce deadlocks http://www.sql-server-performance.com/deadlocks.asp-ec |
 |
|
|
Kenny Blankenship
Starting Member
25 Posts |
Posted - 2004-10-15 : 17:35:34
|
| You are right. I meant deadlock. A little brain dead at the moment, it's been a long week.Thank you for the link. I had read that article and modified all my SELECT statements to use WITH (NOLOCK). That reduced the number of occurances, but did not eliminate it.At the moment, I am considering adding an index to the primary table that the SELECT statements use, but I have to notify others since I am not the only one who uses the table. This should improve the performance of my sprocs and reduce the amount of locking occuring. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-15 : 17:40:59
|
| there are no sql server settings that you can change that will do anything to help you.Not to be too tough on you but you have one of two problems:1. Poorly designed database layout (not properly normalized for example, missing or improper indexing etc.)2. Poorly written code.Chances are it is a combination of the two. Read through that link I posted above and follow their suggestions.-ec |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-10-15 : 17:57:27
|
| FROM BOLAdjusting Transaction Isolation LevelsThe isolation property is one of the four ACID properties a logical unit of work must display to qualify as a transaction. It is the ability to shield transactions from the effects of updates performed by other concurrent transactions. The level of isolation is actually customizable for each transaction.Microsoft® SQL Server™ supports the transaction isolation levels defined in SQL-92. Setting transaction isolation levels allows programmers to trade off increased risk of certain integrity problems with support for greater concurrent access to data. Each isolation level offers more isolation than the previous level, but does so by holding more restrictive locks for longer periods. The transaction isolation levels are: READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLE Transaction isolation levels can be set using Transact-SQL or through a database API:Transact-SQLTransact-SQL scripts and DB-Library applications use the SET TRANSACTION ISOLATION LEVEL statement.ADOADO applications set the IsolationLevel property of the Connection object to adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, or adXactReadSerializable.OLE DBOLE DB applications call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, or ISOLATIONLEVEL_SERIALIZABLEODBCODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE.See AlsoIsolation Levels in SQLOLEDBSET TRANSACTION ISOLATION LEVEL------------------I have read that though setting the isolation level to serializable can solve deadlock problem it may slow down updates*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
|
|
|
|
|
Resource Locking