Fixed a bug in the performance counter query #3175

m82labs · 2017-08-26T13:33:15Z

When running the performance counter query against SQL Server 2016 SP1-CU2, you will see PK errors when inserting the performance counters into the temp table. The performance counter DMV contains duplicate entries which are not handled by the query. Adding DISTINCT to the queries retrieving the results will resolve this issue.

I also tried setting the PK on the temp tables to ignore duplicate keys, but the distinct was marginally cheaper on my system, and likely more backwards compatible.

Required for all PRs:

Signed CLA.
Associated README.md updated.
Has appropriate unit tests.

danielnelson · 2017-08-31T01:43:13Z

@zensqlmonitor Can you review?

zensqlmonitor · 2017-08-31T07:37:26Z

@m82labs, I have SQL Server 2016 SP1 CU3 installed and I can't see any duplicate values...
Could you please post an example ?

m82labs · 2017-09-02T13:44:27Z

@zensqlmonitor running this across my 300+ instances:

SELECT	object_name,
		counter_name,
		instance_name
FROM	sys.dm_os_performance_counters
GROUP BY
		object_name,
		counter_name,
		instance_name
HAVING	COUNT(*) > 1;

Results in 64 instances returning duplicates. The duplicates are isolated to a few of the XTP counters. We use Hekaton on all instances, so it doesn't seem to be related to usage patterns or anything.

Running this on a single instance:

WITH PMCTE AS
(
SELECT	object_name,
		counter_name,
		instance_name
FROM	sys.dm_os_performance_counters
GROUP BY
		object_name,
		counter_name,
		instance_name
HAVING	COUNT(*) > 1
)
SELECT	rtrim(pc.OBJECT_NAME),
		rtrim(pc.counter_name),
		rtrim(pc.instance_name),
		cntr_value,
		cntr_type
FROM	sys.dm_os_performance_counters as pc
		inner join pmcte
		ON pc.object_name = pmcte.object_name
		AND pc.counter_name = pmcte.counter_name
		AND pc.instance_name = pmcte.instance_name

Results in this output:

SQL Server 2016 XTP Cursors	Cursor deletes/sec	MSSQLSERVER	156283976	272696320
SQL Server 2016 XTP Cursors	Cursor deletes/sec	MSSQLSERVER	156283976	272696320
SQL Server 2016 XTP Cursors	Cursor inserts/sec	MSSQLSERVER	1691210617	272696320
SQL Server 2016 XTP Cursors	Cursor inserts/sec	MSSQLSERVER	1691210617	272696320
SQL Server 2016 XTP Cursors	Cursor scans started/sec	MSSQLSERVER	1024733340	272696320
SQL Server 2016 XTP Cursors	Cursor scans started/sec	MSSQLSERVER	1024733340	272696320
SQL Server 2016 XTP Cursors	Cursor unique violations/sec	MSSQLSERVER	132	272696320
SQL Server 2016 XTP Cursors	Cursor unique violations/sec	MSSQLSERVER	132	272696320
SQL Server 2016 XTP Cursors	Cursor updates/sec	MSSQLSERVER	382913851	272696320
SQL Server 2016 XTP Cursors	Cursor updates/sec	MSSQLSERVER	382913851	272696320
SQL Server 2016 XTP Cursors	Cursor write conflicts/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Cursors	Cursor write conflicts/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Cursors	Dusty corner scan retries/sec (user-issued)	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Cursors	Dusty corner scan retries/sec (user-issued)	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Cursors	Expired rows removed/sec	MSSQLSERVER	536961518	272696320
SQL Server 2016 XTP Cursors	Expired rows removed/sec	MSSQLSERVER	536961518	272696320
SQL Server 2016 XTP Cursors	Expired rows touched/sec	MSSQLSERVER	538936634	272696320
SQL Server 2016 XTP Cursors	Expired rows touched/sec	MSSQLSERVER	538936634	272696320
SQL Server 2016 XTP Cursors	Rows returned/sec	MSSQLSERVER	1374587332	272696320
SQL Server 2016 XTP Cursors	Rows returned/sec	MSSQLSERVER	1374587332	272696320
SQL Server 2016 XTP Cursors	Rows touched/sec	MSSQLSERVER	3636879434	272696320
SQL Server 2016 XTP Cursors	Rows touched/sec	MSSQLSERVER	3636879434	272696320
SQL Server 2016 XTP Cursors	Tentatively-deleted rows touched/sec	MSSQLSERVER	215	272696320
SQL Server 2016 XTP Cursors	Tentatively-deleted rows touched/sec	MSSQLSERVER	215	272696320
SQL Server 2016 XTP Garbage Collection	Dusty corner scan retries/sec (GC-issued)	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Garbage Collection	Dusty corner scan retries/sec (GC-issued)	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Garbage Collection	Main GC work items/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Garbage Collection	Main GC work items/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Garbage Collection	Parallel GC work item/sec	MSSQLSERVER	162615	272696320
SQL Server 2016 XTP Garbage Collection	Parallel GC work item/sec	MSSQLSERVER	162615	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec	MSSQLSERVER	4368789	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec	MSSQLSERVER	4368789	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec (first in bucket and removed)	MSSQLSERVER	1391889	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec (first in bucket and removed)	MSSQLSERVER	1391889	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec (first in bucket)	MSSQLSERVER	1391889	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec (first in bucket)	MSSQLSERVER	1391889	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec (marked for unlink)	MSSQLSERVER	114046	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec (marked for unlink)	MSSQLSERVER	114046	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec (no sweep needed)	MSSQLSERVER	522720	272696320
SQL Server 2016 XTP Garbage Collection	Rows processed/sec (no sweep needed)	MSSQLSERVER	522720	272696320
SQL Server 2016 XTP Garbage Collection	Sweep expired rows removed/sec	MSSQLSERVER	3651187	272696320
SQL Server 2016 XTP Garbage Collection	Sweep expired rows removed/sec	MSSQLSERVER	3651187	272696320
SQL Server 2016 XTP Garbage Collection	Sweep expired rows touched/sec	MSSQLSERVER	3688400	272696320
SQL Server 2016 XTP Garbage Collection	Sweep expired rows touched/sec	MSSQLSERVER	3688400	272696320
SQL Server 2016 XTP Garbage Collection	Sweep expiring rows touched/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Garbage Collection	Sweep expiring rows touched/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Garbage Collection	Sweep rows touched/sec	MSSQLSERVER	9750028	272696320
SQL Server 2016 XTP Garbage Collection	Sweep rows touched/sec	MSSQLSERVER	9750028	272696320
SQL Server 2016 XTP Garbage Collection	Sweep scans started/sec	MSSQLSERVER	3154446	272696320
SQL Server 2016 XTP Garbage Collection	Sweep scans started/sec	MSSQLSERVER	3154446	272696320
SQL Server 2016 XTP IO Governor	Insufficient Credits Waits/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP IO Governor	Insufficient Credits Waits/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP IO Governor	Io Issued/sec	MSSQLSERVER	30068	272696320
SQL Server 2016 XTP IO Governor	Io Issued/sec	MSSQLSERVER	30068	272696320
SQL Server 2016 XTP IO Governor	Log Blocks/sec	MSSQLSERVER	1488428605	272696320
SQL Server 2016 XTP IO Governor	Log Blocks/sec	MSSQLSERVER	1488428605	272696320
SQL Server 2016 XTP IO Governor	Missed Credit Slots	MSSQLSERVER	0	65792
SQL Server 2016 XTP IO Governor	Missed Credit Slots	MSSQLSERVER	0	65792
SQL Server 2016 XTP IO Governor	Stale Rate Object Waits/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP IO Governor	Stale Rate Object Waits/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP IO Governor	Total Rate Objects Published	MSSQLSERVER	0	65792
SQL Server 2016 XTP IO Governor	Total Rate Objects Published	MSSQLSERVER	0	65792
SQL Server 2016 XTP Phantom Processor	Dusty corner scan retries/sec (Phantom-issued)	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Phantom Processor	Dusty corner scan retries/sec (Phantom-issued)	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Phantom Processor	Phantom expired rows removed/sec	MSSQLSERVER	2	272696320
SQL Server 2016 XTP Phantom Processor	Phantom expired rows removed/sec	MSSQLSERVER	2	272696320
SQL Server 2016 XTP Phantom Processor	Phantom expired rows touched/sec	MSSQLSERVER	244	272696320
SQL Server 2016 XTP Phantom Processor	Phantom expired rows touched/sec	MSSQLSERVER	244	272696320
SQL Server 2016 XTP Phantom Processor	Phantom expiring rows touched/sec	MSSQLSERVER	52	272696320
SQL Server 2016 XTP Phantom Processor	Phantom expiring rows touched/sec	MSSQLSERVER	52	272696320
SQL Server 2016 XTP Phantom Processor	Phantom rows touched/sec	MSSQLSERVER	3681419	272696320
SQL Server 2016 XTP Phantom Processor	Phantom rows touched/sec	MSSQLSERVER	3681419	272696320
SQL Server 2016 XTP Phantom Processor	Phantom scans started/sec	MSSQLSERVER	3223205	272696320
SQL Server 2016 XTP Phantom Processor	Phantom scans started/sec	MSSQLSERVER	3223205	272696320
SQL Server 2016 XTP Storage	Checkpoints Closed	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Checkpoints Closed	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Checkpoints Completed	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Checkpoints Completed	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Core Merges Completed	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Core Merges Completed	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Merge Policy Evaluations	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Merge Policy Evaluations	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Merge Requests Outstanding	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Merge Requests Outstanding	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Merges Abandoned	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Merges Abandoned	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Merges Installed	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Merges Installed	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Total Files Merged	MSSQLSERVER	0	65792
SQL Server 2016 XTP Storage	Total Files Merged	MSSQLSERVER	0	65792
SQL Server 2016 XTP Transaction Log	Log bytes written/sec	MSSQLSERVER	52147737	272696320
SQL Server 2016 XTP Transaction Log	Log bytes written/sec	MSSQLSERVER	52147737	272696320
SQL Server 2016 XTP Transaction Log	Log records written/sec	MSSQLSERVER	1270137	272696320
SQL Server 2016 XTP Transaction Log	Log records written/sec	MSSQLSERVER	1270137	272696320
SQL Server 2016 XTP Transactions	Cascading aborts/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Transactions	Cascading aborts/sec	MSSQLSERVER	0	272696320
SQL Server 2016 XTP Transactions	Commit dependencies taken/sec	MSSQLSERVER	8	272696320
SQL Server 2016 XTP Transactions	Commit dependencies taken/sec	MSSQLSERVER	8	272696320
SQL Server 2016 XTP Transactions	Read-only transactions prepared/sec	MSSQLSERVER	2939299711	272696320
SQL Server 2016 XTP Transactions	Read-only transactions prepared/sec	MSSQLSERVER	2939299711	272696320
SQL Server 2016 XTP Transactions	Save point refreshes/sec	MSSQLSERVER	657139447	272696320
SQL Server 2016 XTP Transactions	Save point refreshes/sec	MSSQLSERVER	657139447	272696320
SQL Server 2016 XTP Transactions	Save point rollbacks/sec	MSSQLSERVER	358081731	272696320
SQL Server 2016 XTP Transactions	Save point rollbacks/sec	MSSQLSERVER	358081731	272696320
SQL Server 2016 XTP Transactions	Save points created/sec	MSSQLSERVER	2944864727	272696320
SQL Server 2016 XTP Transactions	Save points created/sec	MSSQLSERVER	2944864727	272696320
SQL Server 2016 XTP Transactions	Transaction validation failures/sec	MSSQLSERVER	2	272696320
SQL Server 2016 XTP Transactions	Transaction validation failures/sec	MSSQLSERVER	2	272696320
SQL Server 2016 XTP Transactions	Transactions aborted by user/sec	MSSQLSERVER	907053	272696320
SQL Server 2016 XTP Transactions	Transactions aborted by user/sec	MSSQLSERVER	907053	272696320
SQL Server 2016 XTP Transactions	Transactions aborted/sec	MSSQLSERVER	3953992	272696320
SQL Server 2016 XTP Transactions	Transactions aborted/sec	MSSQLSERVER	3953992	272696320
SQL Server 2016 XTP Transactions	Transactions created/sec	MSSQLSERVER	2944864727	272696320
SQL Server 2016 XTP Transactions	Transactions created/sec	MSSQLSERVER	2944864727	272696320

