SQL Server : which queries are executed?

Question

I have an client application which shows me user info, products and prices etc from SQL Server via buttons on localhost.

For example, when I click on show expenses, it gets some information.

I want to learn which button executes which query?

Is there any way the monitor SQL statements which are executed by the app?

I tried

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

but this code does not show SQL statements from app.

Thanks.

Answer 1

Generally when you are using Sql Server Profiler, you get results for other databases as well when running the trace - If the same database server has other databases hosted which are used by other applications. You would want to track things in your database which your application is using. It may not be case for you If you have only one database in your database server - Just mentioning If this is the case. I have found this approach really useful.

I would set up a template with your database as filter and use that template to create the trace.

This is how you you create a template - http://technet.microsoft.com/en-us/library/ms175070.aspx

This is how you filter to see the only the queries executed against your database :-

Under Trace template properties > Events Selection tab > select show all columns. Now under column filters, you should see the database name. Enter the database name for the Like section and you should see traces only for that database.

You can see the Sql statements executed by the application. I had the profiler running upfront. See an example below.

User enters name as "ashish1" in the below windows form :-

The code which gets executed when user hits the button :-

If you have the profiler running, you can see the Sql statement which was sent by the application:-