Profiler Newbie Question - Which Metrics Should I Watch?

Hi Guys,

I've got an order generation query that will _sometimes_ run long. By that I mean every few days a query used to estimate the required stock quantities for an order will time-out because it has taken an extended time to run. This only occurs at one particular site. This site shares a common schema with others who do not experience this issue, although site is a particularly busy one.

This query (a stored proc call) needs to aggregate sales data from ~200k rows and usually takes 10-15 seconds. On the days when it 'runs long' it will hit the 5 minute time-out period and be failed by the application.

The problem I have with this process is that if I have the application dump out the query it's using to generate the order (this query will vary as the stored proc is passed the name of a temp table to use for this order) so I can run it in Query Analyzer to get an execution plan and maybe some clue as to why it takes so long; the issue will vanish. After I've tried to investigate in this way an order generated from either Query Analyzer or the application will work in the normal time. When the query is running long any processes which attempts to write to the sales data tables will get blocked until the order generation has finished.

The application is accessing the database via ADO 2.6. The database itself is running on SQL Server 2000 sp3 under Win2k.

I believe that the SQL Profiler is the tool best suited to investigate this but I'm hoping for some advice on which of the myriad of watchable metrics I should be checking here.

Any profiler advice or estimates as to what the issue may be here would be great!

-Karl.