SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I kill ad-hoc or long time running queries, safely?


How can I kill ad-hoc or long time running queries, safely?

Author
Message
sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4599 Visits: 1930
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96381 Visits: 38981
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.

Cool
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)
sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4599 Visits: 1930
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.
Neeraj Dwivedi
Neeraj Dwivedi
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2057 Visits: 1341
SYS.PROCESSES or SYSPROCESSES?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96381 Visits: 38981
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

Cool
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)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228297 Visits: 46341
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, 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


sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4599 Visits: 1930
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228297 Visits: 46341
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, 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


sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4599 Visits: 1930
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228297 Visits: 46341
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, 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search