Blog Post

Azure SQL Analytics

,

Azure SQL Analytics is currently in preview mode, still it is very impressive. The goal of this feature is to visualize important SQL performance metrics for your Azure SQL Database. There are a couple of things you need to do first.

  • Setup a Log Analytics workspace.
  • Enable diagnostics for your SQL Databases and/or elastic pools.

Please see the prerequisites section within this document – YOU MUST do this else you will not be able to use this feature. https://docs.microsoft.com/en-us/azure/log-analytics/log-analytics-azure-sql#prerequisites

Once setup it should take approximately 15 minutes to start capturing and rendering back some data. Don’t be surprised if it does take a little longer as was the case for myself.

analytics

Let’s start by clicking the Resources by type section (we will come to the Insights section later). So straightaway you will get to see the databases that you have setup for analytics with high level metrics but also query hashes. Notice no plan hash data, only query hash.

queryHash

You can drill into a Query Hash to get the code behind it and a trending chart on query duration, CPU and Data IO.

querydetails

Unfortunately, there is no option to extract the execution plan – maybe in the next release? Who knows.

Database Wait Stats

Now the fun part. Database wait statistics which I would love to have seen within Query Performance Insight but at least we have access to it now and it really does complete the picture for analysis.

dbwaits

Now this is where it gets powerful. You can drill down on a wait type.

databasewaits

You can open this via Advanced Analytics (button top right) and have the ability to develop some queries.

Such as:

AzureDiagnostics
| where Category=="DatabaseWaitStatistics"
| where ResourceId contains '/SUBSCRIPTIONS/2BEED8SDS22551-A8D2220-422863-922777-AAAECASA745098F3ZB7/RESOURCEGROUPS/RG_AKS/PROVIDERS/MICROSOFT.SQL/SERVERS/SQL01/DATABASES/SQLDB1'
| summarize sum(delta_wait_time_ms_d) by bin(start_utc_date_t, 15m), wait_type_s
| render barchart

This gives:

waitscharts

Hover over a wait type to see how prevalent it has been. (SOS_SCHEDULER_YIELD)

SOS

Log Analytics

I tried writing some code. Looking for errors against a specific database and giving me a pie chart. By the way this is called Azure Log Analytics queries, for more information check this link out https://docs.loganalytics.io/docs/Learn/Getting-Started/Getting-started-with-queries

AzureDiagnostics
| where Category=="Errors"
| where ResourceId contains '/SUBSCRIPTIONS/BZSSBEfd1-Ad8Dsd0-4F863-977SSDF7-ESC74509DZS8F3B7/RESOURCEGROUPS/RG_AKS/PROVIDERS/MICROSOFT.SQL/SERVERS/SQL01/DATABASES/SQLDB1
| summarize count() by Message, bin(TimeGenerated, time(5m))
| order by TimeGenerated desc
| render piechart

PIECHARTS

Looks like someone forgot to realise that they were working against an Azure SQL Database! We cannot use the USE COMMAND in Azure.

Insights

Going back to the original screen and you will see Insights in green, scroll across and you will see that they have found something useful – more specifically around query duration increase.

durationinc

The complete message presented is:

detections

The execution plan has changed and the new plan is causing poor performance. Consider forcing the old plan. Look for query hash [0xDDF80BB171EAECBF] that covers following QueryID(34) has good Plan ID(14098) but it was executed using bad Plan ID(14186). A resource usage increase has been detected. Query causing the increase has query hash 0xDDF80BB171EAECBF.

The query hash is shown next to the information.

Unfortunately, that is all I can get – this feature is in preview mode remember! Highlighting the query hash does not give me further details. Lucky for me I have the Query Store to find this query ID.

qstor

Forcing is now in action!

As you can see this is a powerful feature, don’t get me wrong it is not perfect yet but after a conversation with the program manager for this feature I know this will become even better.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating