October 29, 2009 at 7:18 am
Hello friends,
I do have a 'SQL user' who runs a lot of queries that causes performance issues to the database machine. I want to write a query that would check the CPU,Memory I/O usage from the systems table.
I feel i can use the following
Sp_who2 'active'
Can some one help me to write a query that can identify the heaviest usage spid and Kill that process. I want to automate it in such a way that the SQL job runs for every 5 minutes which cheks for this actively running process and Kills the user.
Please do help me
Thanks
Eben
October 29, 2009 at 8:50 am
I would be careful automatically killing a process for a user.
For 2005 I would look into using sys.dm_exec_sessions,sys.dm_exec_requests, and sys.dm_exec_sql_text
October 29, 2009 at 9:43 am
I would agree that killing the most heavily used query is a way to get into trouble. I think what you need to do is identify what the user(s) is doing that is eating up resources on the server.
If the work needs to get done, then you need to find a way to get it done, not kill it. If it doesn't need to get done, prevent it from running.
October 29, 2009 at 9:47 am
Steve Jones - Editor (10/29/2009)
I would agree that killing the most heavily used query is a way to get into trouble. I think what you need to do is identify what the user(s) is doing that is eating up resources on the server.If the work needs to get done, then you need to find a way to get it done, not kill it. If it doesn't need to get done, prevent it from running.
I remember a few kills where the rollback seemed to be as bad as the "performance killing" query.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 29, 2009 at 9:50 am
Steve Jones - Editor (10/29/2009)
I would agree that killing the most heavily used query is a way to get into trouble. I think what you need to do is identify what the user(s) is doing that is eating up resources on the server.If the work needs to get done, then you need to find a way to get it done, not kill it. If it doesn't need to get done, prevent it from running.
In agreement. In addition to identifying what the user is doing, I would find a way to improve it, do it for the user, setup an automated job - something so that it wasn't such a resource hog.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 29, 2009 at 11:19 am
What type of queries ? Update ? "select" only ? Hitting production ? Is there blocking ? Can you tune the queries ? Set up a reporting database server, replicate to it, and let the users go crazy over there instead. .....
October 29, 2009 at 12:08 pm
I can just see me being the only one on in the middle of the night, running 'SELECT TOP 10 employee FROM staff' and wondering why it keeps failing every five minutes...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 29, 2009 at 12:20 pm
There is a script that Jason Massie wrote that can be found HERE which is pretty helpful in finding high CPU queries. The query below will tell you the high memory users. You can also use profiler though to collect those queries over time and just filter by the user in question. Use that as a worklist to help them out with writing better code. You never know they may end up doing really well with a little help.
SELECT sp.spid,
sp.loginame,
sp.program_name,
sp.hostname,
t.text AS sql_text,
query_plan,
requested_memory_kb,
granted_memory_kb,
used_memory_kb,
mg.dop,
mg.request_time,
mg.grant_time,
mg.query_cost,
mg.timeout_sec,
mg.resource_semaphore_id,
mg.wait_time_ms
FROM (SELECT DISTINCT spid,
loginame,
program_name,
hostname
FROM sys.sysprocesses) sp
INNER JOIN sys.dm_exec_query_memory_grants mg
ON sp.spid = mg.session_id
CROSS APPLY sys.Dm_exec_sql_text(mg.sql_handle) t
CROSS APPLY sys.Dm_exec_query_plan(mg.plan_handle)
WHERE used_memory_kb > 500
AND query_cost > 5
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 30, 2009 at 5:10 am
Guys,
Thanks a lot for all your response. I have actually missed to inform that my set up is an SQL2000 Enterprise edition so i am left out with using DMV's.
The user who attempts to run this is a big shot and we cannot revoke his acess but if he runs pretty big queries that utilizes the memory to its maximum we can Kill the process and inform the team on the activity.
This is the scenario, I am between the devil and the deep blue sea,...
neither can i revoke the acess of the user who i know, i just specifically want to monitor that particular user and neither am allowed to run a SQL profiler for this.
thanks
eben
October 30, 2009 at 5:39 am
Document, document, document, then go have a 'training' session with him and his supervisor, showing why you need to have it.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 30, 2009 at 7:25 am
jcrawf02 (10/29/2009)
I can just see me being the only one on in the middle of the night, running 'SELECT TOP 10 employee FROM staff' and wondering why it keeps failing every five minutes...
It would be fun! 🙂 You run the same query over and over a again. When you are done with your query you are free to go home, but you will never be done because someone wants to kill your process every five minutes. 😀
When the morning comes and you try to explain to your boss that you are not done with his report yet because your job gets killed everytime, and then the "clever" DBA walks by and say "I know what's the problem is." Then i do not think that is only the process that gets killed. 😀
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
October 30, 2009 at 7:30 am
ebenraja (10/29/2009)
Hello friends,I do have a 'SQL user' who runs a lot of queries that causes performance issues to the database machine. I want to write a query that would check the CPU,Memory I/O usage from the systems table.
I feel i can use the following
Sp_who2 'active'
Can some one help me to write a query that can identify the heaviest usage spid and Kill that process. I want to automate it in such a way that the SQL job runs for every 5 minutes which cheks for this actively running process and Kills the user.
Please do help me
Thanks
Eben
If you really have this issue, try to teach him instead of automatic killing his process. You are begging for trubble this way. A rollback is also a heavy process.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
October 30, 2009 at 7:50 am
ebenraja (10/30/2009)
Guys,Thanks a lot for all your response. I have actually missed to inform that my set up is an SQL2000 Enterprise edition so i am left out with using DMV's.
The user who attempts to run this is a big shot and we cannot revoke his acess but if he runs pretty big queries that utilizes the memory to its maximum we can Kill the process and inform the team on the activity.
This is the scenario, I am between the devil and the deep blue sea,...
neither can i revoke the acess of the user who i know, i just specifically want to monitor that particular user and neither am allowed to run a SQL profiler for this.
thanks
eben
You are still addressing the effect, not the cause.
P.S. You're in the 2005 forum. There's a 2000 forum also.
October 30, 2009 at 8:05 am
I would try tuning his query instead of automating a process to kill it. If he is a bigshot as you say, he is likely to get upset when his query gets killed everytime he runs it.
You might be able to tune the query in the same time it takes to develop a routine to kill his spid.
October 30, 2009 at 8:07 am
Erich Brinker (10/30/2009)
I would try tuning his query instead of automating a process to kill it. If he is a bigshot as you say, he is likely to get upset when his query gets killed everytime he runs it.You might be able to tune the query in the same time it takes to develop a routine to kill his spid.
And then you'll earn points for improving performance.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply