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

Question

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.

KASQLDBA · 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

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

One Answer

Add your own answers!

Ask a Question