Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How can I kill ad-hoc or long time running queries, safely? Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 9:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 373, Visits: 1,234
Ok,

Need the final push on this ...

I do have a requirement at work that I need to control or kill ad-hoc queries that have been affecting performance on an specific client or database.

I would prefer to avoid the bad queries altogether, but I can't. And the reason why this is happening in the 1st place is because the developers had the idea (not sure if good or bad) of giving the flexibility to the user of writing their own customize reports. Nice, user or business perspective, but now I have the database problem on my hands. Anyway ...

Narrowed the problem to this T-SQL query that allows me to see what's taking more than X amount of time (sharing, in case someone else need it ) ...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 1
[Spid] = session_Id
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
,total_elapsed_time/1000 AS 'elapsed time in seconds'
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE session_Id > 50
AND DB_NAME(sp.dbid)='MyDatabaseHere' AND session_Id NOT IN (@@SPID) AND nt_username='Joe'
ORDER BY total_elapsed_time/1000 DESC;

The question I have ... how to handle the 2nd part, which is killing the process. I guess that I can put above in a job, insert on a temp table, and query for anything longer than 120 seconds and then kill? However, I do not want to end with bunch of rollback processes if the rogue transaction is an INSERT, UPDATE, DELETE. Then I will affect performance instead of improving it.

By the way, I also thought about using query governor, but I'm afraid that will affect the whole SQL instance. I need this for a particular SQL login and specific client or database.

Any hints?
Post #1451206
Posted Thursday, May 9, 2013 9:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 22,992, Visits: 31,468
Don't have time to help with the query itself, but I noticed you are using sys.sysprocesses. This is a SQL Server 2000 system table and is for backward compatibility. You should lookup in Books Online the DMV's you should used to replace this table as sys.sysprocesses has been deprecated and could disappear in a future version of SQL Server.

The one DMV that comes to mind is sys.processes.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451217
Posted Thursday, May 9, 2013 9:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 373, Visits: 1,234
Lynn Pettis (5/9/2013)
Don't have time to help with the query itself, but I noticed you are using sys.sysprocesses. This is a SQL Server 2000 system table and is for backward compatibility. You should lookup in Books Online the DMV's you should used to replace this table as sys.sysprocesses has been deprecated and could disappear in a future version of SQL Server.

The one DMV that comes to mind is sys.processes.


God catch! It ran fine on my SQL2012 Dev box, but that's an easy change.
Post #1451225
Posted Thursday, May 9, 2013 9:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 809, Visits: 1,157
SYS.PROCESSES or SYSPROCESSES?
Post #1451227
Posted Thursday, May 9, 2013 10:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 22,992, Visits: 31,468
Neeraj Dwivedi (5/9/2013)
SYS.PROCESSES or SYSPROCESSES?


Sorry, there is no sys.processes. The system table sysprocessess mappes to the following:

sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451250
Posted Thursday, May 9, 2013 10:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
Look into using resource governor (not query governor), if you just kill sessions there's a fair chance that the people running them swear, curse and rerun the queries.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1451258
Posted Thursday, May 9, 2013 11:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 373, Visits: 1,234
GilaMonster (5/9/2013)
Look into using resource governor (not query governor), if you just kill sessions there's a fair chance that the people running them swear, curse and rerun the queries.


Hi there Gail ...

Can I kill or restrict an specific user that way or does it apply at how instance level?
Post #1451273
Posted Thursday, May 9, 2013 12:32 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
Kill no.
As for how it works, it would take a few pages to explain, so maybe have a read through the entries in BoL (there's a load of information) and post any specific questions.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1451294
Posted Thursday, May 9, 2013 12:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 373, Visits: 1,234
GilaMonster (5/9/2013)
Kill no.
As for how it works, it would take a few pages to explain, so maybe have a read through the entries in BoL (there's a load of information) and post any specific questions.


Doing that right now. I've heard about it before, but have not implemented on any of my production boxes yet.

However, not so sure if will do the trick. As the issue with those ad-hoc queries is more IO and elapsed time than CPU or RAM. It is my understanding that resource governor is more focused on RAM and CPU resources.
Post #1451301
Posted Thursday, May 9, 2013 1:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
Correct, it's CPU and RAM, but how does killing a query that's done a lot of physical IO help? The IO's already done by the time you kill the query, the stuff that it read already in memory.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1451304
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse