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 |
|
kotsas
Yak Posting Veteran
65 Posts |
 Posted - 2002-06-04 : 09:07:34
|
| I have problem with latching. Can you tell me is there some tool for resolving this problem or how can I resolve this problem??? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-04 : 09:22:28
|
what exactly is the problem? too long of an avg. wait time? what is your latch waits/sec at peak?quote:
When you have a high number of latch waits per second or a long average latch wait time, the system is generally experiencing a low cach hit ratio and is being forced to perform physical I/O. This will ripple into an I/O bottleneck. The optimum way to alleviate this condition is to increase the physical memory on the system. If you cannot increase memory any further, you will need to increase the I/O bandwidth of your system.-SQL Server 7.0 Performance Tuning Technical Reference
<O> |
 |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-06-04 : 11:53:53
|
| "Latches are very lightweight, short-term synchronization objects protecting actions that need not be locked for the life of a transaction. They are primarily used to protect a row while it is being read for a connection.When the relational engine is processing a query, each time a row is needed from a base table or index, the relational engine uses the OLE DB API to request that the storage engine return the row. While the storage engine is actively transferring the row to the relational engine, the storage engine must ensure that no other task modifies either the contents of the row or certain page structures such as the page offset table entry locating the row being read. The storage engine does this by acquiring a latch, transferring the row in memory to the relational engine, and then releasing the latch.SQL Server Performance Monitor has a Latches object that indicates how many times latches could not be granted immediately and the amount of time threads spent waiting for latches to be granted."http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8_ar_sa_38.htmIf you have many users frequently accessing the same records, this van cause the bottleneck you are seeing. You may consider using the WITH (NOLOCK) option on Selects statements to these tables. This should allow a read without insisting that no update can take place during the read (make sure this is acceptable, and that the intended select is for read only purposes...)ie. SELECT au_lname FROM authors WITH (NOLOCK)You may also want to check transactions if you are using them. A begin tran block should contain as few statements as possible to avoid blocking. Try to keep SELECT statements out of transaction blocks if possible.Hope this helps. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-04 : 12:47:38
|
| Kevin is spot on with his suggestions.A word of caution with NOLOCK. Be sure you fully understand what it means. It is fundamentally a way to put aside the rules of a relational database for the purpose convenience. And actually, its is usually the systems that resort to NOLOCK that are the ones that need the transactional integrity the most. I am very leary of NOLOCK and would only recommend it is an absolute last resort. I think the hardware recommendations made would be much more cost effective in the end.<O> |
|
|
|
|
|
|
|
|
Latching problem