Monitoring Your Database with SQL Profiler
Let’s say that we have an application named SOCK that runs against our database SOCKsql, on the SwampTest server. We’d like to see what T-SQL queries hit the database when a user logs into SOCK. We’ll need to:
- Start SQL Server Profiler and set up the trace
- Run the trace while we perform the SOCK application login
- Stop the trace and examine the captured data
Start SQL Server ProfilerYou can start SQL Profiler from the Start menu, or from within SQL Server Management Studio (SSMS) under the Tools menu. (You can also use Start > Run > Profiler.) On startup, Profiler will present you with a Connect to Server dialog box; enter the name of the server you wish to trace - and your authentication details - just as you would for SSMS, and click Connect.
The Trace Properties window allows you to tailor the trace to your needs. On the General tab, you can enter a Trace name, choose a trace template, select save options, and enable a trace stop time. Today we’re only running a short trace that we don’t intend to save, so we’ll keep the default options.
On the Event Selection tab we choose what database events, and which attributes from each event we would like to capture. The events that are already selected are part of the “Standard” template that we saw on the General tab. (Note that checking and unchecking boxes on this screen will not affect the template.)
Near the bottom right of the screen, there are two checkboxes. “Show all events” displays all the possible events we can trace using Profiler. Take a quick look at all the categories available, then uncheck the box.
Check the box marked “Show all columns” so we can see all the available attributes for each event.
- We don’t really need the Audit Login and Audit Logout events today, so uncheck them.
- You will need the “ExistingConnection” event 99% of the time; without it, any action performed by an existing connection will not show up in your trace.
- RPC: Completed stands for “Remote Procedure Call: Completed”. The SOCK application most likely makes use of RPCs, so we will leave that checked. Additionally, scroll right and select “Database” for the RPC:Completed event. We’ll see more about this later.
- SQL:BatchStarting and SQL:BatchCompleted denote the beginning and end of a group of T-SQL statements running against the server. These can come in handy for delving into specifics, but again, we’re really only interested in the procedure calls today. Uncheck these.
Run the TraceWe were very selective with the events we chose on the last screen, and we’d also like to limit the time that the trace runs, so that we’re not overwhelmed with information we don’t need, and the burden on the server is lighter than a heavy trace would be.
When we’re ready to log into SOCK, then we click the Run button in SQL Profiler. We can see “Trace Start” at the top of the event list in Profiler, followed by a series of “ExistingConnection” rows.
We only want to capture the events around the SOCK login, so as soon as the trace begins, we log in to the SOCK application, and then click the red “stop trace” button in SQL Profiler. We now have a table of traced events.