Examine aspects in this order (Memory > Storage > CPU) as one issue can be a symptom of another.
Examine using Performance Monitor (Perfmon). Look at these counters -
How much memory is available for Windows?
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 this counter has become meaningless now.
Examine these Windows counters using Perfmon for the Windows view of Storage performance
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).
For a SQL view of Storage performance, some TSQL to help out ...
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 DESC
For 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 - contig.exe
- Is the RAID configuration appropriate for volume of transactions.
Performance Monitor (Perfmon) counters to determine processor use are -
Processor:% Processor TimeHow busy is the processor?
Should be < 70%
Processor:% Interrupt TimePercentage of time spent servicing hardware interrupt requests.
Should be < 20%
Processor:Processor Queue LengthHow many tasks are waiting for processor time?
Should be < 4 per CPU
For Processor counters, it may be desirable to monitor separate instances (different cores) in addition to monitoring the _Total instance. The _Total instance provides average readings and therefore disguises individual overworked or under-utilized processors / cores.
The processor counters are for everything installed on the system, not just SQL Server.