Apr 01 2020
- last edited on
Apr 08 2022
Is it possible to get query performance from SQL Server (On an Azure VM) into Log analytics ?
Would like to be able to see top 10 longest running, most logical reads etc.
Can't find a hint on how to be able to do that. Any ideas ?
Apr 02 2020 12:31 AM
Have you tried adding some SQL Perf counters?
Apr 02 2020 12:48 AM
Yea, but I'm not sure how perf counters are going to be able to give me a list of which queries are using most cpu.
I'm coming from the SCOM side of monitoring an there i would probably been able to write my own ManagementPack to run on the agent on the sql server and get the data from T-SQL but with azure monitor/Log analytics I'm at a loss on how to do it.
Apr 02 2020 12:57 AM
Apr 02 2020 02:18 AM
Hi, thanks for taking the time to answer.
I'v activated the SQL health check but it doesn't seem to collect what i'm looking for.
And the auditing is for security related if i remember correctly. And for that i would have to collect the whole Application event log.
So im still hunting for a solution :)
Aug 12 2020 06:16 AM
@PatrikHansson Were you able to find a way to get this to work? I'm facing the same challenge right now
Aug 16 2020 11:01 PM
Hi, no i didn't get anywere.
I know there is a private preview for azure monitor targeting server workloads but I haven't applied since iv ben on vacation and don't have the time to make a participation meaningful atm.
Aug 17 2020 05:00 AM
Hi all , im not entire sure what is the performance counter you need, but you can try something like this.
go to the log analytics, -> Advance (In the settings like image below) :
then choose :
Then I think you just need to add something like this :
You can also add the performance counter of your choice.
You need to check what is the perfomance counter you need.
Hope this helps.
Aug 17 2020 05:25 AM
A least for me there isn't a perf counter for what I wanted. It needs to run a query inside sql and send the output to log analytics.
Aug 25 2020 03:02 PM
@PatrikHansson I am working on the SQL monitoring preview you saw in the tweet. We are calling DMVs to get the sql monitoring data. Currently we don't return a lot of details on query perf stats in the preview version. Is there a DMV that you would like to use for this? For example, sys.dm_exec_query_stats.
Aug 25 2020 10:49 PM
I'm not a SQL guru but would like to get information on queries not behaving nice. It's been a while since I looked at this so I don't remember exactly what queries I was using in smss to get the info I wanted, and since i couldn't get it up on the dashboard at that time I stopped exploring.
But it was something like https://www.sqlserver-dba.com/2016/10/how-to-find-longest-running-queries-in-sql-server.html so I guess it was from sys.dm_exec_query_stats.