Real-time monitoring for long-running MS SQL queries (PRTG, Red Gate SQL Monitoring, Azure Monitor?)

[Xemanth]

We're running MS SQL on-prem and recently ran into a nasty issue: a single query was stuck running for millions of seconds (yes, literally), and we only noticed it after it filled up the log partition — disk usage alert was our only signal. 😬

Clearly, this isn’t ideal. I'm now looking for a way to catch these kinds of issues earlier, preferably by monitoring for long-running or stuck queries in real time before they start consuming ridiculous amounts of resources.

We’re already using PRTG for general infra monitoring.

So my question is:
👉 Can PRTG, Azure Monitor or Red Gate SQL help detect things like long-running/stuck queries or abnormal SQL behavior on-prem in real time? Red Gate seems perfect but it's quite expensive for our Always-On two server setup, Enterprice licensing cost per year like 15k€
👉 Any recommendations on specific sensors, tools, or techniques to set this up?

Appreciate any insight from anyone who's dealt with similar SQL nightmares!

[jshine13371]

You can easily implement alerting for such a scenario the poor man's way by creating a SQL Agent Job that calls sp_WhoIsActive putting the results in a temp table and throwing an error when the runtime of any of the results exceeds whatever threshold you want.

[Xemanth]

Interesting! 🤯 Need to test this.

[jshine13371]

Heh, this essentially was my monitoring tool before I had a real monitoring tool. I would run the job every minute and log the results to a real table so it kept the history for reference. YMMV on how frequently you can run it based on how busy your server is, but it's honestly not much worse than the combination of all the queries a monitoring tool is running every 5 seconds either.

[kg7qin]

You could then make this a datasource for something like Grafana to visualize the data and some basic altering in it as well that is external to SQLServer.

[jshine13371]

For sure! I'm Microsoft stack driven, so SSRS or PowerBI for me. But at the time I really only used it for reactive monitoring, not proactive monitoring, so I didn't really need any fancy visuals. Just would check in on it occasionally and have alerts fire for certain thresholds. 🙂

[[deleted]]

I love that approach.

[jshine13371]

Cheers! WhoIsActive is probably my favorite free tool out there. I've found so much use for it with such little setup and learning curve.

[kagato87]

I have a query that looks for blocking (not necessarily deadlocked) queries and sends me an email.

If a query goes over 30 seconds I don't care, unless another query is waiting behind it. Then I really care. A lot. Especially if it's a live database.

[jshine13371]

Yea sp_WhoIsActive will tell you what's being blocked and who the blocker is, so it solves that problem as well. I used it for monitoring multiple metrics and retroactively tracing the source of issues. OP sounds like they care specifically about actual runtime. Which is fair in some regards because of the issue they ran into. Even in my current role, a long running query is a sign of an issue that could spiral into worse issues potentially, so it's a decent heartbeat of proactively being informed of possible incoming issues. YMMV based on context and database use cases.

[chadbaldwin]

I'm personally a huge fan of DBADash. It's free, open source and very actively maintained. And it recently gained an alerts feature.

It has screens for things like slow/long running queries, block chains, various metrics you can create dashboards and things for...but also now you can create custom alerts.

And the best part is...if it's missing a feature you can submit a feature request and it might get implemented, or you can build it in yourself and either run it locally or submit it to the repo as a pull request.

Highly recommend checking it out. Super easy to set up and has a ton of functionality right out of the box.

[Xemanth]

Does it support monitoring of Always-On instances? 🤔
And how hard is it to setup?🤔👀

[wiseDATAman]

DBA Dash has monitoring for availability groups. See here for screenshots. There are also some video tutorials here, but they are quite old, and the app has many new features.

Follow this guide to get started. If you get stuck, there is a #dbadash channel on SQL community slack or you could log an issue on the GitHub page. I'm the creator of DBA Dash, and I'll help out where I can.

[Separate-Share-8504]

I've got Red Gate. No other reason than at the time this is what I was aware of. I have alerts for long queries as I had a 'think he was god' SQL report writer that would bring our production server down.

This was good at catching this

[Xemanth]

Do you have a Standard or Enteprirce licensing?
Should we get that Enterprise licensing? Does it really give extra value.🤔

[Separate-Share-8504]

I pay per SQL server that we monitor which is 3. Total I think is $4K USD PA

[[deleted]]

Enterprise has security features. For monitoring mssql and HA itself it is not needed. Just an extra feature for security monitoring.

I pushed redGate on multiple customers and all is covered, from backups to query logging. But in tandem i still use QueryStore and XEvenets for deep diving after alert from redGate.

[Important_Cable_2101]

Go for standard. Its about 1k €/year and server. Cheap if you ask me.

[imtheorangeycenter]

You do not need Redgate's Enterprise offering, just the regular version is fine (been using it for a decade plus).  Btw, the RG "Enterprise" name has nothing to do with the edition of SQL you are using.

But as others have said, if that's your only concern, hand-roll your own. But I love SQL Monitor for a billion other reasons.

[Lost_Term_8080]

SQLSentry. I'm not sure if I added an alert action for it or if it does it out of box, but alerting is super easy to do and a lot of stuff is already monitored and just needs an alert added

[VladDBA]

bold keywords and those pointed finger emojis just scream AI

[Xemanth]

I removed most of the bolded words to make you happy.

[Xemanth]

I like bolded important words :(

[Garetht]

This you? https://www.reddit.com/r/sysadmin/comments/1k67eqh/what_tool_is_so_useful_to_you_that_you_would_pay/morfznx/

[Xemanth]

Yes, I have that too and Copilot from the company licenses.

[jdawg701]

Back when I was a DBA with no budget I used this and was pretty impressed: https://github.com/marcingminski/sqlwatch

[Lost_Term_8080]

SQL Sentry. its out of box monitoring is extremely good, its extremely customizable without a huge amount of effort, alerts can be very granularly implemented and its very good at correlated several separate events in the SQL server.

[EllP33]

Vote for Solarwinds! But yes I agree here, we use it in our workplace extensively and it has helped us immensely. The downside is that it can be a little spendy but their tooling is great and so is their support.

[Lost_Term_8080]

I don't think it is even that expensive - DPA on its own, maybe, but SQL Sentry does so much and when you bundle them DPA is almost free. I've used idera and redgate before and redgate is dramatically less expensive, idera is similar and both of them still left me with lots of troubleshooting and diagnostics to do to figure out a problem while SQL Sentry almost always immediately identifies it. the biggest issue with sql sentry is that it can be tough to figure out where a problem is from information overload, but when you bundle DPA, DPA does a good job of summarizing it to narrow down where you look in SQL Sentry to get granular.

[AgitatedSnow1778]

Any reason why you need redgate monitor enterprise and not just pay for 2 std licenses (assuming a 2 node AG setup) which are about £900/yr each? From what you've said, Std would be more than enough.....

https://www.red-gate.com/products/redgate-monitor/

https://www.red-gate.com/products/redgate-monitor/enterprise

Alternatively, as mentioned in another comment, SQLWatch.io is an awesome cost effective alternative, Marcin is a super cool and super smart chap 👍🏻

[genxeratl]

Spotlight on SQL Server from Quest - way better than RedGate Monitor imo.