High cardinality when using SqlRequests query due to statement_text tag

[danielnelson]

Relevant telegraf.conf:

[[inputs.sqlserver]]
  servers = ["..."]
  query_version = 2

System info:

Telegraf 1.12.5

Steps to reproduce:

1. Run input plugin without excluding the SqlRequests query.
2. Monitor cardinality

Expected behavior:

Smaller/finite number of series are created.

Actual behavior:

I believe a new series are created for each unique query due to the statement_text tag, depending on the query pattern, this can grow quite rapidly.

Additional info:

Small selection of example output: input-filter.txt

Community Post

[danielnelson]

@denzilribeiro @m82labs This is a pretty large issue for this query I think, can you verify if this query works as I'm understanding: that a new tag value is created for each unique query.

[denzilribeiro]

Are all strings by default a tag? Yes statements could be unique and there could be a lot of them. But without the statement this whole SqlRequests collector is in a way useless

[danielnelson]

Right now this plugin does add all strings as tags but that could be changed. I notice that the query_hash is a field, I assume it is being converted to a []byte in the sql driver.

If the goal is to track specific queries over time, I think there is no way around the query having the cardinality of the number of unique queries that are run. This is similar to issues we have in container monitoring where each time the container runs it is identified by a UUID.

This may or may not be an issue, depending on your workload and your retention policy. I think that the fix for this is better documentation on how to use the query and manage the database cardinality.

[sawo1337]

I believe statement_text should have the option to be a field, rather than a tag. Depending on use cases, you may want to monitor for slow queries, for example, you don't need the statement to be a tag there.

[danielnelson]

I can see one possibly wanting it either way, I'm not entirely sure we should make this switch. I think for now the best course is to use the converter processor to switch it to a string field.