May 9, 2013 at 9:39 am
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?
May 9, 2013 at 9:49 am
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.
May 9, 2013 at 9:54 am
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.
May 9, 2013 at 9:57 am
SYS.PROCESSES or SYSPROCESSES?
May 9, 2013 at 10:20 am
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
May 9, 2013 at 10:29 am
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
May 9, 2013 at 11:09 am
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?
May 9, 2013 at 12:32 pm
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
May 9, 2013 at 12:50 pm
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.
May 9, 2013 at 1:05 pm
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
May 9, 2013 at 2:41 pm
GilaMonster (5/9/2013)
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.
Those ad-hoc queries are hammering the SAN really bad (tempdb and the actual Data LUN) and they keep running for ever, affecting other clients as well.
Killing those ad-hoc queries after X minutes will release the IO pressure. So instead of hammering the SAN for 5+ minutes, if they are not done my 1 min or less , they will be killed.
May 9, 2013 at 4:47 pm
sql-lover (5/9/2013)
Killing those ad-hoc queries after X minutes will release the IO pressure. So instead of hammering the SAN for 5+ minutes, if they are not done my 1 min or less , they will be killed.
then the user will re-run the query and it'll hammer the SAN for another minute, be killed, be re-run, be killed, repeat until the user gets fed up and complains to management that they can't do their work.
You're addressing the symptoms, not the 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
May 9, 2013 at 7:50 pm
GilaMonster (5/9/2013)
sql-lover (5/9/2013)
Killing those ad-hoc queries after X minutes will release the IO pressure. So instead of hammering the SAN for 5+ minutes, if they are not done my 1 min or less , they will be killed.then the user will re-run the query and it'll hammer the SAN for another minute, be killed, be re-run, be killed, repeat until the user gets fed up and complains to management that they can't do their work.
You're addressing the symptoms, not the problem.
Hmmm... now I see your point.
Let me play a bit with resource governor. But something it is clear to me, I do not want to limit or affect other users or clients, which shares the same instance, so need to test this carefully.
May 9, 2013 at 9:29 pm
I wouldn't do any of that because it doesn't address the basic problem of users writing bad code for their reports. Instead, find the users that are writing the code and show them what's going on and how to kill their own runs. If you help them with tuning their code a bit, the problem will go away.
The real problem, of course, is the original sin of letting users write their own code. For a week, let the runs finish, collect the data, and submit a proposal to disallow it in the future. If management refuses, then just let them run until they get the idea. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2013 at 7:46 am
Jeff Moden (5/9/2013)
I wouldn't do any of that because it doesn't address the basic problem of users writing bad code for their reports. Instead, find the users that are writing the code and show them what's going on and how to kill their own runs. If you help them with tuning their code a bit, the problem will go away.The real problem, of course, is the original sin of letting users write their own code. For a week, let the runs finish, collect the data, and submit a proposal to disallow it in the future. If management refuses, then just let them run until they get the idea. 😉
Ideal solution, but not possible.
This is a special type of ad-hoc feature of report that allows clients to run their own reports (customize them) Is an added feature and it's not free. It won't be discontinued anytime soon. Moreover, it may be expanded or sold to other big clients as well. A Developers idea, you know .... :ermm: ... not a DBA idea ...
The good thing is that I do not see many clients using it, due cost vs benefit. Only 5% or less of our clients.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply