Selecting events from SQL Profiler in 2005 for profiling a query

Question

Hi, I need to profile a large SQL query (in a particular database, not the whole server), and I cant figure out how to setup the profiler. I tried reading MSDN SQL web site, but it has not helped.

I am stuck at the Trace Properties "Events Selection" window. I need help as to which items to select in "Events Selection" window.

I am more interested in finding out why the query is not populating the tables (as opposed to performance)

Thanks

Answer 1

Is this answer of use: How Can I Log and Find the Most Expensive Queries?

Answer 2

I usually use the following setup. As Mich said it depends on what you want tho.

• On the General tab select TSQL_Duration from the "Use the template:" drop down list.

• On the events selection tab select
  RPC:Completed if your watching a
  stored proc or SQL:BatchCompleted if you are just watching for queries.

• Check all boxes to the right.

You can monitor different events based on the template you use. I have also found "Tuning" to come in handy.

There might be a TON of other stuff that comes up so you may want to filter what is displayed by editing some of the information that comes up when you click the "Column Filters..." button.

Examples:

Duration over 3000 (3 seconds)

TextData has some text that is specific to your query ( %stored_proc_name% )

Answer 3

If by large SQL query, you mean a large stored procedure that contains multiple individual statements and you want to profile each of those statements when the sproc is called (like this) then monitor the SP:StmtCompleted event. Otherwise, just choosing the "Standard" profile template should suffice.

One of my first ports of call when looking into poor performance of a query would be to look at the execution plan though - that will tell you where the time is being spent.