|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Performance Monitor results skewed???
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Performance Monitor results skewed???
|
|
| Amy Scott 2002-11-04, 11:23 am |
| Hi,
Any insight on this would be greated appreciated! :-)
I am benchmarking performance on two servers and here are the results
for running a read intensive stored procedure 500 times. Both Servers
are SQL2k sp2 on Win2k
******Server A*******
Quad 700 mhz
3.5 gb RAM
3 disk Raid 5 on RA4100
Average elapsed time for 500 executes: 3 minutes 3 seconds
Performance Monitor Counters
- Memory: Pages/Sec 0.111
- Physical Disk: Avg. Disk Queue Length 0.713
- Processor: % Processor Time 12.906
- SQL Server Latches: Avg Latch Wait Time 278.851
- Latch Waits/Sec: Average 2.909, Maximum: 8.970
- Total Latch Wait Time: Maximum 46.989
******Server B*******
Quad 700 mhz
4 gb RAM
3 disk Raid 5 on a SAN
Average elapsed time for 500 executes: 1 minutes 52 seconds
Performance Monitor Counters
- Memory: Pages/Sec 0.000
- Physical Disk: Avg. Disk Queue Length 0.105
- Processor: % Processor Time 16.786
- SQL Server Latches: Avg Latch Wait Time 11304.729
- Latch Waits/Sec: Average 3.879, Maximum: 11.000
- Total Latch Wait Time: Maximum 15.999
I have run this test several times with the results being about the same
each time. Does anyone know why the Average Latch Wait Time is so large
on Server B but the query runs faster and there is no indication of an
i/o bottleneck?
Thank You Thank You Thank You
Amy
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
|
| I don't have an answer, but I think you have a great question. I've been
wondering about that since that counter showed up in SQL 7.0.
If you look in the docs, MS says that is the Avg Latch Wait Time is an
indicator of poor performance and if it is too high, SQL Server is running
low on resources and may need additional resources added. Darn if they
didn't forget to provide any metrics for what is considered a high or low
value and which resources SQL Server could be running out of.
When you look at BOL, it says latches are taken when when pages are moved
from the storage engine to the relational engine. A high Avg Latch Wait Time
would seem to imply that either SQL Server ran out of memory in cache and
was waiting for some memory to free up or I/O was taking extra a long time.
The I/O thing could either be from slow I/O or blocking. Since your disk
queue length is so low, it's probably not I/O bandwidth. So that leaves
blocking.
I'd be curious what the execution plan for your query looked like. Were you
getting intraquery parallelism. If you do have parallelism turned on, I'd be
curious what the perfmn stats looked like if parallelism was turned off. I'd
be interested if the paralellism is causing blocking. The other thing that
is interesting is that on the second server on the SAN, you had one latch
that took 11 seconds and skewed the figures.
"Amy Scott" <anonymous@devdex.com> wrote in message
news:efZYDIChCHA.1688@tkmsftngp09...
> Hi,
>
> Any insight on this would be greated appreciated! :-)
>
> I am benchmarking performance on two servers and here are the results
> for running a read intensive stored procedure 500 times. Both Servers
> are SQL2k sp2 on Win2k
>
> ******Server A*******
> Quad 700 mhz
> 3.5 gb RAM
> 3 disk Raid 5 on RA4100
>
> Average elapsed time for 500 executes: 3 minutes 3 seconds
>
> Performance Monitor Counters
> - Memory: Pages/Sec 0.111
> - Physical Disk: Avg. Disk Queue Length 0.713
> - Processor: % Processor Time 12.906
> - SQL Server Latches: Avg Latch Wait Time 278.851
> - Latch Waits/Sec: Average 2.909, Maximum: 8.970
> - Total Latch Wait Time: Maximum 46.989
>
> ******Server B*******
> Quad 700 mhz
> 4 gb RAM
> 3 disk Raid 5 on a SAN
>
> Average elapsed time for 500 executes: 1 minutes 52 seconds
>
> Performance Monitor Counters
> - Memory: Pages/Sec 0.000
> - Physical Disk: Avg. Disk Queue Length 0.105
> - Processor: % Processor Time 16.786
> - SQL Server Latches: Avg Latch Wait Time 11304.729
> - Latch Waits/Sec: Average 3.879, Maximum: 11.000
> - Total Latch Wait Time: Maximum 15.999
>
> I have run this test several times with the results being about the same
> each time. Does anyone know why the Average Latch Wait Time is so large
> on Server B but the query runs faster and there is no indication of an
> i/o bottleneck?
>
> Thank You Thank You Thank You
>
> Amy
>
>
>
>
>
>
>
>
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
|
|
|
|
|