SQL performance utilization report

• EasyBoy

  SSCertifiable

  Points: 5301

  More actions

  October 25, 2018 at 12:09 pm

  #409154

  I need to run report on monthly basis to capture highest CPU, Memory, I/O, System Queue Length etc.
  How can i achieve that? 

  Thanks

• Michael L John

  One Orange Chip

  Points: 27038

  More actions

  October 25, 2018 at 12:21 pm

  #2011215

  EasyBoy - Thursday, October 25, 2018 12:09 PM

  I need to run report on monthly basis to capture highest CPU, Memory, I/O, System Queue Length etc.
  How can i achieve that? 

  Thanks

  Sorry, this is probably one of the vaguest questions I have ever seen posted. 

  What are you capturing now, and how?

  There are numerous metrics that are related to all of these. 

  For example, using I/O. Do you want:
  Disk Latency? That can be read, write, or total
  Reads?
  Writes?
  Stalls?
  MB written?
  MB read?
  Physical disk?
  Logical Disk?
  Or something else???

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• EasyBoy

  SSCertifiable

  Points: 5301

  More actions

  October 25, 2018 at 12:28 pm

  #2011218

  Michael L John - Thursday, October 25, 2018 12:21 PM

  EasyBoy - Thursday, October 25, 2018 12:09 PM

  I need to run report on monthly basis to capture highest CPU, Memory, I/O, System Queue Length etc.
  How can i achieve that? 

  Thanks

  Sorry, this is probably one of the vaguest questions I have ever seen posted. 

  What are you capturing now, and how?

  There are numerous metrics that are related to all of these. 

  For example, using I/O. Do you want:
  Disk Latency? That can be read, write, or total
  Reads?
  Writes?
  Stalls?
  MB written?
  MB read?
  Physical disk?
  Logical Disk?
  Or something else???

  Regarding to I/O, i am looking for Reads, writes, physical disk and logical disk.
  Memory: Peak of this month is 70%
  System Queue Length: Peak of October is 2 or 3
  CPU: Peak for October is 50%

• Jonathan AC Roberts

  SSCoach

  Points: 18253

  More actions

  October 25, 2018 at 2:12 pm

  #2011245

  EasyBoy - Thursday, October 25, 2018 12:09 PM

  I need to run report on monthly basis to capture highest CPU, Memory, I/O, System Queue Length etc.
  How can i achieve that? 

  Thanks

  A free way to do it is to use perfmon and just pick the statistics you want. you can log to a file or database.
  If you log to a database you can just query the tables at the end of the money to get whatever data you want.

• Michael L John

  One Orange Chip

  Points: 27038

  More actions

  October 26, 2018 at 9:34 am

  #2011370

  EasyBoy - Thursday, October 25, 2018 12:27 PM

  Michael L John - Thursday, October 25, 2018 12:21 PM

  EasyBoy - Thursday, October 25, 2018 12:09 PM

  I need to run report on monthly basis to capture highest CPU, Memory, I/O, System Queue Length etc.
  How can i achieve that? 

  Thanks

  Sorry, this is probably one of the vaguest questions I have ever seen posted. 

  What are you capturing now, and how?

  There are numerous metrics that are related to all of these. 

  For example, using I/O. Do you want:
  Disk Latency? That can be read, write, or total
  Reads?
  Writes?
  Stalls?
  MB written?
  MB read?
  Physical disk?
  Logical Disk?
  Or something else???

  Regarding to I/O, i am looking for Reads, writes, physical disk and logical disk.
  Memory: Peak of this month is 70%
  System Queue Length: Peak of October is 2 or 3
  CPU: Peak for October is 50%

  How did you determine that "System Queue Length" is 2 or 3 for October?

  This article will get you started:
  http://www.sqlservercentral.com/articles/PowerShell/87398/

  You can capture whatever metrics you want, and determine the schedule.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/