Post reply

Sleeping queries blocking rebuild index and update statistics job in Always on

  • February 3, 2015 at 10:09 pm

    #295173

    Hi friends

    At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.

    I have to stop update statistics job manually once i come to office manually.

    once i have killed blocking sleeping query but then other sleeping query blocked it and so on.

    what is the issue and please suggest some solutions

    Regards

    Anoop

  • February 4, 2015 at 12:03 am

    #1775204

    Are you performing Update Statistics with FULLSCAN?

    If yes then have you checked the Data Types of the columns against which you are performing Update Statistics.

    Read this post and see if you are facing similar issue:-

    http://www.sql-server-performance.com/2014/performance-tuning-re-indexing-update-statistics/

    Please let me know your response.

    With Thanks,

    Satnam

  • February 4, 2015 at 12:39 am

    #1775206

    satnam.singh 44358 (2/4/2015)

    Are you performing Update Statistics with FULLSCAN?

    If yes then have you checked the Data Types of the columns against which you are performing Update Statistics.

    Read this post and see if you are facing similar issue:-

    http://www.sql-server-performance.com/2014/performance-tuning-re-indexing-update-statistics/

    Please let me know your response.

    With Thanks,

    Satnam

    Hi Santam

    I think you didn`t get what i was saying.

    I telling that my sql server Index maintenance task which includes rebulid index and update statistics and its auto schedule get`s blocked by sleeping query and not other way round ....

    the link you provided was great but it says that index rebuild and update statistics where blocking other queries...

    Also my Index maintenance task presently takes all dbs and default full scan for update statistics .

    I wish to change it only particular critical tables but i would do it after wards when this issues is resolved....

    Regards

    Anoop

  • February 4, 2015 at 6:20 am

    #1775282

    what are the connections doing?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 4, 2015 at 10:17 pm

    #1775562

    Perry Whittle (2/4/2015)

    what are the connections doing?

    Connections are mostly waiting for commands

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply