September 8, 2016 at 8:05 am
Expert, I am working on scheduling a job which runs every 10 minutes and kill any active session (which is active for more than 10 minutes) from a specific user. I also would like to get an email everytime the session is killed. Can please someone help me with this?
SET NOCOUNT ON
-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
(
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(4000) NULL
)
-- Table variable to hold running processes information
DECLARE @BusyProcess TABLE
(
SPID INT,
Status VARCHAR(100),
Login VARCHAR(100),
HostName VARCHAR(100),
DBName VARCHAR(100),
Command VARCHAR(200),
CPUTime INT,
DiskIO INT,
LastBatch DATETIME,
ProgramName VARCHAR(200),
EventInfo NVARCHAR(4000), -- extra column to hold actual stored procedure or batch call text
EventTime INT -- time in minutes, a process is running
)
-- Insert all running processes information to table variable
INSERT @BusyProcess
( SPID, Status, Login, HostName, DBName, Command, CPUTime,
DiskIO, LastBatch, ProgramName )
SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,last_batch,program_name
FROM SYS.SYSPROCESSES
WHERE
1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
--Transactions that are open not yet committed or rolledback
WHEN Status = 'SLEEPING' AND open_tran > 0 THEN 1
ELSE 0 END
AND cmd NOT LIKE 'BACKUP%'
AND hostname in ('hostname')
-- Cursor to add actuall Procedure or Batch statement for each process
DECLARE cur_BusyProcess Cursor
FOR SELECT SPID
FROM @BusyProcess
OPEN cur_BusyProcess
DECLARE @SPID INT
Fetch NEXT FROM cur_BusyProcess INTO @SPID
While ( @@FETCH_STATUS <> -1 )
BEGIN
INSERT @Inputbuffer
EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')'
)
UPDATE @BusyProcess
SET EventInfo = I.EventInfo,
EventTime = DATEDIFF(MI,LastBatch,GETDATE())
FROM @BusyProcess b
CROSS JOIN @Inputbuffer i
WHERE B.SPID = @SPID
DELETE FROM @Inputbuffer
FETCH NEXT FROM cur_BusyProcess INTO @SPID
END
CLOSE cur_BusyProcess
DEALLOCATE cur_BusyProcess
September 8, 2016 at 8:11 am
Can please someone help me with this?
With what, exactly?
We're busy people and we're not paid to answer questions here, so ...
Please be more specific, rather than asking people to examine every single line of code which you have posted in the hope of determining what you're asking for.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 8, 2016 at 8:15 am
Thanks Phil but this question is for people who are not that busy so they can take their time and look at the code? If my question isn't clear then please tell me? Again, this statement is not for busy people.
September 8, 2016 at 8:43 am
What do you need help with? What's the code doing or not doing that it shouldn't?
And I will reiterate my comment from last time this came up
GilaMonster (6/10/2016)
Chitown (6/9/2016)
I just can't seem to educate users who runs this long query. Even though they pull unnecessary data, they just don't seem to understand. This is my last resortThing is, if you do this, the users have 'proof' that you're hindering their ability to do their job. They take that to your boss and suddenly you're the bad guy.
This is not a technical problem, don't try to solve it with technology. It's a management and person problem.
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
September 8, 2016 at 8:51 am
I'm not sure if this approach would be flexible enough for your needs, but it would leverage some built in features of SQL Server, rather than attempting to code your own.
There is an interesting setting, called 'query governor cost limit', which can be configured at the server level or set at the session level, that enforces an upper limit on user queries (not system sessions) that would exceed an estimated number of "seconds". Rather than killing sessions, what it does is cancel the query execution before it starts and instead returns an error 8649.
NOTE: Keep in mind that the limit value is not actual seconds but some relative value that would need to experiment with. Also, it probably relies on table and index statistics for accurate estimates, so maintaining stats would be important.
Here is an example what it would look like when implemented as a session level setting:
SET QUERY_GOVERNOR_COST_LIMIT 10;
SELECT .. FROM ..
JOIN .. JOIN .. JOIN ..
<some complicated query>
Msg 8649, Level 17, State 1, Line 3
The query has been canceled because the estimated cost of this query (50805) exceeds the configured threshold of 10. Contact the system administrator.
This error message would be raised back to the user, and also you can simply setup an operational Alert with email notification on this specific error number.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 8, 2016 at 9:05 am
Chitown (9/8/2016)
--If my question isn't clear then please tell me?--
Seriously? Your question was this
Can please someone help me with this?
That's a clear question, I'll agree. Possible answers that come to mind:
Yes, No, Maybe
The question that I suspected you meant to ask:
Here is a long piece of code which I have written. What I am trying to do with the code is find long-running queries and kill them. Please read through all of my code, understand it and identify any problematic areas. Then suggest changes to all parts of the code which require them, such that I can satisfy my requirement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 8, 2016 at 9:59 am
If this is SQL Server 2012 or better (that's the forum you're posting in), why are you using sys.processes and DBCC INPUT BUFFER instead of the Dynamic Management Views (DMV) like sys.dm_exec_requests and sys.dm_exec_sql_text, which are going to be a heck of a lot easier to manage than what you have. You can get the query text in a single query instead of running it through a cursor as you're doing now. That's highly inefficient. If you need help building that query, I can recommend the book Performance Tuning With Dynamic Management Views (it's free to download). That will be a much better way to do this same thing.
Now, as to whether or not you should automatically kill long running queries, that's another question. If the query is long running and it has modified data, your rollback could be longer than the query was. You're going to need to be very cautious with this approach. It could cause more problems than it solves.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2016 at 10:04 am
Chitown (9/8/2016)
Expert, I am working on scheduling a job which runs every 10 minutes and kill any active session (which is active for more than 10 minutes) from a specific user. I also would like to get an email everytime the session is killed. Can please someone help me with this?...
Please don't do this. Read Gail's comments very carefully and understand what she is trying to tell you. Speak to management.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 8, 2016 at 11:08 am
Chitown (9/8/2016)
Expert, I am working on scheduling a job which runs every 10 minutes and kill any active session (which is active for more than 10 minutes) from a specific user. I also would like to get an email everytime the session is killed. Can please someone help me with this?
SET NOCOUNT ON-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
(
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(4000) NULL
)
-- Table variable to hold running processes information
DECLARE @BusyProcess TABLE
(
SPID INT,
Status VARCHAR(100),
Login VARCHAR(100),
HostName VARCHAR(100),
DBName VARCHAR(100),
Command VARCHAR(200),
CPUTime INT,
DiskIO INT,
LastBatch DATETIME,
ProgramName VARCHAR(200),
EventInfo NVARCHAR(4000), -- extra column to hold actual stored procedure or batch call text
EventTime INT -- time in minutes, a process is running
)
-- Insert all running processes information to table variable
INSERT @BusyProcess
( SPID, Status, Login, HostName, DBName, Command, CPUTime,
DiskIO, LastBatch, ProgramName )
SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,last_batch,program_name
FROM SYS.SYSPROCESSES
WHERE
1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
--Transactions that are open not yet committed or rolledback
WHEN Status = 'SLEEPING' AND open_tran > 0 THEN 1
ELSE 0 END
AND cmd NOT LIKE 'BACKUP%'
AND hostname in ('hostname')
-- Cursor to add actuall Procedure or Batch statement for each process
DECLARE cur_BusyProcess Cursor
FOR SELECT SPID
FROM @BusyProcess
OPEN cur_BusyProcess
DECLARE @SPID INT
Fetch NEXT FROM cur_BusyProcess INTO @SPID
While ( @@FETCH_STATUS <> -1 )
BEGIN
INSERT @Inputbuffer
EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')'
)
UPDATE @BusyProcess
SET EventInfo = I.EventInfo,
EventTime = DATEDIFF(MI,LastBatch,GETDATE())
FROM @BusyProcess b
CROSS JOIN @Inputbuffer i
WHERE B.SPID = @SPID
DELETE FROM @Inputbuffer
FETCH NEXT FROM cur_BusyProcess INTO @SPID
END
CLOSE cur_BusyProcess
DEALLOCATE cur_BusyProcess
I do apologize, I didn't realize that I had asked the related question before. Anyways, this is the query I have. I am executing this SP but it is still not killing the session. Hostname is ITN, which is mine and I am testing this code in a lower environment. I have open transaction which I was running for over 1 minute and then I executed this SP but it didn't kill the session.
ALTER proc [dbo].[check_longrunningQueries] @Total_time_Executed int
as
BEGIN
SET NOCOUNT ON --added to prevent extra result sets from
-- interfering with SELECT statements.
--dbo.check_GatewayOracle_longrunningQueries 60
SET NOCOUNT ON
Declare @spid int,
@loginname Varchar(50),
@programname Varchar(50),
@host Varchar(50),
@startime datetime,
@lastudpatetime datetime ,
@DBName Varchar(50),
@query Varchar(4000) ,
@msg Varchar(8000) ,
@kill varchar(50) --<<EZ
DECLARE db_cursor CURSOR FOR
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" ,
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed"
FROM master..sysprocesses WHERE hostname= 'ITN' and open_tran > 0 and DATEDIFF (minute, last_batch, GETDATE()) > @Total_time_Executed
OPEN db_cursor
set @msg = 'Alert:A long running transactions were killed'
set @Total_time_Executed = 1
FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query
WHILE @@FETCH_STATUS = 0
BEGIN
set @kill = 'Kill ' + cast( @spid as varchar(5))
exec(@kill)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default Mail Profile',
@recipients = 'email address',
@body = @query,
@subject = 'The long running transactions from were killed '
FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
September 8, 2016 at 11:11 am
I agree with Chris and Gail.
Every one of those emails are going to result in a "WHY DID THEY KILL MY JOB?" scream.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 12:21 pm
Guys I do appreciate you telling me that this is not a good practice and I do agree but this is what I was asked to do. And before we implement any changes in the prod environment, CAB has to approve the change. So basically we want users to scream if they run any adhoc query which they r not supposed to run.
September 9, 2016 at 7:14 am
The Dixie Flatline (9/8/2016)
I agree with Chris and Gail.Every one of those emails are going to result in a "WHY DID THEY KILL MY JOB?" scream.
That's why it's important to limit execution of these queries via a query cost theshold and for the DBA to get sign-off from executive management. That way the DBA doesn't have to answer the question "Why did you kill my query?"; instead the user has to answer for why they are attempting to run complicated ad-hoc queries that exceed X seconds/minutes in the first place. It's conceptually no differant than using permissions to restrict what objects the users have access to.
I'm offering advice under the assumption that this is a mission critical transactional database, some users are allowed to run ad-hoc queries, but there are occasional long running queries that cause performance issues or blocking. If the real problem is that users shouldn't be running ad-hoc queries in the first place, then the solution would be to tighten up on access using role based authorization and permissions.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 9, 2016 at 7:27 am
What I'd suggest is, instead of killing sessions, and instead of using the Query Govenor (users aren't going to voluntarily set that, and server-wide means *everything* gets it), is to set up resource govenor groups, put maintenance and normal app stuff into one and have user ad-hoc queries fall into another, and set restrictions and limits on the ad-hoc query pool.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply