sp_kill: Add @EmergencyMode input parameter to minimize work done by the procedure

[jobbish-sql]

Is your feature request related to a problem? Please describe.
(theoretical issue based on reading the code) I could be an end user having a performance emergency on my server but sp_kill is taking a long time to return results because there are some executing queries with huge query plans. It takes a long time to write the query_plan and live_query_plan columns to the temp table and it takes a long time for SSMS to render the result set that includes the XML columns.

Describe the solution you'd like
Add an input parameter @EmergencyMode BIT = 0

If @EmergencyMode = 1, then sp_kill should avoid all optional work that could result in delays on very busy servers. Suggestions:

• don't write the query_plan and live_query_plan columns to the temp table
• don't attempt to create the output table if it doesn't exist (I've seen create table statements take over a minute on extremely busy servers)
• don't attempt to write rows to the output table

Describe alternatives you've considered
Could also have an input parameter @SkipQueryPlans BIT = 0 that only skips the query_plan and live_query_plan columns

Are you ready to build the code for the feature?
Yes, but I'd like your guidance on which features of sp_kill could be considered to be optional when the procedure is run in emergency mode.

[BrentOzar]

I love it, makes sense.

If you don't wanna write rows to the output table, just don't specify the output table, heh. You don't need a parameter for that part. But I agree, I'm fine with skipping the create and the population if @EmergencyMode = 1.

Looking at the source tables for the temp table, I think we could skip all of these joins in EmergencyMode:

• sys.dm_exec_query_memory_grants
• sys.dm_exec_query_plan
• sys.dm_exec_query_statistics_xml
• sys.dm_db_task_space_usage
• sys.dm_exec_sql_text - I could go either way on this one. How about if @EmergencyMode = 1, we skip the above stuff, and if it's = 2 then we skip this one too?