Monday, May 3, 2010

Using SQL Server Profiler – Tracing EF and SQL queries
SQL Profiler is a sometimes helpful figure out what the applications are doing on the wire. So open SQL Profiler and start with File | New Trace. It will ask to which database you’d like to connection and then pop-up the Trace Properties window. If you click on the Events Selection tab, you can select the events you see.

For tracing EF, make sure RPC:Completed events  is present and also that the TextData column is checked for RPC:Completed event.
For SP and dirct query execution tracing, make sure SQL:BatchCompleted and SQL BatchStarting  events  are present and also that the TextData column is checked for them.
When you press the Run button, you’ll see a rolling list of calls made to that instance of SQL Server. Now when you run an EF application and it will trace the activites in SQL server for EF.
Tracing a particular Query Token (either executed from EF or TSQL)
For Tracing a particular token like in my case a store procedure named ‘GetPricing’. So the easiest thing to do is to press the Column Filters button and set the Filter on column ‘TextData’

Now write a query containing ‘GetPricing’ token and execute in query editor.   Or execute the stored procedure containing similar SP Name.
Here is the output:
Nice tool  for  traceability