Cannot insert the value NULL error with PerformanceCounters query

[danielnelson]

After upgrading to Telegraf 1.14.0, I am unable to get the PerformaceCounters query to work with an Azure Basic teir server.

Relevant telegraf.conf:

[[inputs.sqlserver]]
  servers = []
  query_version = 2
  azuredb = true
  include_query = ['PerformanceCounters']

System info:

Telegraf 1.14.0

Steps to reproduce:

1. Setup Basic tier SQL Server on Azure
2. telegraf --test

Expected behavior:

Output similar to with Telegraf 1.13

> sqlserver_performance,counter=Lock\ Wait\ Count,database_name=master,host=loaner,instance=XOdbcGroup,object=SQLServer:Workload\ Group\ Stats,sql_instance=telegraf value=0 1586814619000000000
> sqlserver_performance,counter=CPU\ Usage\ (time),database_name=master,host=loaner,instance=XOdbcGroup,object=SQLServer:Workload\ Group\ Stats,sql_instance=telegraf value=7 1586814619000000000
> sqlserver_performance,counter=CPU\ Limit\ Violation\ Count,database_name=master,host=loaner,instance=XOdbcGroup,object=SQLServer:Workload\ Group\ Stats,sql_instance=telegraf value=0 1586814619000000000

Actual behavior:

2020-04-13T21:52:33Z I! Cannot insert the value NULL into column 'instance_name', table '@PCounters'; column does not allow nulls. INSERT fails.
2020-04-13T21:52:33Z E! [inputs.sqlserver] Error in plugin: mssql: Cannot insert the value NULL into column 'instance_name', table '@PCounters'; column does not allow nulls. INSERT fails.

Additional info:

[danielnelson]

cc @denzilribeiro

[denzilribeiro]

@danielnelson is this true only of Basic tier? Most new DB's are Vcore model, haven't ever tested with Basic given it has a 2GB limit and not sure anyone who is on it actually does detailed monitoring

[denzilribeiro]

BTW just instantiated a basic tier and tested the query atleast works fine will have to pull latestest branch and test.

[danielnelson]

I've only tested with basic tier and essentially an empty database.

[andrewmitchell21]

@danielnelson @denzilribeiro I am seeing this issue on a vCore GP Gen 4 SKU, this issue seems to also eat up CPU as disabling this counter seems to be the only resolution

[denzilribeiro]

@andrewmitchell21 are you seeing this? If so will send you a TSQL script outside of the collector

"Cannot insert the value NULL into column 'instance_name', table '@PCounters'; column does not allow nulls. INSERT fails."

I don't get the eating CPU - this was more of an error.

[denzilribeiro]

FYI for CPU - see this #7236 ( and associated fix) - is your DB at compat level 140 or earlier?

[andrewmitchell21]

@danielegozzi I might have just put the two together and made an assumption, however, I do still see the performance degradation with this counter enabled and I am using v1.41

[inputs.sqlserver] Error in plugin: mssql: Cannot insert the value NULL into column 'instance_name', table '@PCounters'; column does not allow nulls. INSERT fails

Most of the databases are at a compatibility level of 150 or 140, I believe 150 is backwards compatible with 140

[denzilribeiro]

Can you please run the query itself and give me the output? of these 2 statements on the server hitting the error in collecting?
a. SELECT DISTINCT
RTrim(spi.object_name) object_name,
RTrim(spi.counter_name) counter_name,
CAST(spi.cntr_value AS BIGINT) AS cntr_value,
spi.cntr_type
FROM sys.dm_os_performance_counters AS spi
where instance_name is null

b. Result for this query:

	SELECT

'SQLServer:Workload Group Stats' AS object,
counter,instance,CAST(vs.value AS BIGINT) AS value,1
FROM
(
SELECT
rgwg.name AS instance,
rgwg.total_request_count AS "Request Count",
rgwg.total_queued_request_count AS "Queued Request Count",
rgwg.total_cpu_limit_violation_count AS "CPU Limit Violation Count",
rgwg.total_cpu_usage_ms AS "CPU Usage (time)",
rgwg.total_cpu_usage_preemptive_ms AS "Premptive CPU Usage (time)",
rgwg.total_lock_wait_count AS "Lock Wait Count",
rgwg.total_lock_wait_time_ms AS "Lock Wait Time",
rgwg.total_reduced_memgrant_count AS "Reduced Memory Grant Count"
FROM sys.dm_resource_governor_workload_groups AS rgwg
INNER JOIN sys.dm_resource_governor_resource_pools AS rgrp
ON rgwg.pool_id = rgrp.pool_id
) AS rg
UNPIVOT (
value FOR counter IN ( [Request Count], [Queued Request Count], [CPU Limit Violation Count], [CPU Usage (time)]
, [Premptive CPU Usage (time)], [Lock Wait Count], [Lock Wait Time], [Reduced Memory Grant Count] )
) AS vs
where instance is null

[andrewmitchell21]

@denzilribeiro both of those queries don't return any results, however, if I change where instance_name is null to = '' I get results on the first query but on the second query nothing is returned with either query, would you like me to share these and if so how?

if it helps running:

SELECT COUNT(*) FROM sys.dm_os_performance_counters where instance_name = ''

returns 294 records

[denzilribeiro]

Empty is not the same as NULL.

DECLARE @PCounters TABLE
(
object_name nvarchar(128),
counter_name nvarchar(128),
instance_name nvarchar(128),
cntr_value bigint,
cntr_type INT,
Primary Key(object_name, counter_name, instance_name)
);
-- Assuming this works
insert into @PCounters
SELECT object_name,counter_name,instance_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters where instance_name = ''

-- But this does not?
insert into @PCounters
SELECT top 1 object_name,counter_name,NULL,cntr_value,cntr_type FROM sys.dm_os_performance_counters where instance_name = ''

[andrewmitchell21]

Yep aware of them not being the same, and yeah top insert works but the bottom doesn't @denzilribeiro

[denzilribeiro]

Can we sync up online ( easier to debug)- denzilr@microsoft.com will take like 5 mins if can get a repro - has to be a specific combination of things
Try this query - does it work?

SET DEADLOCK_PRIORITY -10;
DECLARE @PCounters TABLE
(
object_name nvarchar(128),
counter_name nvarchar(128),
instance_name nvarchar(128),
cntr_value bigint,
cntr_type INT,
Primary Key(object_name, counter_name, instance_name)
);

INSERT INTO @PCounters
SELECT DISTINCT
RTrim(spi.object_name) object_name,
RTrim(spi.counter_name) counter_name,
CASE WHEN (
RTRIM(spi.object_name) LIKE '%:Databases'
OR RTRIM(spi.object_name) LIKE '%:Database Replica'
OR RTRIM(spi.object_name) LIKE '%:Catalog Metadata'
OR RTRIM(spi.object_name) LIKE '%:Query Store'
OR RTRIM(spi.object_name) LIKE '%:Columnstore'
OR RTRIM(spi.object_name) LIKE '%:Advanced Analytics')
AND TRY_CONVERT(uniqueidentifier, spi.instance_name)
IS NOT NULL -- for cloud only
THEN d.name
WHEN RTRIM(object_name) LIKE '%:Availability Replica'
AND TRY_CONVERT(uniqueidentifier, spi.instance_name) IS NOT NULL -- for cloud only
THEN d.name + RTRIM(SUBSTRING(spi.instance_name, 37, LEN(spi.instance_name)))
ELSE RTRIM(spi.instance_name)
END AS instance_name,
CAST(spi.cntr_value AS BIGINT) AS cntr_value,
spi.cntr_type
FROM sys.dm_os_performance_counters AS spi
LEFT JOIN sys.databases AS d ON LEFT(spi.instance_name, 36) -- some instance_name values have an additional identifier appended after the GUID
= CASE WHEN -- in SQL DB standalone, physical_database_name for master is the GUID of the user database
d.name = 'master' AND TRY_CONVERT(uniqueidentifier, d.physical_database_name) IS NOT NULL
THEN d.name
ELSE d.physical_database_name
END

[andrewmitchell21]

@denzilribeiro sure, do you use ms teams?

[denzilribeiro]

Yep please ping me on teams

[denzilribeiro]

Talking to Andrew and getting some details, was able to repro it ONLY for Elastic pools. Has to do with fix for this #6570 . Wierdly there is a guid instance_name in dm_os_performance_counters that isnt in sys.databases. I can work around it easily but want to figure out why before masking the problem with a fix.