[inputs.sqlserver] ServerProperties expensive - can it safely be excluded?

[leepfrog-ger]

I've started to test with the sqlserver inputs plugin. It seems to work nicely but I was concerned about the possible performance impact on the SQL server that is being monitored.

Therefore I took a look at the SQL profiler to see how long the queries generated by the plugin actually take. It was immediatly obvious that the sqlServerPropertiesV2 query was the most "expensive" one in terms of execution duration. It took between 500 and 2000ms to complete, even though it just returns a dozen of fields (which would not be relevant for my use case given the "cost").

I am however not sure if other data or processes within the plugin depend on it as it is not listed in the list of metric queries that can be excluded. I've tried to exclude it on a test system by using exclude_query = [ 'ServerProperties' ] and this seems to work fine (as in: data is still coming in), I am however not sure if it could cause trouble down the line?

@m82labs tagging you because I've seen you've done a lot of work in that plugin (thanks for that!)

Thanks!

[m82labs]

@leepfrog I plan on refactoring this query a bit as it is VERY expensive on a few of my instances. I'll try to get a change in this week to address this.

…

On Tue, Dec 11, 2018 at 8:34 AM leepfrog ***@***.***> wrote: I've started to test with the sqlserver inputs plugin. It seems to work nicely but I was concerned about the possible performance impact on the SQL server that is being monitored. Therefore I took a look at the SQL profiler to see how long the queries generated by the plugin actually take. It was immediatly obvious that the sqlServerPropertiesV2 <https://github.com/influxdata/telegraf/blob/b43165f2d859813a077a4460e96e5b94400308b2/plugins/inputs/sqlserver/sqlserver.go#L391> query was the most "expensive" one in terms of execution duration. It took between 500 and 2000ms to complete, even though it just returns a dozen of fields (which would not be relevant for my use case given the "cost"). I am however not sure if other data or processes within the plugin depend on it as it is not listed in the list of metric queries that can be excluded. I've tried to exclude it on a test system by using exclude_query = [ 'ServerProperties' ] and this seems to work fine (as in: data is still coming in), I am however not sure if it could cause trouble down the line? @m82labs <https://github.com/m82labs> tagging you because I've seen you've done a lot of work in that plugin (thanks for that!) Thanks! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#5132>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AIS1dGa9QCFL4vChq35TqIxUSd3h8qlIks5u37RJgaJpZM4ZNauq> .

[m82labs]

I have a local copy running with some changes to help this. I am letting it run for a bit and then I should be able to put in a PR.

[leepfrog]

@leepfrog-ger :)

[m82labs]

(ノಠ益ಠ)ノ彡┻━┻

[leepfrog-ger]

@leepfrog whaaaat!

@m82labs wow that was fast. I do not have a go build environment but I'll try the nightly once this is merged.
For now are you aware of any compliations when the ServerProperties module is disabled with the exclude_query = [ 'ServerProperties' ] configuration (apart from the server properties metrics missing)?

[danielnelson]

Closed by #5135

[m82labs]

@leepfrog-ger no complications, as you said, you just won't get that data.

[leepfrog-ger]

Just tested with the most recent nightly build. The values have greatly improved, even on a server that is being hammered the average duration is in the ballpak of 120ms.

Thanks again for the patch and the great input module

[m82labs]

Great to hear!

…

On Thu, Dec 20, 2018 at 7:45 AM leepfrog ***@***.***> wrote: Just tested with the most recent nightly build. The values have greatly improved, even on a server that is being hammered the average duration is in the ballpak of 120ms. Thanks again for the patch and the great input module — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#5132 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AIS1dJoTfsbosw62P0-Xn-LOPK8o8R3sks5u64ZtgaJpZM4ZNauq> .