What's the best way to get SQLServer:Access Methods:Forwarded Records/sec?

PeterDanielsCRB

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?

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

Alex B

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

Alex B

Hi @PeterDanielsCRB,

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):

SELECT [dopc].[object_name],dopc.cntr_value

FROM sys.dm_os_performance_counters AS dopc

@SERVICENAME + ':Access Methods'

AND dopc.counter_name ='Forwarded Records/sec'

The concatenation of the object name was due to mine being "MSSQL$SQL2017:Access Methods" instead of just being "SQLServer.Access Methods" and this way if you have multiple instances it is going to be run against it will find the right name for each.

You can also raise a feature request on the SQL Monitor Uservoice forum here: https://sqlmonitor.uservoice.com/forums/91743-suggestions/filters/top


I hope that helps!

Kind regards,
Alex

PeterDanielsCRB

Thanks, @Alex B.  I like how you added handling for named instances.  I did go ahead and add the metric as-is.  Turns out it's one of those perf counters that is mis-named - seems that it is a cumulative count and just increases over time.  It's not actually an average #/sec over a sampling time.  

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....