SqlServer Plugin Feature - Adding Blocking Query Information

[AakashMasand]

It would be great if we could figure out the Blocking Information and the query causing the block event using telegraf. A lot of third party tools like Idera and SQLMonitor provide the functionality.

Something on the lines of:

[danielnelson]

@m82labs @denzilribeiro Would love to hear your thoughts on if adding these queries to sqlserver would be good. I'm also working a new plugin for ad-hoc queries so if its too expensive/advanced/esoteric that could be a way we allow for these types of queries.

[denzilribeiro]

Query as is a bit more heavy weight than I would prefer, and is missing a few things such as open tran count, app name, etc I was thinking of adding some form of this as yes it would be useful. Note this will be a bit heavier weight than other collectors so far - for example yes convenient to get the object name, but that encompasses the join with partitions. I think there is a simpler form of this that can be collected and has enough info. I can submit a pull request in a few days with that change if you want. Also curious to hear about the plugin for ad-hoc :) would be great. Right now "all" queries follow same interval - is there any thought to allow different intervals ( certain things don't need to be run as often for example).

[danielnelson]

I think there is a simpler form of this that can be collected and has enough info.

That seems great, I think we are on the same page, the database plugins are a delicate balancing act between too much and too little.

---

The ad-hoc query plugins are a set of plugins for databases with a Go SQL driver, initially in Telegraf we would have support for SQLServer, MySQL, and Postgres. The code will be mostly shared though I will probably have named plugins for these 3, plus a generic version that could be useful for users who want to load an external driver (Oracle). The configuration will be spartan: a connection string, and a list of queries/query-scripts along with which columns will be treated as tags and optionally which column will be the timestamp.

Doing multiple intervals in a single plugin is tricky in Telegraf, as the scheduler isn't very sophisticated. With the initial version of the plugin you would need to specify the plugin multiple times:

[[inputs.sqlserver_query]]
  interval = "10s"
[[inputs.sqlserver_query]]
  interval = "1h"

But this does give me an idea, perhaps it would be worthwhile to investigate adding a shared pool if the connection string is the same. The plugin definition would still be the same: multiple plugins with the duplicated connection string, but it could reuse the same connections under the hood.

[danielnelson]

Closed in #6144