shahgols (11/8/2013)
I really appreciate your response and patience!If I run Profiler, the sql statement will never finish. haha
I am using sp_whoisacitve. the CPU usage is much, muuuuch higher on production than it is on staging. And the "reads" column is somewhat higher. On staging, where the script runs in 25 seconds, the CPU shows 19K cycles, and reads shows 45K reads. On production, after 3 hours of running the same script, the CPU is at 9M cycles, and reads is at 64K reads.
I hope that answers your question, otherwise let me know what to do (that doesn't involve Profiler) please.
My guess is that you are running Profiler with STATEMENT completed as opposed to BATCH completed events. NEVER run profiler with STATEMENT completed if you have cursors, UDFs, etc in your production system. BATCH completed won't cause the query to run slowly by trying to capture every execution of the guts of the UDF like STATEMENT completed will.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service