Performance Monitor, or Perfmon, measures performance statistics on a regular interval, and saves those stats in a file.
To set it up for SQL Server, do the below steps:
- Open perfmon from the run
- Expand Data Collector Sets
- Right click User Defined
- Click New -> Data Collector Set
- Give it a name
- Click Create manually
- Click Next -> Create data logs -> Performance counter -> Next
- Click Add in the next screen
- Add Available Bytes, Available MBytes, Page faults per second, Pages per second under Memory
- Add % disk time, Average disk second per read, Average disk second per write, Current disk queue length, disk bytes per second, disk transfer per second under Physical Disk
- % privileged Time, % processor time under Processor
- Add Freespace scans per second, Fullscan/sec under SQL Server access methods
- Add buffer cache hit ratio, checkpoint pages/sec, free pages, lazy writes/sec, page life expectancy under SQL Server buffer manager
- Add user conections under SQL Server general statistics
- Add Total latch wait time under SQL Server latches
- Add Lock timeouts/sec, Lock wait time, number of deadlocks/sec under SQL Server locks
- Under SQL Server memory manager, add memory grants pending, targe server memory and total server memory
- Under SQL Server SQL statistics, add batch requests/sec, SQL compilations/sec, SQL re-compilations/sec
- Under System, add context switches/sec, processor queue length
- Click OK -> Next -> Next -> Finish
- Right click this new collector set under User defined and choose start
When one needs to see the report, stop this collector and open the collector file and view as report.
Originally published at xameeramir.github.io.