Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Fixed a bug in the performance counter query #3175

Merged
merged 1 commit into from
Sep 14, 2017
Merged

Fixed a bug in the performance counter query #3175

merged 1 commit into from
Sep 14, 2017

Conversation

m82labs
Copy link
Contributor

@m82labs m82labs commented Aug 26, 2017

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 danielnelson modified the milestones: 1.4.0, 1.4.1 Aug 28, 2017
@danielnelson
Copy link
Contributor

@zensqlmonitor Can you review?

@zensqlmonitor
Copy link
Contributor

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

@m82labs
Copy link
Contributor Author

m82labs commented Sep 2, 2017

@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
Copy link
Contributor Author

m82labs commented Sep 2, 2017

@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
Copy link
Contributor

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

@danielnelson danielnelson added the fix pr to fix corresponding bug label Sep 13, 2017
…ver 2016 SP1-CU2. The performance counter DMV contains duplicate entries which are not handled by the query.
@m82labs
Copy link
Contributor Author

m82labs commented Sep 14, 2017

@danielnelson done, it should be good now.

@danielnelson danielnelson merged commit 1c5ebd4 into influxdata:master Sep 14, 2017
danielnelson pushed a commit that referenced this pull request Sep 14, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
fix pr to fix corresponding bug
Projects
None yet
Development

Successfully merging this pull request may close these issues.

4 participants