CODEWITHSUNDEEP
sqlazureazure-synapse
Gudwlk
Gudwlk

Reputation: 1157

Queries executed in Azure Synapse dedicated SQL Pool by User?

How to get the list of queries executed in Azure Synapse Dedicated SQL Pool by user?

Upvotes: 0

Views: 1168

Answers (1)

Utkarsh Pal
Utkarsh Pal

Reputation: 4544

You can get the query details by either using Azure Query Store service which automatically captures a history of queries, plans, and runtime statistics, and retains these for your review or you can create your custom table in your database to capture all the required details.

Only Synapse Dedicated pool supports Query Store but it isn't enabled by default for new Azure Synapse Analytics databases.

Use the ALTER DATABASE statement to enable the query store for a given database. For example:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

The following query returns information about queries and plans in the Query Store.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id;

On the other side, if you want to create a table to track the history of all the queries, you can refer the answer given by SqlWorldWide on this similar thread.

Upvotes: 1

Related Questions