Monday, January 24, 2011

SQL Server - Performance Monitor Quick Check

These are the first basic steps to take when looking at a sql server instance.
Look at hardware resources in this order

1 Memory 
2 Storage 
3 CPU

This is because low memory can demand storage to temporarily store committed memory.
If memory demand causes Windows to page this will increase CPU demand.
Basically one issue can be a symptom of another.

Memory

Look at these 4 Performance Counters.

Look at these counters -

Memory/Available MBytes - Ensure there is adequate memory for Windows, leave 200MB free.
Remember to prevent SQL from consuming too much memory by setting the maximum memory.

SQLServer:Memory Manager/Target Server Memory (KB)
This is how much memory SQL wants

SQLServer:Memory Manager/Total Server Memory (KB)
This is how much memory SQL has.
If this is less than the value it wants then more memory is needed to be allocated or installed.

SQLServer:Buffer Manager:Page Life Expectancy
300 seconds (5 minutes) for an OLTP system.
90 seconds (1 & 1/2 minutes) from a data warehouse.

SQLServer:Buffer Cache Hit Ratio is no considered a useful measure as it can still be high when other counters indicate problems.


Storage

You can use Performance Monitor to provide top level information of how Windows sees drives performing.

Examine using Perfmon for Windows view of Storage performance
LogicalDisk:Avg.Disk sec/Transfer
This is the time in seconds to transfer data to disk
Disk transfers should be < 20ms (0.020 seconds) for volumes hosting sql data files

This metric can be further analysed by breaking into reads & writes.
LogicalDisk:Avg.Disc sec/Read
LogicalDisk:Avg.Disc sec/Write

Differences between Read & Write figures could lead you to investigate a number of items e.g. RAID type, Drive Controller Cache, Sector Alignment.

CPU

Performance Monitor (Perfmon) counters to determine processor use are -

Processor: % Processor Time 
Use this as an idea of how busy the server is relative to capacity.

Processor: % Privileged Time 
Time spent managing server resources (not running applications)
Microsoft say over 30% is bad.

Processor: % User Time 
Time spent running applications

Process: %Processor Time : sqlservr 
This counter enables you to determine SQL server cpu usage.
Use it to prove that high CPU usage is due to sql (or not).

How they relate -
Processor Time = Privileged Time + User Time

No comments: