How to find database usage for a specific application action

Question

A user is opening a page in our application which takes around 35 seconds. I want to see what is happening in the database during this time. What's the best way to do this, doing a trace? Thanks!

Answer 1

• Best scenario is only do this test and not let anything else run on the server during the test.
• If not isolate one application server for this purpose so you can filter with host name or login (whatever is easier in your case).
• Set up an extended event trace with the events you want to capture, include necessary Global Fields.
• Set up data storage type event_file so you can review the result later and even load into a SQL Server database table.
• Since you are capturing the trace under one minute I would not bother to roll files.
• If you are not familiar with Extended Event trace search for 'getting started with extended events' and you will see a large amount of useful resources.