In general I am interested in these three key resources
Data CollectorA User Defined Data Collector Set can be created in the Performance Monitor by a right-click on
Data Collector Sets \ User Defined
and select to create the data collector set manually.
Performance CountersLogicalDisk \ Avg. Disk Queue Length : ( data; translog; tempdb )
LogicalDisk \ Avg. Disk sec/Read ( data; translog; tempdb )
LogicalDisk \ Avg. Disk sec/Write ( data; translog; tempdb )
Memory \ Available Mbytes
Memory \ Pages/sec
PhysicalDisk \ Avg. Disk Queue Length ( data; translog; tempdb )
PhysicalDisk \ Avg. Disk sec/Read ( data; translog; tempdb )
PhysicalDisk \ Avg. Disk sec/Write ( data; translog; tempdb )
PhysicalDisk \ Disk Reads/sec ( data; translog; tempdb )
PhysicalDisk \ Disk Writes/sec ( data; translog; tempdb )
Process(sqlservr) \ % Processor Time
Process(sqlservr) \ Page Faults/sec
MSSQL : Access Methods \ Forwarded Records/sec
MSSQL : Access Methods \ Full Scans/sec
MSSQL : Access Methods \ Index Searches/sec
MSSQL : Access Methods \ Page Splits/sec
MSSQL : Buffer Manager \ Buffer cache hit ratio
MSSQL : Buffer Manager \ Page life expectancy
MSSQL : Buffer Manager \ Page reads/sec
MSSQL : Buffer Manager \ Page writes/sec
MSSQL : General Statistics \ User Connections
MSSQL : Latches \ Average Latch Wait Time (ms)
MSSQL : Locks(_Total) \ Average Wait Time (ms)
MSSQL : Locks(_Total) \ Lock Timeouts/sec
MSSQL : Locks(_Total) \ Number of Deadlocks/sec
MSSQL : Locks(_Total) \ Locks Waits/sec
MSSQL : Memory Manager \ Total Server Memory (KB)
MSSQL : Memory Manager \ Target Server Memory (KB)
MSSQL : SQL Errors(_Total) \ *
MSSQL : SQL Statistics \ SQL Compilations/sec
MSSQL : SQL Statistics \ SQL Re-Compilations/sec
MSSQL : Wait Statistics(Average wait time (ms)) \ Network IO waits
MSSQL : Wait Statistics(Average wait time (ms)) \ Page IO latch waits
Paging File(_Total) \ % Usage
System \ Processor Queue Length
The sample interval is set to 5 seconds and the data are logged in a binary (.blg) file on a local drive.
AnalysisA quick view on one baseline measure series is easy done with a User Defined Report in Performance.
When you want to do some statistics a spreadsheet is a common tool.
To export the data from Performance Monitor to a spreadsheet you open the logged data in Performance Monitor and right-click in the graph area. Then you click Save Data As and save the data as tabulator seperated values (.tsv).
To import the data in a Excel spreadsheet you create a new sheet and select the tab Data.Click the item From Text in the panel, and do the proper selections. In Denmark where I live we use comma as decimal seperator and dot as thusand seperator. This is one of the details that could trick you. The first time you do a import you might not get it right, but I am sure you will get it right after a few tries.
When you have more sheets from several measure series you can do some delta compares and produce some nice diagrams for management. Most diagrams I create as X-Y diagrams, but the type depends on what you are looking for or trying to show.
Some basic statistics that are usefull are
- Average =AVERAGE()
- Median =MEDIAN()
- Most Frequent Number =MODE.SNGL()
- Standard Deviation =STDEV.S()
- Min =MIN()
- Max =MAX()
DiscussionBe aware when
- Disk latency (sec/Read & sec/Write) is higher than 20 ms. If the value is less than 8 ms it is excellent.
- Page life expectancy is less than 500 in intervals. If the value is less than 300 in regular spikes this should be investigated.