SQL Server 2012 Performance Monitoring

We should monitor following things.

• Disk Activity
• Physical Disk:
•  % Disk Time
• Should be less than 90%. (If not on RAID)
• Avg. Disk Queue Length
• < 1.5 - 2 times disk spindles.

• Buffer Manager
• Page Reads/Sec
• Page Writes/Sec

• Process Utilization
•  % Processor Time
• Should be < 50%
• Consistance reading upto 80-90 is bad.
• Run each instance on separate processor.
• % Privillage Time
• Indicates time spend on windows Kernnel commands (SQL Server I/O Requests)
• If this and physical disk counters are high, Faster disk subsystem is needed.
• % User Time
• Time spent on user processes.

• Processor Queue Length
• Number of threads waiting for processor time > 2 is bad.

• Memory Usage.
• Available Bytes
• Bytes available for use by processes.
• Pages/Sec
• Hight Value indicates excessive paging.
• Page Faults/Sec

• SQL Server
• Access Methods
• Full Scans/Sec > 1 is bad.
• Buffer manager
• Buffer Cache Hit Ratio > 90.
• Locks
• Average Wait time should be low.

Starting Performance Monitor

To start performance Monitor go to run command and type perfmon.msc, this will open PM.