how to find recently executed queries in a specific database by a specific application?

Question

how to find recently executed queries in a specific database by a specific application?

Chaitanya Kiran 801

Ronen Ariely 15,206 Reputation points

2022-01-12T14:35:31.243+00:00

Hi,

how to find recently executed queries in a specific database by a specific application?

There is not solution which you can count on if you are looking for "any query" unless you monitor the queries in advance (using Extended Event monitor the captures the SQL:StmtCompleted event for example).

For example using sys.dm_exec_query_stats returns aggregate statistics for cached query plans in SQL Server., but if the cached was cleaned then you lost the information, so you cannot count on this. Moreover, it does not provide the level fo information which you are looking for.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-01-12T23:01:01.4+00:00

(using Extended Event monitor the captures the SQL:StmtCompleted event for example).

Caught you there! SQL:StmtCompleted is a Trace event!

But, yes, that is the way to do. Run a Trace or Extended Event session, filtering for the application name (and the application sets a good name to filter on.)

I would not capture StmtCompleted, though, but let it suffice with RPC:Completed and SQL:BatchCompleted to reduce the overhead.
AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2022-01-17T09:29:16.19+00:00

Hi ChaitanyaKiran-2787,

How are things going? Do the answers help you?
Please feel free to let us know if you have any other question.
If the post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

2 answers

Your answer

Ronen Ariely 15,206 Reputation points

2022-01-12T14:35:31.243+00:00

Hi,

how to find recently executed queries in a specific database by a specific application?

There is not solution which you can count on if you are looking for "any query" unless you monitor the queries in advance (using Extended Event monitor the captures the SQL:StmtCompleted event for example).

For example using sys.dm_exec_query_stats returns aggregate statistics for cached query plans in SQL Server., but if the cached was cleaned then you lost the information, so you cannot count on this. Moreover, it does not provide the level fo information which you are looking for.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-01-12T23:01:01.4+00:00

(using Extended Event monitor the captures the SQL:StmtCompleted event for example).

Caught you there! SQL:StmtCompleted is a Trace event!

But, yes, that is the way to do. Run a Trace or Extended Event session, filtering for the application name (and the application sets a good name to filter on.)

I would not capture StmtCompleted, though, but let it suffice with RPC:Completed and SQL:BatchCompleted to reduce the overhead.
AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2022-01-17T09:29:16.19+00:00

Hi ChaitanyaKiran-2787,

How are things going? Do the answers help you?
Please feel free to let us know if you have any other question.
If the post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

Answer 1

Olaf Helper 47,436

SQL Server do have some execution statiscs, see sys.dm_exec_query_stats (Transact-SQL)
But not on the level you are looking for, for this you have to implement your own audit/logging.

Answer 2

Hi ChaitanyaKiran-2787,
Agree with others. You can select three events RPC:Completed, SP:StmtCompleted and SQL:BatchCompleted in the SQL Server Profiler, and make sure column ApplicationName, DatabaseName and textData is selected.

And then click on the "Column Filters..." button to filter the specific database and application name.

Or you can use Extended event, which is similar with SQL Server profiler. In the new session wizard, select rpc_completed,sql_batch_complete and sql_batch_starting events, and make sure client_app_name, database_name and sql_text is clicked in “capture Global Fields” page, then filter the database and application name in the “Set Session Event Filters” page.

Please refer to Use SQL Server Profiler to trace database calls from third party applications and Quickstart: Extended events in SQL Server which might be helpful.

Best Regards,
Amelia

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-01-13T22:13:31.977+00:00

Very important! While you can use Profiler to configure a trace for this purpose, do absolutely not run the trace from Profiler, but use File->Export-Scipt trace defintion and run the trace as a server-side trace.

Running the trace directly from Profiler can have severe impact on the performance on the server. A server-side trace can also come with a cost, if the application submits many short SQL batches.

And do not include StmtCompleted for this kind of monitoring. That can definitely add insult to injury.

Share via

how to find recently executed queries in a specific database by a specific application?

2 answers

Your answer