Look at hardware resources in this order
1 MemoryThis 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.
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.
You can use Performance Monitor to provide top level information of how Windows sees drives performing.
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.
Differences between Read & Write figures could lead you to investigate a number of items e.g. RAID type, Drive Controller Cache, Sector Alignment.
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