1

For the last few days the service broker command which run from master database is consuming much more RESOURCES than before. The Impact on the Database is high (from the bad side), It causes for internal waits. I didn't make any changes in the database before and after the 10.07.2014 (you can see this start date in the graph). I checked if service broker is enabled in any database and found that it is, in Tempdb & Msdb. What can cause these changes ?

I attached a graph, you can see the behavior change.

enter image description here enter image description here

i attached another screen shot as you can see from sp_who2, what i don't understand is, you can see that the "LastBatch" is in the very old , from the 10/12 07:55 (this results are from today 10/20/2014 10:23 Local time). why the session not closed . ?

1 Answer 1

5

Looks like you're using a tool not capable of identifying benign waits. See Wait statistics, or please tell me where it hurts. There is no impact, just bad monitoring.

I recommend you read Filtering out benign waits. The engine has certain background tasks and when these background tasks don't have any work they simply sit idle, waiting for work. while waiting for work, the wait times are aggregated by the wait stats but unfortunately there is no way to distinguish between 'wait for a resource to continue the work' or simply 'wait for something to do to start the work'. The later case is a 'benign wait'. The less work these tasks actually do, the more wait stats they aggregate. These tasks never shut down and there is no session to close. You have embarked on a snipe hunt.

For more details read Understanding how SQL Server executes a query and How to analyse SQL Server performance.

4
  • 1. I attached another screen shot as you can see from sp_who2, what i don't understand is, you can see that the "LastBatch" is in the very old , from the 10/12 07:55 (this results are from today 10/20/2014 10:23 Local time). why the session not closed . ?
    – itzik Paz
    Oct 21, 2014 at 6:49
  • Can I close/Kill this BACKGROUND process (BRKR TASK). And still I don't understand why this process stays unclosed .and can i know what he run (BRKR TASK) ?
    – itzik Paz
    Oct 28, 2014 at 9:14
  • 2
    No. You cannot, and you should not. Oct 28, 2014 at 9:16
  • So it will stay there forever ?
    – itzik Paz
    Oct 28, 2014 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.