How to get database name from Synapse serverless SQL pool query history

Foster, Gwendolyn 0

I have used the DMVs to get the Synapse serverless SQL pool query history and Synapse Studio. I do see in the query text listing that shows the from statement with the name of the view <schema>.<view name> but I do not see the data source (i.e. database) identified. Where would I find that information captured?

Foster, Gwendolyn 0 Reputation points

2024-12-17T07:31:21.5433333+00:00

Thank you for the information that was very helpful. Looking in the Log Analytics SynapseBuiltInSQLPoolRequestsEnded, I see the querytext and the Select statement but the information has the <schema>.<view name>detail but still no database detail.

In the Synapse Studio Monitor Hub SQL Pools Built-in it shows request history in a graph but no drilldown into the details.
phemanth 12,895 Reputation points Microsoft Vendor

2024-12-17T16:43:00.8633333+00:00

@Foster, Gwendolyn

Thanks for your information.

Here are a few additional steps

You can set up Extended Events to capture detailed information about query execution, including the database context. This can be configured to log specific events and their associated data.

If possible, you can implement custom logging within your queries or procedures to explicitly log the database name along with other query details. This can be done by including a logging mechanism that writes to a central log table or external logging service.

Ensure that your Synapse workspace is configured to capture and log detailed query execution data. Sometimes, additional configuration might be needed to capture all the necessary details.

I hope these steps help you resolve the issue. Please Let me know if issue persists.
phemanth 12,895 Reputation points Microsoft Vendor

2024-12-18T16:31:52.8366667+00:00

@Foster, Gwendolyn We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Foster, Gwendolyn 0 Reputation points

2024-12-19T08:54:22.4833333+00:00

Phemanth, sorry I missed your comments. I will look for the steps to configure the Synapse workspace to capture and log detailed query execution data. Will update you on my steps to do that. Thanks
phemanth 12,895 Reputation points Microsoft Vendor

2024-12-20T19:37:30.7533333+00:00
@Foster, Gwendolyn

Yes, it is fair to say that the database context needs to be captured at the time of query execution to be available in views like sys.dm_exec_requests_history and SynapseBuiltinSqlPoolRequestsEnded.

Regarding Extended Events in Synapse Workspace, it appears that Synapse does not natively support Extended Events in the same way as SQL Server or Azure SQL Database This means you might need to rely on other methods for capturing detailed query execution data. For monitoring and capturing fields in Synapse Studio, the Monitor Hub provides some customization options, but it might not offer the granularity you need. Here are a few alternative approaches:

Custom Logging: Implement custom logging within your queries or procedures to explicitly log the database name along with other query details. This can be done by including a logging mechanism that writes to a central log table or an external logging service.

Azure Log Analytics: Ensure your Synapse workspace is integrated with Azure Log Analytics. You can query the SynapseBuiltinSqlPoolRequestsEnded table to get detailed logs. While it might not show the database name directly, you can correlate other details to infer the database context

Query Store: If enabled, the Query Store can provide insights into query performance and history. This can help you track queries and their associated databases

Unfortunately, there isn't a specific window or interface in Synapse Studio to add or configure fields being captured from SQL requests directly. You might need to use a combination of the above methods to achieve the desired level of detail.
phemanth 12,895 Reputation points Microsoft Vendor

2024-12-23T18:27:16.9533333+00:00

@Foster, Gwendolyn

Yes, setting up auditing as outlined in the Auditing for Azure SQL Database and Azure Synapse Analytics documentation can help you capture more detailed information about your queries. Here are the key steps:

Enable Auditing: You can enable auditing at the server or database level. This will log events and write them to an audit log.

Configure Audit Settings: Specify the storage account where the audit logs will be stored. You can also configure the retention period for the logs.

Audit Actions and Groups: Define which actions and groups you want to audit. This can include successful and failed logins, data access, and changes to database schema.

Review Audit Logs: Once auditing is enabled and configured, you can review the logs to see detailed information about the queries, including the database context.

1 answer

phemanth 12,895 Reputation points Microsoft Vendor

2024-12-16T19:28:59.4833333+00:00
@Foster, Gwendolyn

Thanks for using MS Q&A platform and posting your query.

To identify the database name from the Synapse serverless SQL pool query history, you can use the following methods:

Dynamic Management Views (DMVs): While DMVs provide extensive details about query execution, they might not directly show the database name. However, you can use the sys.dm_exec_requests and sys.dm_exec_query_stats DMVs to gather more context about the queries.

Query Store: If enabled, the Query Store can provide insights into query performance and history. You can enable it using the following command:
ALTER DATABASE <database_name> SET QUERY_STORE = ON;
This will help you track queries and their associated databases.

Azure Log Analytics: By integrating Synapse with Azure Log Analytics, you can query the SynapseBuiltinSqlPoolRequestsEnded table to get detailed logs, including the database context.

Synapse Studio Monitor Hub: The Monitor Hub in Synapse Studio provides a visual interface to monitor SQL requests. You can customize the columns to include more details about the queries, which might help in identifying the database.

I hope these steps help you resolve the issue. Please Let me know if issue persists.
Please sign in to rate this answer.
Foster, Gwendolyn 0 Reputation points

2024-12-19T16:54:54.1033333+00:00

Hi, Would it be a fair statement that the field data (i.e. database) needs to be captured prior to it being in sys.dm_exec_requests_history and the like and SynapseBuiltinSqlPoolRequestsEnded. I haven't seen any place to add Extended Events in the Synapse Workspace. On the Monitor, I haven't seen any place where you see the fields being captured or to add/configure fields being captured from the SQL requests. Do you have a window where this can take place?

Foster, Gwendolyn 0 Reputation points

2024-12-23T13:07:03.56+00:00

I am checking through 'Auditing for Azure SQL Database and Azure Synapse Analytics'. I think maybe what is outlined in this link is where I could set the auditing to capture more information. https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-setup?view=azuresql

Foster, Gwendolyn 0 Reputation points

2025-01-09T14:40:31.1233333+00:00

Apologies for not getting back to you sooner. Your assistance helped me create the Azure SQL Auditing logs which gives me the database name that I was looking for and work on exporting the logs to a storage account. I have been able to create a Data Explorer Cluster where I can see some of the data. I am still working out the connection and data updates in the data flow between Audit logs > Storage Account > Data Explorer. I may also try an Event Hub to see that offers better access to the data. Thanks again.

phemanth 12,895 Reputation points Microsoft Vendor

2025-01-10T05:20:06.2233333+00:00

@Foster, Gwendolyn I'm glad to hear that the Azure SQL Auditing logs are helping you capture the database name and that you've made progress with your Data Explorer Cluster. It sounds like you're on the right track with setting up the data flow between the Audit logs, Storage Account, and Data Explorer.

Trying out an Event Hub could indeed provide better access to the data and streamline the process.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to get database name from Synapse serverless SQL pool query history

1 answer

Your answer