Getting SQL query performance into log analytics

%3CLINGO-SUB%20id%3D%22lingo-sub-1271621%22%20slang%3D%22en-US%22%3EGetting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271621%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20get%20query%20performance%20from%20SQL%20Server%20(On%20an%20Azure%20VM)%20into%20Log%20analytics%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20like%20to%20be%20able%20to%20see%20top%2010%20longest%20running%2C%20most%20logical%20reads%20etc.%3C%2FP%3E%3CP%3ECan't%20find%20a%20hint%20on%20how%20to%20be%20able%20to%20do%20that.%20Any%20ideas%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1271621%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1274026%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1274026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577472%22%20target%3D%22_blank%22%3E%40PatrikHansson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20you%20tried%20adding%20some%20SQL%20Perf%20counters%3F%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-log-analytics%2Fsql-cluster-performance-counters%2Fm-p%2F882432%23M2017%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-log-analytics%2Fsql-cluster-performance-counters%2Fm-p%2F882432%23M2017%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1274067%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1274067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYea%2C%20but%20I'm%20not%20sure%20how%20perf%20counters%20are%20going%20to%20be%20able%20to%20give%20me%20a%20list%20of%20which%20queries%20are%20using%20most%20cpu.%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20coming%20from%20the%20SCOM%20side%20of%20monitoring%20an%20there%20i%20would%20probably%20been%20able%20to%20write%20my%20own%20ManagementPack%20to%20run%20on%20the%20agent%20on%20the%20sql%20server%20and%20get%20the%20data%20from%20T-SQL%20but%20with%20azure%20monitor%2FLog%20analytics%20I'm%20at%20a%20loss%20on%20how%20to%20do%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1274089%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1274089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577472%22%20target%3D%22_blank%22%3E%40PatrikHansson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThese%20may%20give%20you%20some%20ideas%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Finsights%2Fsql-assessment%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Finsights%2Fsql-assessment%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3Eand%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fargonsys.com%2Fmicrosoft-cloud%2Flibrary%2Fazure-log-analytics-oms-agent-now-collects-sql-server-audit-logs%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fargonsys.com%2Fmicrosoft-cloud%2Flibrary%2Fazure-log-analytics-oms-agent-now-collects-sql-server-audit-logs%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1274315%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1274315%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20thanks%20for%20taking%20the%20time%20to%20answer.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'v%20activated%20the%20SQL%20health%20check%20but%20it%20doesn't%20seem%20to%20collect%20what%20i'm%20looking%20for.%3C%2FP%3E%3CP%3EAnd%20the%20auditing%20is%20for%20security%20related%20if%20i%20remember%20correctly.%20And%20for%20that%20i%20would%20have%20to%20collect%20the%20whole%20Application%20event%20log.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20im%20still%20hunting%20for%20a%20solution%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584322%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577472%22%20target%3D%22_blank%22%3E%40PatrikHansson%3C%2FA%3E%26nbsp%3BWere%20you%20able%20to%20find%20a%20way%20to%20get%20this%20to%20work%3F%20I'm%20facing%20the%20same%20challenge%20right%20now%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1592143%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1592143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757184%22%20target%3D%22_blank%22%3E%40boaz_rosen_adb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20no%20i%20didn't%20get%20anywere.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20there%20is%20a%20private%20preview%20for%20azure%20monitor%20targeting%20server%20workloads%20but%20I%20haven't%20applied%20since%20iv%20ben%20on%20vacation%20and%20don't%20have%20the%20time%20to%20make%20a%20participation%20meaningful%20atm.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftwitter.com%2FAzureMonitor%2Fstatus%2F1281250644481806336%3Fs%3D20%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftwitter.com%2FAzureMonitor%2Fstatus%2F1281250644481806336%3Fs%3D20%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1592741%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1592741%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%20%2C%20im%20not%20entire%20sure%20what%20is%20the%20performance%20counter%20you%20need%2C%20but%20you%20can%20try%20something%20like%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ego%20to%20the%20log%20analytics%2C%20-%26gt%3B%20Advance%26nbsp%3B%20(In%20the%20settings%20like%20image%20below)%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22loadedlouie27_2-1597665566836.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212730i097443B8ECE505FB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22loadedlouie27_2-1597665566836.png%22%20alt%3D%22loadedlouie27_2-1597665566836.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethen%20choose%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22loadedlouie27_0-1597665400026.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212728iA1D79992D1A0A464%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22loadedlouie27_0-1597665400026.png%22%20alt%3D%22loadedlouie27_0-1597665400026.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThen%20I%20think%20you%20just%20need%20to%20add%20something%20like%20this%26nbsp%3B%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22loadedlouie27_1-1597665438093.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212729iFE07D43AB226C4A9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22loadedlouie27_1-1597665438093.png%22%20alt%3D%22loadedlouie27_1-1597665438093.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EYou%20can%20also%20add%20the%20performance%20counter%20of%20your%20choice.%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20check%20what%20is%20the%20perfomance%20counter%26nbsp%3B%20you%20need.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1592800%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1592800%22%20slang%3D%22en-US%22%3E%3CP%3EA%20least%20for%20me%20there%20isn't%20a%20perf%20counter%20for%20what%20I%20wanted.%20It%20needs%20to%20run%20a%20query%20inside%20sql%20and%20send%20the%20output%20to%20log%20analytics.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612277%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577472%22%20target%3D%22_blank%22%3E%40PatrikHansson%3C%2FA%3E%26nbsp%3BI%20am%20working%20on%20the%20SQL%20monitoring%20preview%20you%20saw%20in%20the%20tweet.%26nbsp%3B%20We%20are%20calling%20DMVs%20to%20get%20the%20sql%20monitoring%20data.%26nbsp%3B%20Currently%20we%20don't%20return%20a%20lot%20of%20details%20on%20query%20perf%20stats%20in%20the%20preview%20version.%26nbsp%3B%20Is%20there%20a%20DMV%20that%20you%20would%20like%20to%20use%20for%20this%3F%26nbsp%3B%20For%20example%2C%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-dynamic-management-views%2Fsys-dm-exec-query-stats-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Esys.dm_exec_query_stats%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612753%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20SQL%20query%20performance%20into%20log%20analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612753%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770977%22%20target%3D%22_blank%22%3E%40ScottK206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20a%20SQL%20guru%20but%20would%20like%20to%20get%20information%20on%20queries%20not%20behaving%20nice.%20It's%20been%20a%20while%20since%20I%20looked%20at%20this%20so%20I%20don't%20remember%20exactly%20what%20queries%20I%20was%20using%20in%20smss%20to%20get%20the%20info%20I%20wanted%2C%20and%20since%20i%20couldn't%20get%20it%20up%20on%20the%20dashboard%20at%20that%20time%20I%20stopped%20exploring.%3C%2FP%3E%3CP%3EBut%20it%20was%20something%20like%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.sqlserver-dba.com%2F2016%2F10%2Fhow-to-find-longest-running-queries-in-sql-server.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sqlserver-dba.com%2F2016%2F10%2Fhow-to-find-longest-running-queries-in-sql-server.html%3C%2FA%3E%26nbsp%3Bso%20I%20guess%20it%20was%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-dynamic-management-views%2Fsys-dm-exec-query-stats-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Esys.dm_exec_query_stats%3C%2FA%3E%3CSPAN%3E.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi

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 ?

10 Replies

@Clive Watson 

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. 

@Clive Watson 

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 :)

@PatrikHansson Were you able to find a way to get this to work? I'm facing the same challenge right now

@boaz_rosen_adb 

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. 

 

https://twitter.com/AzureMonitor/status/1281250644481806336?s=20

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) :

loadedlouie27_2-1597665566836.png

then choose : 

 

loadedlouie27_0-1597665400026.png

Then I think you just need to add something like this  :

loadedlouie27_1-1597665438093.png

You can also add the performance counter of your choice. 

You need to check what is the perfomance counter  you need. 

 

Hope this helps. 

 

 

 

 

 

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. 

@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.

@ScottK206 

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.