-2

I am working on a project where I need to collect as many SQL performance metrics as possible, all from system views.

I currently have collected, and in parenthesis the system view from which I have collected them:

  • CPU usage % (sys.server_resource_stats)
  • Active worker threads (sys.dm_os_Schedulers)
  • IOPS and Throughput (sys.dm_io_virtual_file_stats)
  • Buffer cache hit ratio (sys.dm_os_performance_counters)
  • Page reads/sec (sys.dm_os_performance_counters)
  • Page writes/sec (sys.dm_os_performance_counters)
  • Lazy writes/sec (sys.dm_os_performance_counters)
  • Memory grants pending (sys.dm_os_performance_counters)

Any suggestions on other metrics I can use, and how can they be obtained from system views? I cannot use a third party resource like SolarWinds or the Performance Monitor. The data must be collected from the system views.

1

I believe there is no one fit all answer for this.

You can refer to documentation of some great monitoring tools available , like sentryone , quest, idera etc. to give you an idea.

Also ton of information would be available on msdn links for such system views.

There are lots of scripts out there which make use of these DMVs and you can get some idea from there like Microsoft tiger tool teams GitHub page, Brent ozar health check scripts followed by Glenn berry DMV diagnostics scripts out there.

in addition if you are looking for VM monitoring:

From memory perspective I guess

Should be memory reservation, ballooning and swapping

From CPU perspective I think CPU ready time

There are some additional in terms of storage like disk latencies and usage.

Also it would be great to monitor host as well in addition to guest machines.

I do not recall but Jonathan from sqlskills or sqlperformance blogs has some of these listed as well

| improve this answer | |
  • Thanks, do you know from which tables I can get this info? – Monya Feldman Aug 6 at 21:33
  • I don't think these are from system views. Based on VM type I guess these metrics can be pulled from perfmom counter's – KASQLDBA Aug 6 at 22:02
  • @Monya - updated my answer with few ref: you can get ideas from Glenn Berry's DMVs which you can see I guess for azure as well – KASQLDBA Aug 6 at 22:53
  • Thanks so much, I've taken a look at the Glen Berry resources and they are exactly what I am looking for. Thanks! – Monya Feldman Aug 6 at 22:56
  • Sure,np, Glad to help – KASQLDBA Aug 6 at 23:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.