I agree but in this case its with an adhoc dev enviroment which its ok. If someone could explain to me how to go about it I would appreciate it
As you've seen, this can certainly be done but it's a really, really bad idea on several fronts. The best thing to do is to find the person who's actually running the query and have them cancel the query. The reason for that is if you kill the spid, it may get "stuck" in a never ending- CPU consuming "zero percent to go" rollback. This is a documented problem with SQL Server and the only way to kill the spid with the bad rollback is to bounce the SQL Server service.
The other thing is a bit more on the human side. If you kill the run, the person who wrote it learns nothing and may just try to run it again... and again... and again. What you need to do is go see the "user", show THEM how to kill the run, and then help them improve their query and, perhaps, how to read an execution plan to help avoid such problems in the future in a thoughtful mentor-like fashion.
If neither you or the user have time to do that, then consider how much time each of you waste during such episodes. You work for the same company. Take the time to help each other out. Then the "user" will pass his/her knowledge on to the next person and suddenly your job gets a whole lot easier because you don't have to spend so much time killing spids. ;-)
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs