SQL Performance - One counter to rule them all - identify CPU/MEM/DISK/NET pressure

• sqld-_-ba

  SSCarpal Tunnel

  Points: 4365

  More actions

  February 27, 2018 at 3:30 pm

  #327698

  I've been performance tuning SQL servers for a few years as a DBA.

  I'm trying to create a fast-food version of performance metrics that can quickly (in 5 minutes) and accurately (provable) answer a question from Management "Does this server need more/faster _ ?"

  '_' being one of these 4 possible bottelenecks in an IT-stack bottom up (from server-perspective, without going in to the app/code/ui):

  1. NETWORK
   
  2. DISK
   
  3. MEMORY
   
  4. CPU

  There are thousands of counters, articles, products to help monitor these. But is there a simple, instant and accurate script that can pinpoint if any of these 4 need to be scaled up or scaled out ?

  e.g.
  sys.dm_os_wait_stats - SOS_SCHEDULER_YIELD has high signal waits => need either more or faster CPU.
  PAGEIO_LATCH => need more files or faster DISK

  Are these 2 accurate? Does Page Life Expectancy accurately 'PROVE' the need for more memory ? What is your **GO-TO** script for diagnosing a performance issue ?

  I've used sp_whoisactive, sp_blitz, Glenn's DMVs, Spotlight, Idera etc. but am yet to come across a script that will satisfy a CIO's question about where to spend budget money, or that will correctly blame the problems on bad code, or slow SAN, or the ISP.

  Everytime any (Network/Systems/DBA/App) engineer points fingers at the other team, we have to 'PROVE' our statement, and with Virtualization and Cloud, without ideal test environments, without downtime, it's getting increasingly difficult to *provably* pinpoint the source of server performance issues, other than maybe Task Manager <*excuse the rant*>