Average Latch Wait Time (ms)

SQL Server 2000 EE SP3
Win 2000
6-Way PIII 700Mhz
8GB RAM
I have a question regarding the Average Latch Wait Time (ms) counter :
We have had this server for 3 years running a very critical application
(200+ Transactions per sec)
The business has duplicated in the past year and we are thinking about
getting a new server because this one is slow and we want to increase memory
(Windows 2003 EE)
The average Latch wait time was normally around 385 and some days up to 415.
However this morning when i came to my office the counter was 35. I find the
server a lot faster today but we haven't made any significant changes.
This is the only thing that I can think of:
We have an archive process and in the last couple days we have moved to
archived Database millions of records (We do this every month)
We have a couple of huge tables (This month for example, table 1: 34
millions before archive - went to 24 millions, table 2: 5 millions before
archive - went to 3 millions)
Do you think that deleting those records from those big tables made the
difference?
Do you have any comment on why the average latch wait time dropped
dramatically?
Thanks in advanceNo where near enough data to know.
Can cutting your biggest tables close ot 1/2 have a big impact? Sure.
Will dropping the latch waits as decribed potentially have a big impact.
Sure.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Seal" <sealtielp@.afex.com> wrote in message
news:%23dMAKm2EEHA.3344@.tk2msftngp13.phx.gbl...
> SQL Server 2000 EE SP3
> Win 2000
> 6-Way PIII 700Mhz
> 8GB RAM
>
>
> I have a question regarding the Average Latch Wait Time (ms) counter :
> We have had this server for 3 years running a very critical application
> (200+ Transactions per sec)
>
> The business has duplicated in the past year and we are thinking about
> getting a new server because this one is slow and we want to increase
memory
> (Windows 2003 EE)
>
> The average Latch wait time was normally around 385 and some days up to
415.
> However this morning when i came to my office the counter was 35. I find
the
> server a lot faster today but we haven't made any significant changes.
>
> This is the only thing that I can think of:
> We have an archive process and in the last couple days we have moved to
> archived Database millions of records (We do this every month)
> We have a couple of huge tables (This month for example, table 1: 34
> millions before archive - went to 24 millions, table 2: 5 millions before
> archive - went to 3 millions)
>
>
> Do you think that deleting those records from those big tables made the
> difference?
> Do you have any comment on why the average latch wait time dropped
> dramatically?
>
>
> Thanks in advance
>|||Hello Seal,
I believe Brian has pointed it out that the operation on the data archive pr
ocess can have impact on
dropping the average Latch Wait Time (Performance Counter).
Here I follow up with additional information as below:
1) Average latch wait time represents the time for latch requests that had t
o wait.
As you'll see later, pages of an index use a slightly different locking mech
anism than regular data pages. A
lightweight lock - Latch lock is used to lock upper levels of indexes.
2) The SQL Server lock manager provides two separate locking systems.
The first system affects all fully shared data and provides row locks, page
locks, and table locks for tables,
data pages, text pages, and leaf-level index pages. The second system is use
d internally for index
concurrency control, controlling access to internal data structures, and ret
rieving individual rows of data
pages. This second system uses latches, which are less resource intensive th
an locks and provide
performance optimization.
3) Latches ensure the physical consistency and locks ensure the logical cons
istency of the data.
Latching happens when you place a row physically on a page or move data in o
ther ways, such as
compressing the space on a page. SQL Server must guarantee that this data mo
vement can happen
without interference.
Above all, we can see that if we lessen the operations to place a row physic
ally on a page, or move data in
other ways, the time for request a latch/latches are also shortened. I belie
ve the latest archive process have
made this big impact on the performance.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.