0

I have a problem with a program I am running, where a query sometimes take 10-30 seconds to complete. I thought about using the SQL profiler to help me identify the problem.

The profiler definately shows that there is a problem, but I dont really know what to do about it. The following image is from the profiler: profiler

Can somebody tell me something about that query?

The thing is that IUModule.TaskSchedulerChangeMessages is not a scheme that exists so i'm wondering how this query can even be run.

Jesper Plantener
  • 229
  • 3
  • 16
  • That duration is just the 60 second timeout for your service broker `waitfor` statement. Presumably no messages were received in that minute. – Martin Smith Oct 26 '14 at 13:04
  • I need a bit more elaboration on that. Are you saying that the duration doesn't actually affect the database? – Jesper Plantener Oct 26 '14 at 13:07
  • 2
    Yes. It's just waiting for a message. And not consuming any resources from the 0 CPU and 4 reads. – Martin Smith Oct 26 '14 at 13:08
  • And it does not block for other queries while waiting? – Jesper Plantener Oct 26 '14 at 13:10
  • Wouldn't have thought so unless it is in a transaction with something else that holds a lock. You need to look at the waiting tasks DMVs if you suspect blocking. Likely the issue is nothing to do with that statement though. – Martin Smith Oct 26 '14 at 13:11
  • All right. It's just the "Waitfor" statement in the beginning of the line that made me worry that it would block. – Jesper Plantener Oct 26 '14 at 13:12
  • Is there a place where I can see where the service brokers queries come from? because thatparticular query should definately not be run since the scheme doesn't exist. – Jesper Plantener Oct 26 '14 at 13:23

0 Answers0