MS SQL Server - Performance metrics

Author

Performance metrics

John T

2005-10-27, 5:17 pm

Hello all,

I am trying to address some troublesome performance metrics. Specifically,
I would like
to know where/how I can further investigate and troubleshoot the following
items:

- the ratio of Buffer Manager: [Total Pages]/[Target Pages].
- the ratio of Memory Manager: [Total Server Memory]/[Target Server Memory].
- Cache Manager->Cache Hit Ratio->_Total
- Cache Manager->Cache Hit Ratio->Execution Contexts
- Cache Manager->Cache Hit Ratio->Misc. Normalized Trees
- Cache Manager->Cache Hit Ratio->Procedure Plans

Below is what I believe to be all the pertinant info I have gathered so far.
Please
let me know if I'm missing anything. The cpu was not included and shows
very minimal
activity. This server is running hyperthreading. There are some other

>very< light weight
services that run on this server, but all combined, they use no more than
35MB of ram combined.

Thank you for any input.
John T

xp_msver
--------
ProductName
Microsoft SQL Server
ProductVersion
8.00.760
Language
English (United States)
Platform
NT INTEL X86
CompanyName
Microsoft Corporation
FileDescription
SQL Server Windows NT
FileVersion
2000.080.0760.00
InternalName
SQLSERVR
PrivateBuild
NULL
SpecialBuild
NULL
WindowsVersion
5.2 (3790)
ProcessorCount
4
ProcessorActiveMask
0000000f
ProcessorType
PROCESSOR_INTEL_PENT
IUM
PhysicalMemory
2047 (2146951168)

sp_configure
---------------------------
affinity mask 0
allow updates 0
awe enabled 0
c2 audit mode 0
cost threshold for parallelism 5
Cross DB Ownership Chaining 0
cursor threshold -1
default full-text language 1033
default language 0
fill factor (%) 0
index create memory (KB) 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 255
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
open objects 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 600
scan for startup procs 1
set working set size 0
show advanced options 1
two digit year cutoff 2049
user connections 0
user options 0

logman
-----------
Object: Memory
Available MBytes 1155.614
Page Faults/sec 176.951

Object: PhysicalDisk
0 C: D: 1 E:
% Disk Time 5.858 2.978
Avg. Disk Queue Length 0.059 0.030
Avg. Disk sec/Transfer 0.011 0.015

Object: SQLServer:Access Methods
Page Splits/sec 0.016

Object: SQLServer:Buffer Manager
Buffer cache hit ratio 99.864
Stolen pages 6594.590
Target pages 205811.570
Total pages 122502.654

Object: SQLServer:Cache Manager
Cache Hit Ratio
_Total 67.204
Adhoc Sql Plans 92.501
Cursors 99.688
Execution Contexts 56.778
Misc. Normalized Trees 61.695
Prepared Sql Plans 99.983
Procedure Plans 73.668
Replication Procedure Plans 0.000
Trigger Plans 99.910

Object: SQLServer:Latches
Average Latch Wait Time (ms) 61.105
Latch Waits/sec 0.514

Object: SQLServer:Memory Manager
Target Server Memory(KB) 1659284.561
Total Server Memory (KB) 992813.230

John T

2005-10-27, 8:23 pm

So...Is this the wrong forum for this? Anybody have any input at all?

Thanks.

"John T" wrote:

> Hello all,
>
> I am trying to address some troublesome performance metrics. Specifically,
> I would like
> to know where/how I can further investigate and troubleshoot the following
> items:
>
> - the ratio of Buffer Manager: [Total Pages]/[Target Pages].
> - the ratio of Memory Manager: [Total Server Memory]/[Target Server Memory].
> - Cache Manager->Cache Hit Ratio->_Total
> - Cache Manager->Cache Hit Ratio->Execution Contexts
> - Cache Manager->Cache Hit Ratio->Misc. Normalized Trees
> - Cache Manager->Cache Hit Ratio->Procedure Plans
>
> Below is what I believe to be all the pertinant info I have gathered so far.
> Please
> let me know if I'm missing anything. The cpu was not included and shows
> very minimal
> activity. This server is running hyperthreading. There are some other
> services that run on this server, but all combined, they use no more than
> 35MB of ram combined.
>
> Thank you for any input.
> John T
>
> xp_msver
> --------
> ProductName
> Microsoft SQL Server
> ProductVersion
> 8.00.760
> Language
> English (United States)
> Platform
> NT INTEL X86
> CompanyName
> Microsoft Corporation
> FileDescription
> SQL Server Windows NT
> FileVersion
> 2000.080.0760.00
> InternalName
> SQLSERVR
> PrivateBuild
> NULL
> SpecialBuild
> NULL
> WindowsVersion
> 5.2 (3790)
> ProcessorCount
> 4
> ProcessorActiveMask
> 0000000f
> ProcessorType
> PROCESSOR_INTEL_PENT
IUM
> PhysicalMemory
> 2047 (2146951168)
>
> sp_configure
> ---------------------------
> affinity mask 0
> allow updates 0
> awe enabled 0
> c2 audit mode 0
> cost threshold for parallelism 5
> Cross DB Ownership Chaining 0
> cursor threshold -1
> default full-text language 1033
> default language 0
> fill factor (%) 0
> index create memory (KB) 0
> lightweight pooling 0
> locks 0
> max degree of parallelism 0
> max server memory (MB) 2147483647
> max text repl size (B) 65536
> max worker threads 255
> media retention 0
> min memory per query (KB) 1024
> min server memory (MB) 0
> nested triggers 1
> network packet size (B) 4096
> open objects 0
> priority boost 0
> query governor cost limit 0
> query wait (s) -1
> recovery interval (min) 0
> remote access 1
> remote login timeout (s) 20
> remote proc trans 0
> remote query timeout (s) 600
> scan for startup procs 1
> set working set size 0
> show advanced options 1
> two digit year cutoff 2049
> user connections 0
> user options 0
>
> logman
> -----------
> Object: Memory
> Available MBytes 1155.614
> Page Faults/sec 176.951
>
> Object: PhysicalDisk
> 0 C: D: 1 E:
> % Disk Time 5.858 2.978
> Avg. Disk Queue Length 0.059 0.030
> Avg. Disk sec/Transfer 0.011 0.015
>
> Object: SQLServer:Access Methods
> Page Splits/sec 0.016
>
> Object: SQLServer:Buffer Manager
> Buffer cache hit ratio 99.864
> Stolen pages 6594.590
> Target pages 205811.570
> Total pages 122502.654
>
> Object: SQLServer:Cache Manager
> Cache Hit Ratio
> _Total 67.204
> Adhoc Sql Plans 92.501
> Cursors 99.688
> Execution Contexts 56.778
> Misc. Normalized Trees 61.695
> Prepared Sql Plans 99.983
> Procedure Plans 73.668
> Replication Procedure Plans 0.000
> Trigger Plans 99.910
>
> Object: SQLServer:Latches
> Average Latch Wait Time (ms) 61.105
> Latch Waits/sec 0.514
>
> Object: SQLServer:Memory Manager
> Target Server Memory(KB) 1659284.561
> Total Server Memory (KB) 992813.230
>

Gert-Jan Strik

2005-10-28, 8:23 pm

John, metrics don't cause performance problems. They are just
statistics.

If you have a performance problem, then please specify what the problem
is that you are trying to solve. On badly tuned systems, performance
problems are often causes by poor database design, inproper indexing
and/or poorly written queries (for example excessive use of cursors).
Your post does not contain enough information.

Gert-Jan

John T wrote:[color=darkred
]
>
> So...Is this the wrong forum for this? Anybody have any input at all?
>
> Thanks.
>
> "John T" wrote:
>

John T

2005-10-28, 8:23 pm

Hello,

I am well aware that measurments are not causally related to performance. I
am under no such delusion. However, you must admit that performance problem
or not, poor cache hit ratio's and the inability of the sqlserver service to
obtain the memory that it wants is indicative of a problem. That said, let
me rephrase my questions.

1) Given poor cache hit ratio's in the Cache Manager subsystem, how would
you investigatge the source of poor hit ratios on Misc. Normalized Trees, and
Execution Contexts? I know how to pursuit Procedure Plans, but included that
data for a larger picture.

2) According to the SQLServer:Memory Manager, my server want's more memory
and is not getting it. Given that my server has plenty of available ram, no
disk contention, no cpu overload, minimal competing resources, and my current
configuration options, how would you identify why the sql server service can
not receive as much memory as it is requesting?

Thanks for your response. Please, can anybody provide further input?

John

"Gert-Jan Strik" wrote:
[color=darkred]
> John, metrics don't cause performance problems. They are just
> statistics.
>
> If you have a performance problem, then please specify what the problem
> is that you are trying to solve. On badly tuned systems, performance
> problems are often causes by poor database design, inproper indexing
> and/or poorly written queries (for example excessive use of cursors).
> Your post does not contain enough information.
>
> Gert-Jan
>
>
> John T wrote:
>

Gert-Jan Strik

2005-10-28, 8:23 pm

I don't know if I am the right person to answer your questions, but I
will give it a try. See inline.

John T wrote:
>
> Hello,
>
>
> I am well aware that measurments are not causally related to performance. I
> am under no such delusion. However, you must admit that performance problem
> or not, poor cache hit ratio's and the inability of the sqlserver service to
> obtain the memory that it wants is indicative of a problem. That said, let
> me rephrase my questions.
>
> 1) Given poor cache hit ratio's in the Cache Manager subsystem, how would
> you investigatge the source of poor hit ratios on Misc. Normalized Trees, and
> Execution Contexts? I know how to pursuit Procedure Plans, but included that
> data for a larger picture.

For me, in itself, low ratios are not a problem to be fixed. I have had
a situation where there was unusually high CPU usage, to the point where
it hurt performance of heavy (CPU intensive) queries. At that time, a
low cache hit ratio was a reason for me to investigate the
compilations/recompilations. It turned out that one heavily used stored
procedure accessed a non-existing table, which caused the stored
procedure to be compiled each time it was called.

Answer to your question: I have no idea, but I would definitely check
out BOL, for example the topic "Execution Plan Caching and Reuse"

By the way: what is your CPU usage?

> 2) According to the SQLServer:Memory Manager, my server want's more memory
> and is not getting it. Given that my server has plenty of available ram, no
> disk contention, no cpu overload, minimal competing resources, and my current
> configuration options, how would you identify why the sql server service can
> not receive as much memory as it is requesting?

I think your analysis is correct. The numbers also seem to indicate that
there is memory pressure, because I think the number of pagefaults per
second is relatively high.

You could test your theory about not getting the requested memory by
selecting from a large table, for example with a statement like this:
SELECT COUNT(*) FROM LargeTable (index=0)
During its execution, the available memory should drop to its minimum.

If the available memory does not drop, then I would investigate the
Windows part. I hope you are not using a beta or release candidate of
Windows 2003. I would also look at SQL-Server service pack 4, to see if
there were any bugfixes in this area.

If the memory does drop, then I would investigate the
compilations/recompilations further, and any other use than stored
procedures to access to the database.

HTH,
Gert-Jan

[color=darkred]
> Thanks for your response. Please, can anybody provide further input?
>
> John
>
> "Gert-Jan Strik" wrote:
>

Sponsored Links