What's the best way to get SQLServer:Access Methods:Forwarded Records/sec?
PeterDanielsCRB
Posts: 126 Bronze 3
SQL Monitor 10.0.8
We have a vendor ERP DB (Deltek Vision) that uses heaps...everywhere. I was a little surprised to not find SQLServer:Access Methods:Forwarded Records/sec in the stock metrics in SQL Monitor. I looked for one in the existing custom metrics, but did not find anything matching 'forwarded'. What's the best way to get this? Create a custom metric using the following SQL?
TIA,
-Peter
We have a vendor ERP DB (Deltek Vision) that uses heaps...everywhere. I was a little surprised to not find SQLServer:Access Methods:Forwarded Records/sec in the stock metrics in SQL Monitor. I looked for one in the existing custom metrics, but did not find anything matching 'forwarded'. What's the best way to get this? Create a custom metric using the following SQL?
SELECT dopc.cntr_value
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.object_name = 'SQLServer:Access Methods'
AND dopc.counter_name = 'Forwarded Records/sec'
TIA,
-Peter
Tagged:
Best Answer
-
Alex B
Posts: 1,158 Diamond 4
Hi @PeterDanielsCRB,
I've just realized the whole query wasn't fully displayed in my last post (for me anyway), so edited that... about 50 times and finally went with a quote rather than code block.
Anyway, yeah if it's a cumulative then that's going to be trouble... I suppose you could have an automated process (outside of SQL Monitor) to query it and insert the difference from the previous value into a table and you could then sample that in the custom metric? But that's more of a thought than a tried method (for me anyway).
As for something at the table level, that's not really going to work as the lowest granularity we go for querying these is currently database level I'm afraid.
Kind regards,
Alex
Answers
If that SQL returns you a scalar value for the information you want then yes, that is what you would need to do for a custom metric.
For me, when I checked, I had to write mine like this to get that value for my named instance (named SQL2017):
I hope that helps!
Kind regards,
Alex
Have you visited our Help Center?
I've actually been working more with the "forward_fetch_count" from sys.dm_db_index_operational_stats to find which heaps are getting scanned and accessing the forwarding pointers. Not sure how I would implement a way to track this metric, though, as it would be a metric capture at a table level.
Hmmm....