SP makes the version store grow - hanging

  • Hi

    At occasions I've noticed that tempdb grows on one of my SQL Servers. And it keeps growing until either I run out of storage or I kill certain processes. After some intensive research I've pinned down the cause for tempdb to grow. It is the version store that is the culprit, and are increasing in size. When this happens I have a stored procedure that are executed from one of several servers (several servers can trigger this procedure). When I look at the process in question in Activity monitor I notice that they always are marked as suspended in "Taskstate" and that wait state is "ASYNC_IO".

    How can I monitor exactly what is happening? Since this situation happens with the same 1 or 2 stored procedures? Are there any way that I can use SQL Server profiles to do this, or are there any other recommended way of doing this?

    Please help

    Regards

    Andy.l

  • First question I'd ask is, what are those procedures doing. If it just runs and runs, something is up in that code. Rather than try to determine what's happening at the low level on the server, examine the code and determine what's happening there.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi thanks for your answer

    It doesn't look like they're just running. It's more like they're hanging. And since the SP's are keeping the transactions open the version store keeps running, since the database is using row lock.

    So I'm guessing this is maybe being caused by some kind of locking in the database. That's why I would like to use the profiler and monitor certain SP's since this is NOT happening all the time, and not from all servers.

    /Andy.l

  • andy.l (11/23/2010)


    Hi

    At occasions I've noticed that tempdb grows on one of my SQL Servers. And it keeps growing until either I run out of storage or I kill certain processes. After some intensive research I've pinned down the cause for tempdb to grow. It is the version store that is the culprit, and are increasing in size. When this happens I have a stored procedure that are executed from one of several servers (several servers can trigger this procedure). When I look at the process in question in Activity monitor I notice that they always are marked as suspended in "Taskstate" and that wait state is "ASYNC_IO".

    How can I monitor exactly what is happening? Since this situation happens with the same 1 or 2 stored procedures? Are there any way that I can use SQL Server profiles to do this, or are there any other recommended way of doing this?

    Please help

    Regards

    Andy.l

    Do you have SSIS packages running in your server?

    Regards,
    Pravasis

  • Hi

    Nope, no SSIS packages.

    /Andy.l

  • Use sys.dm_exec_requests and see what is causing the procedure to wait. If it's blocked, trace the blocking chain, again, available from the same DMO.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • If the procedure is trying to update and delete the data from the same table in two different statements and it is being called at the same time then this would block either process. You can try the hints to minimize the impact like using rowlock ans nolock etc.

    Regards,
    Pravasis

Viewing 7 posts - 1 through 6 (of 6 total)

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