Re: SQL Server 2000 and Latching problem

Hi andrew.

This has certainly given us food for thought here! :)

I think you've more or less posted what we were suspecting - inefficient
queries.
The snag here is that we are dealing with legacy systems. We have a lot
of tables that tend to have a lot of records in them (1000's of
entries, in some cases 10000's).

In one case - we have a fairly important table that has something in the
region of 100 fields, of which there are 45 fields indexed. Clustered
indexing is fine, but which field to chose as the clustered index? The
trouble we have is - the answer depends on which aspect of the table you
are accessing. The access to the information needs to be near instant.
Since we provide screens in our GUI to search the table using different
fields, these fields need to have indexes on them in order to get the
results back quickly enough.

Basically what we have done is taken the ISAM system, and "ported" it
more or less directly into SQL. The middleware (where is where we are
getting the latches) takes the ISAM instructions and converts them into
SQL instructions - meaning SQL becomes a glorified storage layer.

While the way we indexed our tables and the way we wrote the queries etc
- works really well in the ISAM environment, I think this could be why
we are having these difficulties with SQL - it works - just not
efficiently - and occasionally things stop dead for 300sec while this
latch condition occurs. Since all traffic has to go thru the
middleware ISAM translator, this means any transactions which are queued
behind the "latched instruction" have to wait making it look as thought
the applications have locked/crashed (the middleware is not
multithreaded).

Owing to the legacy areas, I am concerned that there probably ist much
room for manoeuvre here. Id like to break up some of the tables, or at
least have a multithreaded system so even if a latch DOES occur, its
just that connection/instruction that is delayed - the rest of the
userbase "doesn't know" about it .. But this is a complete re-write of
the system which is in the pipeline anyway - but I was trying to see if
I can eke out a little more performance out of this until a more
"modern" approach to our database access becomes possible.


So bottom line is: "chances are you are getting latches because you are
performing scruffy queries - check these out, reduce the workload of any
given query where possible"

In which case, this sounds like it means:

"don't do a query that says 'select a,b,c,d,e,f,g where b=1 c=2 d=3..."
and then feed the complete results into some sort of processing engine -
better off doing select a,b,c,e,f,g, where b=1" and then performing a
query on the results the query .. And a query on that query"

- so chaining them together "on-demand" rather than doing a
1-hit-wonder?

Thanks for your responses so far andrew - they've been really useful!
-s



-----Original Message-----
From: Andrew J. Kelly [mailto:sqlmvpnooospam@xxxxxxxxxxxx]
Posted At: Sep06 2005 1426
Posted To: connect
Conversation: SQL Server 2000 and Latching problem
Subject: 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: 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: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.server)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.programming)
  • Re: Ongoing purging of active records causes deadlocks
    ... For a query like: ... plan for some of our queries and it looks like it sorts first, ... What is a possible alternative, though, is that the purge first performs: ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Looking for a professional SQL programmer for a small job
    ... Pro SQL Server 2000 Database Design - ... I have two queries to build and while I know my way around SQL, ... >> One is a seach query that pull rental properties from a database based on ...
    (microsoft.public.sqlserver.programming)