June 25, 2021 at 10:40 am
Hi,
What is the best recommended solution to monitor Azure managed Instance metrics. Looking to get performance stats for items like some of the below.
Buffer Manager: BufferCache Hit-Ratio
Buffer Manager: Page Life Expectancy
General Statistics: Logical Connections
General Statistics: User Connections
We use currently use nagios to monitor and graph from sql counters so that I can review these metrics over days/weeks/months etc for our onsite sql instance and pin point any issues. It's proved very useful over the years to pin point issue.
There's a couple of options listed in the article below, does anyone have any experience of them and what one might be best to utilize?
Thanks
June 25, 2021 at 5:20 pm
I think it depends on your requirements. I don't have any Azure instances to monitor, but I like SQL Monitor from RedGate. The price is right and the tool works really well.
I would recommend reviewing some of the tools they list and getting a demo of them. Then you can find the one that suits your needs. Recommendations from people on the forum are likely going to be biased based on either who they work for or the tool they are currently using. My bias is towards RedGate as the tool works well AND is reasonably priced.
It also depends on your budget. Some tools are more expensive than others. They do say you get what you pay for. But that is why I'd demo a few tools, price them out, and compare what tool will work best in your environment.
If the 4 metrics you listed are all you need, you may be able to get by using DMV's and scheduled tasks to pull the data into a table and build your own monitoring solution. That being said, I would still recommend buying a vendor supported tool as that way you don't need to reinvent the wheel AND you get a lot more metrics being captured.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 28, 2021 at 11:43 am
Thanks, I've had a quick look at the redgate solution and it's looks good. I'll do a more comprehensive test of it and see how it performs in terms of our requirements.
Just as a matter of interest is running the webserver and base monitoring software for redgate heavy on the host machine?
June 28, 2021 at 12:25 pm
Microsoft has a bunch of monitoring built in to the Azure portal. It's not the easiest to use, and seeing all your instances at once is a real pain. However, you can use it to get the job done.
I want to mention all that before I say, yeah, I'd probably get a 3rd party tool for this. SQL Monitor does a great job (disclaimer, I work for Redgate). Best of all, it can monitor your Azure Managed Instance, any Azure SQL Databases, and your Azure VMs, on-premises instances, AWS RDS databases and any other VM running anywhere else. All in a single pane of glass so you can see all your servers and instances at once, in the same way.
The load on the systems is minimal. We don't use an agent, and instead use a combination of WMI, DMV, & Extended Event queries. A little Powershell & other Azure CLI stuff is thrown in there too. Any monitoring product adds some load. Ours adds as little as we can. We're constantly tuning it to make it better in this regard too.
"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
July 26, 2021 at 12:02 pm
Hi,
I'm just in the process of trying out sql monitor for managed instance. Its a really good tool.
However, have one issue/question. I can see the page life expectancy graph and other metrics from the 'overviews' page for the MI. It's gives me the metric for the past two hours from what I can see but when I go into the Analysis Graph page I can't seem to select the Page life expectancy for the MI. I'd really like to see the page life expectancy over a longer period of time. Is this possible?
July 26, 2021 at 1:23 pm
I don't have a test instance for Managed Instance to look at for you. Maybe open a ticket with support. I'd think you'd be able to see more data on this, but I'm not sure.
"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
July 26, 2021 at 1:29 pm
Will do thanks for getting back so quickly
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply