Hi everyone.
I want to monitor SQL Server live such as Activity monitor. I want to create an application but I don't know how to live monitoring events such as waits, blockings , deadlocks, and so on.
In monitoring systems such as redgate, is it running query to get these data every seconds or has a different solutions for live monitoring?
Sorry for my bad English.
January 23, 2022 at 7:43 pm
They will run queries against the instance every X s/m/h as per the configuration.
However I wouldn’t be writing my own monitoring solution, if I was financially constrained then I would look at SQLWatch.io otherwise I’d be going with Quest/RedGate/Idera/SentryOne depending which does which right for the company.
The amount of change to the DMVs and keeping your custom monitoring updated is a royal pain in the ass. Let someone else worry about all that, there are bigger fish to fry.
January 24, 2022 at 2:39 am
This was removed by the editor as SPAM
Up front, I work for Redgate.
We, every monitoring tool, use the exact same set of tools you can use. Dynamic Management Views, Extended Events, Query Store, DMI queries, PerfMon, all the same tools. The knowledge of how to query all these things is also really well established. So, if you have simply tons of spare time, you too can build a monitoring system from scratch.
Or
Purchase one. It's cheaper, takes less time, gives you more functionality than you're likely to give yourself, updated regularly, all sorts of benefits. Then, as needed, either customize the tool with your own unique monitoring needs, or add some XEvents monitoring to enhance whatever tool you land on.
I strongly recommend the second choice, and would even if I didn't work for a vendor.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 24, 2022 at 4:10 pm
Most vendors will give you a free trial period, so you can see which you like best.
At least one of them I remember giving a 2nd block of free time if needed to evaluate.
January 24, 2022 at 11:23 pm
This was removed by the editor as SPAM
January 25, 2022 at 5:12 am
Thank you a lot for your information.
January 27, 2022 at 10:30 pm
I wrote my own monitoring solution that has recently gone open source. It might be worth a look (I'm biased, but it's free and open source):
https://github.com/trimble-oss/dba-dash
I would also take a look at query store which is built in and free. Query store isn't just useful for diagnosing performance issues - it's very useful for fixing common performance issues like parameter sniffing issues. Query store is limited in scope though - DBA Dash will compliment query store and give you a lot more data to help with performance issues. For example, waits and blocking are covered. Deadlocks are also tracked with a performance counter - though not currently captured by the tool.
This video will give you an overview of the tool. I'm not skilled at video presentations but it should give you an idea what the tool is about:
https://www.youtube.com/watch?v=X7e4zElOQ3c&t=8s
Other community tools like sp_WhoIsActive,sp_BlitzWho, sp_BlitzFirst & sp_QuickieStore are also worth a look. Or there are lots of commercial options available depending on your requirements and budget.
DBA Dash - Free, open source monitoring for SQL Server
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply