Find SQL query from Azure Analytics Log error message

Question

We have Azure SQL Analytics enabled against a Managed Instance Database.

The "Errors/Blocking/Timeouts/Deadlocks" chart presents a view of Counts by Time, and then a listing of issues underneath e.g.

If you click on an entry, your are presented with a detail view e.g.

The message provides no other context e.g. the table being queried. I'm unable to trace this error back to the actual query which raised this error. There's no query hash given to allow it to be found in the query plan cache. Since it is such a generic error, it's of very little value when trying to find the errant code.

Does anyone have the Kusto-fu to provide a query that I can run against the analytics logs to get more context, or a T-SQL query to find queries run at a given date and time?

EDIT: I've found the raw log entry, but all query hash values are -1

Answer 1

There is a few ways that you can possibly track this down. First i would start with this query running in the master database for the server:

SELECT * FROM sys.event_log WHERE severity=2

This will show you errors, and hopefully yours shows up there. After getting an idea of what database is having this error, assuming you didnt already know, you can then go to that database's query store and search for statements run at the same time as your error. Query store can be found in the object explorer in SSMS but that only gives you the out of the box options. Here is a query you can play around with to assist in your rearch - note you will need to change the where clause in the bottom portion.

IF OBJECT_ID('tempdb..#compiledValue') IS NOT NULL
    DROP TABLE #compiledValue;

SELECT q.object_id
    ,pp.name
    ,p.query_plan AS QryPlan
    ,CAST(p.query_plan AS XML) query_plan
    ,q.last_execution_time
    ,t.query_sql_text
    ,TRY_CONVERT(XML, SUBSTRING(p.query_plan, CHARINDEX('<ParameterList>', p.query_plan), CHARINDEX('</ParameterList>', p.query_plan) + LEN('</ParameterList>') - CHARINDEX('<ParameterList>', p.query_plan))) AS Parameters
INTO #compiledvalue
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t ON q.last_compile_batch_sql_handle = t.statement_sql_handle
INNER JOIN sys.procedures pp ON q.object_id = pp.object_id;

SELECT object_id
    ,name
    ,query_plan
    ,last_execution_time
    ,query_sql_text
    ,pc.compiled.value('@Column', 'nvarchar(128)') AS Parameterlist
    ,pc.compiled.value('@ParameterCompiledValue', 'nvarchar(128)') AS [compiled Value]
FROM #compiledValue cvalue
OUTER APPLY cvalue.parameters.nodes('//ParameterList/ColumnReference') AS pc(compiled)
WHERE pc.compiled.value('@Column', 'nvarchar(128)') IS NOT NULL
    AND pc.compiled.value('@ParameterCompiledValue', 'nvarchar(128)') <> 'NULL'
    --QryPlan LIKE '%Table Scan%'

This isnt meant to be a full answer, but too long to be a comment.