Monitoring for SQL server performance issues

• ThompsonAdmin

  SSC Rookie

  Points: 47

  More actions

  September 11, 2015 at 7:30 am

  #399028

  Hello all, I'm new to SQL Server management and i'm trying to setup some monitoring and establish a baseline. I have noticed some people using SQL queries to get values from SQL's performance counters and others using windows performance monitor. When comparing these, specifically, SQLServer:Buffer Manager - Page reads/sec and page writes/sec. SQL Performance data gives High values and yet in windows performance monitor it shows 0.00. That makes me a little confused. So should I use values from SQL Server performance using a query or should I be using windows performance monitor, and why is there a difference?

  (I have attached a screenshot.)

  these are the counters I am planning to use.

  OBJECTCOUNTER

  Memory –Available MBytes

  Memory -Pages/sec

  Paging File –% Usage

  Physical Disk –Avg. Disk sec/Read

  Physical Disk –Avg. Disk sec/Write

  Physical Disk –Disk Reads/sec

  Physical Disk –Disk Writes/sec

  Physical Disk -% Disk Time (add all physical disks not total)

  Processor –% Processor Time (add all processors not total)

  SQLServer: Buffer Manager –Buffer Cache Hit Ratio

  SQLServer: Buffer Manager –Buffer Cache Hit Ratio Base

  SQLServer: Buffer Manager –Page Lookups /Sec

  SQLServer: Buffer Manager –Free Pages

  SQLServer: Buffer Manager –Lazy Write/sec

  SQLServer: Buffer Manager –Page Reads/sec

  SQLServer: Buffer Manager –Page Writes/sec

  SQLServer: Buffer Manager –Page life expectancy

  SQLServer: Locks -Number of Deadlocks/sec (total)

  SQLServer: Database -Transactions/sec (total)

  SQLServer: SQL Statistics –Batch Requests/sec

  SQLServer: SQL Statistics –Compilations/sec

  SQLServer: SQL Statistics –Recompilations/sec

  SQLServer: General Statistics – User Connections

  SQLServer: Memory Manager –Memory Grants Pending

  SQLServer: Memory Manager –Target Server Memory(KB)

  SQLServer: Memory Manager –Total Server memory (KB)

  System –Processor Queue Length