Diagnostics on Azure SQL Server & Storage

Question

Hello All,

We have enabled diagnostic settings on Azure SQL server.
We need to identify the queries executed in time between a start time and end time and who all executed those queries. how should I do that ?
Also, we have enabled diagnsotic in azure blob on another server. so how to identify the same aspects w.r.t blob storage logs

Thnaks for the help in advance !

Naimish Makwana · Answer

Sure, I can help with that. For Azure SQL Server: To identify the queries executed in a specific time range, you can use the Query Store feature in Azure SQL Server. Here’s a query that can be useful to see the most executed queries on your Azure SQL Server database12: SELECT TOP 10 execution_count, statement_text FROM ( SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset) /2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST ) AS query_stats ORDER BY execution_count DESC To identify who executed those queries, you can use the following query3: SELECT sdes.session_id, sdes.login_name, sdest.Query FROM sys.dm_exec_sessions AS sdes INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id CROSS APPLY ( SELECT db_name(dbid) AS DatabaseName, object_id(objectid) AS ObjName, ISNULL((SELECT TEXT AS [processing-instruction(definition)] FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) FOR XML PATH(''), TYPE), '') AS Query FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) ) sdest WHERE sdes.session_id @@SPID ORDER BY sdec.session_id For Azure Blob Storage: To identify the queries executed in a specific time range, you need to list all blobs in the container and then apply filtering on the client side4. An alternative would be to import this information into an Azure Cognitive Search Index4. To identify who accessed Azure Blob Storage, you can use the UserPrincipalName column to identify the user and parse the column with JSON AuthorizationDetail.action to identify the action of the user on storage5. You can also find this information in Storage Analytics Logs6. Please note that these are general guidelines and you might need to adjust the queries based on your specific requirements. Thanks

Diagnostics on Azure SQL Server & Storage

Answers (1)