Memory > Storage > CPU
This is because one issue can be a symptom of another.
Memory
Examine using Performance Monitor (Perfmon)
Look at these counters -
Memory/Available MBytes - Memory for Window, leave sufficient!
Else stop SQL consuming too much by setting maximum memory (recommended tip).
SQLServer:Memory Manager/Target Server Memory (KB)
How much memory SQL wants
SQLServer:Memory Manager/Total Server Memory (KB)
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.
The old method was SQLServer:Buffer cache hit ratio (ideal of 99%) but data volumes mean the counter is meaningless now.
Storage
Examine using Perfmon for Windows view of Storage performance
LogicalDisk:Avg.Disk sec/Transfer
Should be < 20ms (0.020 seconds) for volumes hosting sql data files
For further problems look for differences between
LogicalDisk:Avg.Disk sec/Read & LogicalDisk:Avg.Disk sec/Write
Could show issues with controller or RAID (e.g. slow write on RAID5).
SQL view of Storage performance
On SQL 2000 looking at I/O statistics is achieved using fn_virtualfilestats
SELECT d.name as DBName ,RTRIM(b.name) AS LogicalFileName ,a.NumberReads ,a.NumberWrites ,a.BytesRead ,a.BytesWritten ,a.IOStallReadMS ,a.IOStallWriteMS ,CASE WHEN (a.NumberReads = 0) THEN 0 ELSE a.IOStallReadMS / a.NumberReads END AS AvgReadTransfersMS ,CASE WHEN (a.NumberWrites = 0) THEN 0 ELSE a.IOStallWriteMS / a.NumberWrites END AS AvgWriteTransfersMS FROM ::fn_virtualfilestats(-1,-1) a INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid INNER JOIN sysdatabases d ON d.dbid = b.dbid ORDER BY a.NumberWrites DESCFor SQL 2005+, sys.dm_io_virtual_file_stats is a available. This is a dynamic management function that shows how SQL is using the data files.
SELECT DB_NAME(filestats.database_id) AS DBName ,files.name AS LogicalFileName ,num_of_reads AS NumberReads ,num_of_writes AS NumberWrites ,num_of_bytes_read AS BytesRead ,num_of_bytes_written AS BytesWritten ,io_stall_read_ms AS IOStallReadMS ,io_stall_write_ms AS IOStallWriteMS ,CASE WHEN (num_of_reads = 0) THEN 0 ELSE io_stall_read_ms / num_of_reads END AS AvgReadTransfersMS ,CASE WHEN (num_of_writes = 0) THEN 0 ELSE io_stall_write_ms / num_of_writes END AS AvgWriteTransfersMS FROM sys.dm_io_virtual_file_stats(-1,-1) filestats INNER JOIN sys.master_files files ON filestats.file_id = files.file_id AND filestats.database_id = files.database_id ORDER BY num_of_writes DESC
Further info on here c/o David Pless
If Disk Performance is an issue, consider these aspects
- Auditing disk configuration
- Physical file fragmentation
- Is the RAID configuration appropriate for volume of transactions.
CPU
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
