influxdata / influxdata/telegraf

Problem & FIX to SQL Server Input Plugin round large values in Performance Counters

Open
#10,133 2 comments 0 reactions 0 assignees View on GitHub
area/sqlserver bug plugin/input
Dominant language
Go
Stars
17.8k
Forks
5.8k
Avg merge
1d 20h
Merged PRs (30d)
161

Description

# Problem: SQL Server Input Plugin round large values in Performance Counters

When the performance counter has very large values, the query that obtains the data from the SQL Server database performs a rounding by means of a statement CAST (……… float (10)) that prevents the precise calculation of derived values.

## Solution
The solution is change all CAST statements in this queries to increase the precision by changing to CAST (……… float (53)).

### Fragment of the query with the problem

```
SELECT
'sqlserver_performance' AS [measurement]
,REPLACE(@@SERVERNAME,'\',':') AS [sql_instance]
,pc.[object_name] AS [object]
,pc.[counter_name] AS [counter]
,CASE pc.[instance_name] WHEN '_Total' THEN 'Total' ELSE ISNULL(pc.[instance_name],'') END AS [instance]
,CAST(
CASE
WHEN pc.[cntr_type] = 537003264 AND pc1.[cntr_value] > 0
THEN (pc.[cntr_value] * 1.0) / (pc1.[cntr_value] * 1.0) * 100
ELSE pc.[cntr_value]
--------------------------------------------------------------------
--- Round too much for large values and cumulative counter
--- Change 10 for 53 to solve the problem
--------------------------------------------------------------------
END AS float(10)) AS [value]
--------------------------------------------------------------------
,CAST(pc.[cntr_type] AS varchar(25)) AS [counter_type]
FROM @PCounters AS pc
LEFT OUTER JOIN @PCounters AS pc1
ON (
pc.[counter_name] = REPLACE(pc1.[counter_name],' base','')
OR pc.[counter_name] = REPLACE(pc1.[counter_name],' base',' (ms)')
)
AND pc.[object_name] = pc1.[object_name]
AND pc.[instance_name] = pc1.[instance_name]
AND pc1.[counter_name] LIKE '%base'
WHERE
pc.[counter_name] NOT LIKE '% base'
OPTION(RECOMPILE)
```

Contributor guide

Open the contributing guide

Research direction

Search the repository for the SQL Server input plugin and the CAST expression using float(10), then read the surrounding performance-counter query. Update the relevant precision casts and verify that the query preserves large counter values without rounding.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.