For SQL Server 2000, I would suggest running a trace using SQL Server Profiler and filtering either on CPU, duration and/or reads (and possibly writes). With the filter, SQL Server Profiler will only show queries that meet your specification. For example, duration > 1000 milliseconds.
answered Nov 16 '09 at 05:51 PM
Instead of using the Profiler gui to collect this data, I'd suggest, very strongly, that you set up a server side trace. This is using the trace events that Profiler uses, but activated through a TSQL script. This puts less of a load on the server and guarantees a good set of data will be collected. Gail Shaw has two excellent articles on how to set this up over at Simple-Talk. Here's the first one.
answered Nov 17 '09 at 09:54 AM
Grant Fritchey ♦♦