Eric M Russell (2/12/2015)
Jeff Moden (2/12/2015)
Killing even the most ridiculous users sessions is a really good way to need an updated resume. I know it takes a whole lot longer to do but proper use of the resource governor and training the cowboys will be a whole lot more effective over time. Management buy-in for all of it in the form of a company policy would help protect you and to set expectations.Just killing stuff is going to perpetuate and maybe make the problem worse.
I agree 95%, but in a data warehouse environment, insuring a successful DDL script deployment or critical data reload often times requires a window where the DBA has exclusive use of the database. That's impossible if ad-hoc users have SQL brain farts running 24/7. The DBA should schedule maintenance for a time when management approves, and then give users due notice, but when these maintenance windows arrive, it's time to kick the kids out of the pool.
Real mission critical querying takes place during regular business hours.
I've never seen a DBA lose their job because they aborted zombie SQL processes that ran for half and day and into the night. In fact, often times it's management who orders the kill (behind the scenes with a wink and a nod). But I have seen a DBA lose their job because the database was generally no longer available to executive level users due to missed deployments or runaway resource consumption caused by lower level script kiddies.
In an organization where SQL coders act like cowboys, the DBA has to play the role of Sheriff, and that sometimes means (trouble)shooting... to kill.
I actually have to disagree about the timing of mission critical querying. Well meaning folks that need to get something big done know that the hinnies will be pasted to the wall if they seriously interfere with "normal" daylight hours and so will run their "critical" queries at night. And, make no mistake about it, some of them run queries that are very important to the business.
I do, however, absolutely agree with you that it's just as or more mission critical for the DBA to have some windows of opportunity. As you said, the best thing would be to publish when those windows are with all the appropriate warnings if you intend to kill SPIDs.
Shifting gears a bit, if it's truly a 24/7 shop, it's not like you can set the database to single user or kick anyone out of the database to get something done. That's when the "sheriff" finds a way to get all the Indians and Cowboys across the river to the other side using just one canoe. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.