long running queries

Hi Everyone

I wanted to generate a script which will return all the queries which are running more than 5 minutes.

I want the text of the sql query so that i can tune the query if required.

I have used the profiler and have filtered the query which are running setting Duration parameter.

I actually wanted to set a automated method which will send me by mail the list of all queries which are running more than the desired time.
May be some store proc using system tables. which I will call in a job and the job will run periodically.

Thanks in advance.

You can save the results of the profiler trace to a SQL Server table, and then either ;

1) Have a trigger on the table to send an email, or

2) Use the user defined performance monitoring counters to do this - Use a SQL Agent job to periodically query the table, and set the user defined counter if the value exceeds the threshold.

Let me know if you need more details on setting up either of the above.

|||

Hi Rod,

Do you know other than Profiler, is there another less resource intensive way to record the SQL statements that run longer than a certain time? I have a very critical server that is very sensitive and always has blocking issue. I would like to use the least resource intensive way to get the statements that run long thus caused the blocking. I saw an article saying that we can use ODBC trace, to save the long running queries to a log file when configure the system DSN. http://msdn2.microsoft.com/en-us/library/ms403323.aspx Do you know whether the ODBC trace is less intensive than Profiler or not?

Also, where I should set it? on the SQL server that I want to find out the long query? Just create a DSN that points to the blocking issue db and save long query to a log file? Then all the long queries will be logged to the file as long as I turn on the trace? But all user applications on that server won't use that DSN though. The ASP pages set their own connection string. What the file looks like? Do I need to use other tool to interpret the log file? I use SQL server 2000 Enterprise Edition sp4.

Thanks a lot,

-Jessie