October 21, 2016 at 4:03 pm
On a SQL 2016 server the Server Activity History report from Data Collections is only returning data for some charts (Wait stats and memory usage). The other charts such as "SQL Server Activity" show "no data available". I have restarted collection sets and cleared the MDW cache, also checked the logs and there are no errors. When running the query below from the server activity history.rdl it returns no data because the [snapshots.performance_counters] view contains no rows:
SELECT SUBSTRING(pc.path, CHARINDEX(N'\', pc.path, 2)+1, LEN(pc.path) - CHARINDEX(N'\', pc.path, 2)) as series,
s.snapshot_time_id,
CONVERT (datetime, SWITCHOFFSET (CAST (s.snapshot_time AS datetimeoffset(7)), '+00:00')) AS snapshot_time,
CONVERT (datetime, SWITCHOFFSET (CAST (pc.collection_time AS datetimeoffset(7)), '+00:00')) AS collection_time,
pc.formatted_value
FROM snapshots.performance_counters pc
JOIN core.snapshots s ON (s.snapshot_id = pc.snapshot_id)
WHERE s.instance_name = <SQL instance>
AND s.snapshot_time_id BETWEEN @snapshot_start_time_ID AND @snapshot_end_time_ID
AND (pc.performance_object_name LIKE '%SQL%:General Statistics' OR pc.performance_object_name LIKE '%SQL%:SQL Statistics')
AND pc.performance_counter_name IN ('Logins/sec', 'Logouts/sec', 'Batch Requests/sec', 'Transactions',
'User Connections', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')
Any ideas?
Thanks,
Matt
MattF
October 27, 2016 at 11:14 am
Ok - found a Connect item for this - please vote it up: https://connect.microsoft.com/SQLServer/feedback/details/2887824/data-collector-does-not-collect-performance-counter
MattF
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply