• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

kill a SPID if taking more than % of resources

is it possible to configure to automatically kill a SPID if it is taking x amount of processor or memory; is it possible to set it up that way?

thanks
0
anushahanna
Asked:
anushahanna
  • 6
  • 4
  • 3
  • +1
6 Solutions
 
chapmandewCommented:
maybe...but why would you want to?
0
 
anushahannaAuthor Commented:
running a *huge* query in the off-peak hours, but don't want the system to off offline because off an overload, and more headaches in the morning...
0
 
LMiller7Commented:
In general this would be a bad idea, even if it were possible. If a process is consuming too many resources this indicates a problem you should investigate. Killing it doesn't resolve the problem but merely sweeps it under the rug. This destroys all evidence of the problem and makes it more difficult to resolve.

Killing a process may improve the performance of a server. Small consolation when your users complain that they can't access the resources they require because an important process isn't running.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> is it possible to configure to automatically kill a SPID if it is taking x amount of processor or memory

I wouldn't go with this approach.
Instead I would start

* Fine tuning the query or job that utilizes more processor or memory
* Create appropriate indexes

Note: Remember killing a job or spid affects your valuable DATA.
0
 
chapmandewCommented:
describe huge query....
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Note: Remember killing a job or spid affects your valuable DATA.

Instead you can cancel the execution of that query at all to reduce overall load to Server.
0
 
anushahannaAuthor Commented:
>> Note: Remember killing a job or spid affects your valuable DATA.

data integrity will not be affected, right? if not all successful, it will rollover?
0
 
chapmandewCommented:
>>data integrity will not be affected, right? if not all successful, it will rollover?

Maybe, maybe not.  Depends on what you're doing and how you have it structured.
0
 
anushahannaAuthor Commented:
>>describe huge query....

one time need/operations that does the same thing over and over again-one i am working right, as an example..
@str = replace(@str, CHAR(32) + CHAR(32), CHAR(32)) in a loop
need to compare procs for any slight modification between test and dev environments. has taken hours and still executing..

just concerned about it..
0
 
anushahannaAuthor Commented:
>>Killing it doesn't resolve the problem but merely sweeps it under the rug.

I agree. But for occasional data checks that involve large processing of data, failure should not affect users. but i see your point. thanks.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Maybe, maybe not.  Depends on what you're doing and how you have it structured.

well said, chapman

>> need to compare procs for any slight modification between test and dev environments. has taken hours and still executing..

Try of some alternative logics instead of looping..
Posting the entire logic would help us give you some tuning tips..
0
 
anushahannaAuthor Commented:
sure. thanks.

CREATE FUNCTION [dbo].[udf_cleaner] (@input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS 
BEGIN
		DECLARE @result VARCHAR(max)
		SET @result = REPLACE(@input, CHAR(13), CHAR(32))
		SET @result = REPLACE(@result, CHAR(10), CHAR(32))
		SET @result = REPLACE(@result, CHAR(9), CHAR(32))
		WHILE PATINDEX('%  %', @result) > 0
		SET @result = REPLACE(@result, CHAR(32) + CHAR(32), CHAR(32))
		RETURN (@result)
END

select object_name(a.object_id,db_id('DB1')) from 
	(select m.* from DB1.sys.sql_modules m, DB1.sys.objects o where o.object_id = m.object_id and type = 'p') a
join
	(select m.* from DB2.sys.sql_modules m,DB2.sys.objects o where o.object_id = m.object_id and type = 'p') b
on
	object_name(a.object_id,db_id('DB1')) = object_name(b.object_id,db_id('DB2')) and 
	dbo.udf_cleaner(a.definition) <> dbo.udf_cleaner(b.definition)

DROP FUNCTION [dbo].[udf_cleaner]

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Seems like you are trying to find object Dependencies of a procedure.
And you can easily find it using SQL Dependency tracker tool given below

http://www.red-gate.com/products/SQL_Dependency_Tracker/
0
 
anushahannaAuthor Commented:
Thank you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now