TransWikia.com

What are some good SQL Server Performance Metrics from System Views?

Database Administrators Asked by Monya Feldman on December 3, 2020

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.

One Answer

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

Answered by KASQLDBA on December 3, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP