• Paulo A. Nascimento (7/5/2015)


    you cannot generate for free such secure and bold opinions

    Actually, I can.

    Because I know how SQL works and because I've seen people try this before. Because I've seen the screw ups that can be caused caused by automatically killing sessions and because I've cleaned them up before.

    1. The session (except for the 1st one, in theory), runs at the most for 180 seconds (tops) with current parametrization;

    "Except for the first one", the first one being the blocker, it's probably a data modification (based on what you said below about the queries being selects), which means the rollback will take longer than the query ran for. And while the current queries may run no more than 3 minutes, that's no guarantee that they will continue to do so.

    Also, you presented this as a general solution, hence I commended on it as a general solution.

    2. The session is an IBM Cognos BI read-only query or Office query (basically a DML select so no harm done).

    If you only have read only selects, then you cannot have blocking. Selects take shared locks and shared locks are shared, meaning any number of sessions can take shared locks on the same resource. To have blocking, you have data modifications going on. It'll be likely that the data modification is the head blocker (because shared locks are shared) and so you're more likely to kill a data modification than anything else

    3. I have mechanisms in place to detect early corruption (SQL Server alerts) if it was applicable, which is not the case.

    Killing sessions can't cause corruption, so irrelevant.

    4. Doubt explicit transactions are used at all;

    Yup, that's what I expected and meant by "you could end up messing up the data if the people who wrote the code didn't use transactions properly."

    Without transactions, all data modifications are auto-committed, so if you have a set of data modifications that all must succeed and they are not wrapped in a transaction and you kill the session, you can end up with only some of them running. And that's no fun at all to clean up after the fact

    5. Last but not least, this was requested by and for the business, so pertinent audiance has knowledge that this solution is a fair trade-off;

    Business, in my experience, seldom has the technical knowledge to understand the impact of their requests, which is why we have to explain to them the pros and cons, the risks and the alternatives. It's not their job to understand the technical details and impacts, that's our job.

    By the way, what your alternative solution would be ?

    If there's frequent blocking, then I would investigate the cause of the blocking and fix the problem, rather than addressing the symptoms. If a session needs to be killed, I want to check what it's doing and what the consequences of rolling it back will be first.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass