July 17, 2017 at 10:13 am
Hi,
Are there any counters that can give more granularity for this beyond the server level? I am unable to find any in the WMI, profiler or extended events.
The issue is that the counter is fairly constant and spiking pretty high at times on our server. I queried the tables for forwarded records and found a number of them but none were too big. I did a little sampling with sys.dm_db_index_usage_stats to try and see which had the most I/O reads but I didn't see nearly enough to explain the levels of the counter. I rebuilt all of them over the weekend but we're still seeing it. I could go add CI's to all of them but given my findings I'm not sure it's the answer...
We do have heavy tempdb usage, could they be coming from there ( or another system table )? I tried looking at tempdb but had to quickly give that up as it is way to busy and I was blocking.
Thanks,
Scott
July 17, 2017 at 11:21 am
Yeah, you can use sys.dm_db_index_operational_stats for that.
Something like this:
SELECT database_name=DB_NAME(database_id),
table_name =OBJECT_NAME(object_id, database_id),
forwarded_fetch_count
FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
WHERE index_id=0 AND forwarded_fetch_count>0
ORDER BY forwarded_fetch_count DESC;
Keep in mind that the data from sys.dm_db_index_operational_stats is not guaranteed to persist, as it is subject to some caveats, as mentioned in the documentation at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql
If you're consistently seeing high values in perfmon, though, the heaps in question are probably pretty active, so this should work well in your case.
Cheers!
July 17, 2017 at 12:03 pm
Thank you, I looked at that one but didn't see that column. The largest value is 2852 for all tables but the counter is way over that even in just the last hour so I guess these are coming from tempdb.
I used the view like this so I could script it against the databases and hit just the heaps instead of letting it walk the whole server .
;with heaps as (
select
DB_NAME(DB_ID()) dbname, object_name ( p.object_id ) objname, sum(row_count) row_count,
DB_ID() database_id, p.object_id objectid
from
sys.dm_db_partition_stats p
join sys.objects o on o.object_id = p.object_id
WHERE
index_id = 0 and o.is_ms_shipped = 0 --and row_count > 0
group by p.object_id
)
select
h.*,
forwarded_fetch_count
from heaps h
cross apply sys.dm_db_index_operational_stats(database_id, objectid, 0, null) ps
Thanks,
Scott
July 17, 2017 at 12:07 pm
OK, I used your query and mystery solved ( mine walked only user DBs ). As I suspected...
database_name table_name forwarded_fetch_count
tempdb #ACB65264 19660418
tempdb #B3933082 6790229
tempdb #A08DAD08 3121187
tempdb #B0FAEBA2 2248807
tempdb #ADAA769D 1982640
tempdb #A8C43DCB 814180
etc.
Only one user table had a few fetches. Is it worth trying to track the worst of them back to the queries?
July 17, 2017 at 12:31 pm
Yes, quite likely it is worth it.
Those are very high counts and would be affecting performance of the queries involved pretty drastically, unless it was some odd case like those forwarded_fetch_count numbers coming from only having a couple forwarded records but scanning the table millions of times (and even in that case, the queries should be tracked down, just then because of the scan counts 🙂 )
Cheers!
July 17, 2017 at 3:15 pm
Sounds good, thanks much for your help Jacob.
--
Scott
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply