No metrics for collector: query works in one case, not in other #154
Comments
|
Hey @jakob-reesalu, I commented on the logging stuff in the PR, please take a look. As for the query issue, I'm happy to pair up to investigate and solve the issue. 👍 Could you share the message you get if you run the full query? If you like we can also hop on a call or something, as I don't really have MSSQL around to try things out on my side. What do you think? |
|
@burningalchemist Thanks a lot! I didn't try the full query iterating through all DBs as it takes 15+ mins. But when manipulating it to just do the first DB in the list (which takes 1 sec querying the DB server directly), I get no error, just an infomsg of the returned columns. Log level is debug. I'm up for a call, just gotta have lunch first. When are you available and how to contact you? Maybe on Googlemeet? |
|
@jakob-reesalu Feel free to pick up the slot here: https://calendly.com/szyubin |
|
@burningalchemist Dang it, I gotta leave at about 4pm specifically today. xD I'll continue troubleshooting during the day, maybe a call tomorrow instead? |
|
@jakob-reesalu Yeah, tomorrow works too, also after 4pm unfortunately. But in the meantime, if could drop any intermediate results, that might be useful. 👍 |
|
Could you drop me a screenshot from SQL Management Studio (with sensitive parts removed if there are any) for the full query you expect to run? I might have an idea. 👍 |
|
@burningalchemist Alright! Here you go: NOTE: There's a typo on AvgFragmentationInPREcent in the image but got that fixed Here's the collector config: |
|
@burningalchemist Having been away on lunch, letting the exporter run, I found a bunch of "context deadline exceeded" errors (this is the above posted query but set to only check 1 DB): "index_fragmentation_checks" is the collector at issue Then afterwards getting these messages, without errors: |
|
@jakob-reesalu thanks, let's try to catch up tomorrow, I have some thoughts. 👍 |
|
@burningalchemist Yo! So I tried usql as we talked about. It gave this error: Problem is, the variable is declared in the query above. Googling on it, some ppl suggested this can happen when mixing dynamic and static SQL so eventually I put the whole query within the @Sql variable and then EXECUTE(@Sql) on that. Didn't get any error message but nor any result. Tried it in SQL Exporter and no change from before. While testing various things on the way I would get a result "IF 21" but have no idea what that was about, and the usql github didn't have any threads on that. An interesting note is that the other queries I have that do work in SQL Exporter would also yield the error of "@CurrentDB" not being declared. Anywho, due to time I think I'll have to give up on this with variables and the while loop and maybe just make a bunch of static queries. |
|
@burningalchemist FYI: the issue might be with doing INNER JOIN with other tables. Because I use a while loop and declare variables in other queries that work, but I don't do joins there. So maybe that's the issue, in case you encounter some similar issue in the future! |
|
Hey Jakob! Thanks for the follow up and the detailed investigation. 👍 Let me think what I can do to get access to some MSSQL server to check your hypothesis. If I get any results or explanation I'll mention you in this issue. What's your MSSQL version by the way? |
|
No worries :) The version is SQL Server 14.0.2037.2 Additionally on INNER JOIN. I noticed that INNER JOIN is used in the example config so it can't be that SQL Exporter can't deal with it. But I see that it only appears under the "queries" property (like the below one) and then is referenced under the "query_ref" property of a metric: So I gave it a try and placed my complex query under that property and then referenced it with "query_ref", and now, I can at least see that the query hits the database - that didn't happen before. So this might work after all, just need to fiddle a bit with scrape timeouts since I'm getting the "context deadline exceeded" errors doing this. EDIT: @burningalchemist So, this only worked once. Then I couldn't see the query anymore. But then I changed the query name and it worked again, but not after that. I tried a query without INNER JOIN and that worked giving results instantly. If adding an INNER JOIN I get nothing. Running the query in usql gives the result "IF 12", not sure what that means. |
|
@jakob-reesalu Thanks for sharing the query, I'll use it on my end to investigate. 👍 So if you run it several times it returns nothing? Any logs on the SQL Exporter side or MSSQL side? |
|
@burningalchemist You're welcome ;) So, me and a colleague looked at the issue and we changed some things, like declaring each variable in a separate DECLARE statement and changing temporary tables to variables instead. The query now looks like this: This gives the following result in usql now: So.... something with variable declarations it seems, but I don't get what really. I had a look at the driver for MSSQL https://github.com/microsoft/go-mssqldb but no clues there really. |
|
@jakob-reesalu I think the problem that you're using multiple statement query, which isn't supported. I'm still trying to get an access to MSSQL 2014 somewhere to try things out myself. :) |
|
@burningalchemist Hm... here's another query and it actually works. It checks databases for whether a cleanup has been performed and there are multiple statements: Even if it works uSQL gives these errors: |
|
@burningalchemist We finally nailed it, in quite an ugly way, but at least it's working! Some lessons: uSQL is a liar, queries that work in sql-exporter may render errors in uSQL. And to get the final query working in uSQL we had to put it in a stored procedure and execute that, but that's not necessary in sql-exporter where we can put the query directly in the collector's config. This is how it turned out in the end, I think maybe the big thing is that we put the But there is still some spooky "context deadline exceeded" errors. Sometimes the above query runs to completion and provides the metrics, and I can see that it's a query currently running on the db instance. Other times I see the query starting to run but immediately stops due to context deadline exceeded. Prometheus config: |
Hi! I got one collector working but got issues with the second one. It's something with the query, will be a long running one in the end, like 15-30mins (gonna run once a day). It's purpose is to scan all indexes on some tables for all dbs.
I started chopping the query up to find when it works and when it won't. At some occasions I got "context deadline exceeded" messages. I read up on some old threads saying that one might need to increase Prometheus and Sql Exporter scrape_timeout, but this didn't make a difference. Eventually I cut the query down bit by bit, and even if it takes less than 1 sec in Microsoft SQL Server Management Studio, no metrics would be provided - timing shouldn't be an issue then, right?
Finally got it working selecting indexes for only one specific DB, this would provide metrics in the UI:
This query provides results instantly when starting up. BUT, if un-commenting the last line, then suddenly I get no results. Both versions works instantly in MSSM.
SQL Exporter config:
Prometheus config:
Very grateful for any ideas on this!
P.S. I first ran version 0.5 but changed to 0.9 now (misunderstood which github page was the active one), and in the earlier version I could change log level with "-v 4" but using the new flag like "-log.level debug" won't work (wrote a comment here).. So can't get logging on this currently. Issue persists in new version anyhow.
The text was updated successfully, but these errors were encountered: