I have written the following script and have found it to be a rather effective, minimally invasive, and low-tech way to track down troublesome SQL statements when tuning my apps for SQL Server. I’ll usually set it up as a SQL Server Agent job to run once per minute. It logs all currently running SQL statements to the SQLLog table, for analysis at a later date. (Note that the job must be run as the sa user, or another user with sysadmin privileges in order for the fn_get_sql function to work.) The thing I like about this technique is that it seems to get a good cross-section of both long-running statements, and statements that may be short in duration, but are run often. I’ll then do a count on statements to see where to focus my tuning efforts. Enjoy!
-- SQLLog SQL Server Agent Job
-- Copyright (c) 2007 PerformantDesign.com
IF OBJECT_ID('SQLLog','U') IS NULL
CREATE TABLE SQLLog (sql_time datetime, sql_text varchar(8000))
DECLARE proc_cursor CURSOR FOR select sql_handle from [master].[dbo].[sysprocesses] where sql_handle <> 0x0000000000000000000000000000000000000000
OPEN proc_cursor
DECLARE @handle binary(20)
FETCH NEXT FROM proc_cursor INTO @handle
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
INSERT INTO SQLLog (sql_time, sql_text) SELECT getdate(), s.text FROM ::fn_get_sql(@handle) s
END
FETCH NEXT FROM proc_cursor INTO @handle
END
CLOSE proc_cursor
DEALLOCATE proc_cursor
SELECT COUNT(*) AS count, sql_text FROM SQLLog GROUP BY sql_text ORDER BY 1 DESC