These are boxes that may have started their lives as SQL Server 2014, then went to SQL 2016 CTP and then upgraded and patched several times to get to the latest CU on SP1. It is quite possible this might be a hold-over from one of the CTPs or something. I should note as well that this is happening on CU2 and CU3 for me.

m82labs · 2017-09-02T13:50:15Z

@zensqlmonitor as a test, I reloaded perfmon counters on one of the boxes and the duplicates remain. the Get-Counter PowerShell cmdlet also shows the duplicates.

danielnelson · 2017-09-13T19:21:00Z

@m82labs Can you rebase, I think you will need to do an interactive rebase and remove the commits that are not part of your changes.

…ver 2016 SP1-CU2. The performance counter DMV contains duplicate entries which are not handled by the query.

m82labs · 2017-09-14T00:55:29Z

@danielnelson done, it should be good now.

(cherry picked from commit 1c5ebd4)

danielnelson modified the milestones: 1.4.0, 1.4.1 Aug 28, 2017

danielnelson added the fix pr to fix corresponding bug label Sep 13, 2017

Fixed a bug in the performance counter query when run against SQL Ser…

8c481bc

…ver 2016 SP1-CU2. The performance counter DMV contains duplicate entries which are not handled by the query.

danielnelson merged commit 1c5ebd4 into influxdata:master Sep 14, 2017

danielnelson pushed a commit that referenced this pull request Sep 14, 2017

Fix duplicate keys in perf counters sqlserver query (#3175)

558ab0c

(cherry picked from commit 1c5ebd4)

maxunt pushed a commit that referenced this pull request Jun 26, 2018

Fix duplicate keys in perf counters sqlserver query (#3175)

53cb9ff

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fixed a bug in the performance counter query #3175

Fixed a bug in the performance counter query #3175

m82labs commented Aug 26, 2017 •

edited

Loading

danielnelson commented Aug 31, 2017

zensqlmonitor commented Aug 31, 2017

m82labs commented Sep 2, 2017 •

edited

Loading

m82labs commented Sep 2, 2017

danielnelson commented Sep 13, 2017

m82labs commented Sep 14, 2017

Fixed a bug in the performance counter query #3175

Fixed a bug in the performance counter query #3175

Conversation

m82labs commented Aug 26, 2017 • edited Loading

Required for all PRs:

danielnelson commented Aug 31, 2017

zensqlmonitor commented Aug 31, 2017

m82labs commented Sep 2, 2017 • edited Loading

m82labs commented Sep 2, 2017

danielnelson commented Sep 13, 2017

m82labs commented Sep 14, 2017

m82labs commented Aug 26, 2017 •

edited

Loading

m82labs commented Sep 2, 2017 •

edited

Loading