SQL Server Quick Check - Notes

Checking a SQL Server over? Some notes on how to approach this.
Examine aspects in this order (Memory > Storage > CPU) as one issue can be a symptom of another.

Memory

Examine using Performance Monitor (Perfmon). Look at these counters -

Memory:Available MBytes
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.


Storage

Examine these Windows counters using Perfmon for the 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).


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.

CPU

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

Processor:% Processor Time

How busy is the processor?
Should be < 70%

Processor:% Interrupt Time 

Percentage of time spent servicing hardware interrupt requests.
Should be < 20%

Processor:Processor Queue Length

How 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.