April 12, 2007 at 2:48 pm
I have an application that takes a long time to run some reports. What's the best way to identify what query is being run against the database?
Thanks...Nali
April 12, 2007 at 3:34 pm
There are number of methods to identify a database call and its script. First that comes to my mind is to setup Profiler, then filter only application or user name that you are interested in and by time column you will be able to identify the query text. But disadvantage of this method is that you have to setup Profiler beforehand, so it will display all the call send after that.
In your situation, when something is already running for long and you need to check for it, query sysprocesses view, like this:
select db_name(dbid), * from sysprocesses
You can also limit it to program_name, user_name, and so on. Pay special attention to waittime column, it shows time elapsed for each process. Also check for blocked column, it shows which SPID blocked this process.
When you identify suspected SPID, you can find out what it actually running now by quering:
dbcc inputbuffer(your_spid)
Hope, this will help.
April 13, 2007 at 5:45 am
You can use the profiler trace as back ground process and find out the culprit.
Regards,
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply