Re: SQL Server 2000 and Latching problem

Simon,

Here is a brief definition of what latches are used for by Tom Davidson:
>>>
Latches are short term light weight synchronization objects. Latches are
not held for the duration of a transaction.



?Plain? latches are generally not related to IO. These latches can be used
for a variety of things, but they are not used to synchronize access to
buffer pages (PAGELATCH_x is used for that).



Possibly the most common case is contention on internal caches (not the
buffer pool pages), especially when using heaps and/or text.
<<<


Do you have a clustered index on each table? If not you might want to think
about adding one. In general every table should have a clustered index. How
large are these tables? You say they are simple queries but that does not
mean you have the right indexes. Have you looked at the query plans or
profile traces to see which ones might be scanning vs. Seeks? A "Bad" query
is one that does more work than it needs to. Usually scans instead of seeks
either due to lack of proper indexes or a poorly designed WHERE clause.
Running a trace to see which queries have excessive reads will most likely
go a long way towards finding the culprits.


--
Andrew J. Kelly SQL MVP


"Simon Goss" <s.goss@xxxxxxxxxxxx> wrote in message
news:xqkEsqrsFHA.8272@xxxxxxxxxxxxxxxxxxxxxxxxxxx
> hi andrew.
>
> Well heres the odd thing.
> I would have thought if im going to get a latch at all - it would be on
> writing - since that implies updating the data, and then SQL server
> updating the indicies, etc - in theory computationally intensive stuff -
> but I think im getting it on reads most of the time - supposedly passive
> stuff! But yes youre right - its "general" traffic - read, insert,
> update, deletions are in the minority.
>
> Youre right its limiting with single thread (!) - the client is being
> rewritten to take modern databases into account like SQL and oracle, and
> this may even mean that the messing about with the database handler
> (that im getting the latches with) might even be killed off completely
> as the clients could talk to the database directly. This is going to be
> multithread - but this is expected to be 12-18months away (at current
> progress).
>
> To answer the rest of your points:
>
> Memory: latches have occurred on servers with anything between 512M and
> 2GB RAM, on RAID5 F/Wide SCSI HD arrays. Doesn't appear to change
> anything if the SQL server is dedicated or not.
>
> Interesting point about the bad query design. What we have tried to do
> here is to more or less "translate" the ISAM queries into a SQL
> equivalent, but we found we were hitting some major performance issues,
> so for certain operations, since we know that when a particular event
> occurs -> "this is the info we want" - we hardcode the SQL query rather
> than translate the original ISAM request. However, the queries we
> submit to SQL are usually quite simple - "where x matches y in table z
> update field a in table b", or "show me * in table a where [a].[a] =
> [b].[a]"
>
> This may sound like a silly question - but could you give me an example
> of what you mean by bad query (.. And what a "good" query is?)
>
> Thanks for your reply!
> -s
>
>
> -----Original Message-----
> From: Andrew J. Kelly [mailto:sqlmvpnooospam@xxxxxxxxxxxx]
> Posted At: Sep05 2005 1957
> Posted To: connect
> Conversation: SQL Server 2000 and Latching problem
> Subject: Re: SQL Server 2000 and Latching problem
>
>
> Are these mostly read only requests or do they involve Inserts, Updates
> and Deletes? That seems pretty limiting to have only a single thread
> processing these requests. In any case that is no where near enough
> info to determine what the issues are. My guess would be lack of proper
> indexes or poorly formed queries. How much ram on the SQL Server? Oh
> and is there anything else on the same server other than SQL Server?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Simon Goss" <s.goss@xxxxxxxxxxxx> wrote in message
> news:R1BL71isFHA.8272@xxxxxxxxxxxxxxxxxxxxxxxxxxx
>> hi all.
>>
>> got a question - wondered if you guys have come across this.
>>
>> I have an application that acts as a database "server" where it
> captures
>> requests for information from many clients (1-100+ but 30 is typical)
>> where it then interrogates a SQL server database on their behalf, and
>> then returns the results. Its a single-threaded application, so
> various
>> clients requests have to be queued.
>>
>> anyway - this is working ok, except ive noticed that occasionally my
>> single connection to the odbc server seems to freeze for about 300sec
>> before going on a bit more accepting some more connections and then
>> freezes again.
>> Searches on MS technet show this is evidence of latching - and also
>> event log confirms this. advice from microsoft is - keep ldf and mdf
> on
>> separate physical devices as latches are usually caused by i/o unable
> to
>> keep up with the application. so we have done this, but still getting
>> latches when the activity of the "server" (the gateway between the
>> application clients and the sql server) seems to rise. worst case,
> the
>> gateway application can be dealing with a request every 1ms , but i
>> would have thought anywhere between 1-10ms is average - its not flat
> out
>> all the time.
>>
>> so - i suppose the short question is - is there any way i can avoid
>> latching completely? its an application that used to use ISAM database
>> format, but this new gateway "captures" isam-style requests for info
> and
>> converts them to a sql request as this is where the info is stored
> now.
>> it was considered too expensive to rewrite the client software, so we
>> emulate an ISAM situation by realtime "translation" of isam<->SQL
>>
>> any thoughts anyone ? please feel free to email me here or directly -
>> but id really appreciate any pointers!
>> thanks!
>> -s
>>
>>
>> _____________________________________
>>
>> Simon Goss
>> Senior Products Consultant
>> Marval Software Limited
>>
>>
>
>


.

Relevant Pages

  • Re: Using SQl to store aspx pages and memory problems
    ... I've seen classic ASP applications design this way that work ... database isn't some sort of magic pixie dust that solves all problems. ... > the memory increases - the slowness of the request could be dealt with by ... >> takes to get the page from SQL Server. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Ado SQL Concept question.
    ... Once I get on a real database server (does MSDE ... Each user/session may request information about ... For SQL server a query will be best. ... Cursor type can be forward only if you only need readonly but that won't get you ...
    (borland.public.delphi.database.ado)
  • RE: connection to ado.net object
    ... The vb.net code will that submit the request for data to the hp> mainframe by using http and xml. ... This will somehow need to> include a connection object and statements oh how to connect to the SQL> server 2000 database. ... The user name and password are suppose to be passed to> the sql server as part of the connection string. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL Server 2000 and Latching problem
    ... > getting the latches) takes the ISAM instructions and converts them into ... > While the way we indexed our tables and the way we wrote the queries etc ... > query on the results the query .. ... SQL Server 2000 and Latching problem ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server 2000 and Latching problem
    ... While the way we indexed our tables and the way we wrote the queries etc ... "chances are you are getting latches because you are ... query on the results the query .. ... SQL Server 2000 and Latching problem ...
    (microsoft.public.sqlserver.connect)