February 9, 2015 at 1:00 am
Hi All,
User calls up and tell that SQL Server is using 100% CPU.
Please find the most expensive queries and kill those queries.
Whats the best way to do it ?. Can someone pls share me the script to do it ?
When I googled, get lot of options and confused.
Thanks in advance.
Sa
February 9, 2015 at 1:55 am
Politely tell the user that the database management is your business and not theirs. Or tell them that their queries are the most expensive and offer to kill them.
Randomly killing connections isn't going to fix performance problems. If you don't investigate first, you could kill something important. Like the payroll run. I supposed it's fine if you don't get paid, right?
If you have high CPU usage, identify the queries which are using excessive CPU (overall, not per execution) and tune them.
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
February 9, 2015 at 2:05 am
Thanks Gila.
Yes, I have already conveyed to them. I even passed them the queries they need to look at long ago.
Think its done with some pathetic design and coding. All those problematic queries dynamic queries.
So to find the most expensive queries and kill them, can we utilize the activity monitor ?
Thanks.
February 9, 2015 at 2:26 am
Randomly killing connections is not a solution. At best you're going to have the people simply re-connect and start their queries over, at worst you're going to have reports sent to your boss complaining about how unstable the server is since it keeps dropping connections.
Look into Resource Governor if you want to be able to limit CPU usage, but that requires some way to identify the connections (login name, host, application, etc). Look into tuning the queries, tuning the indexes
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
February 9, 2015 at 3:02 am
first, you should find the query, and confirm with your client, then kill them.
I often find the most longest query first, and check if there is any optimization I can do.
Hope the following script can help you!
SELECT 'kill',s.[session_id] ,
r.[start_time] ,
DATEDIFF(MILLISECOND, r.start_time, GETDATE()) AS elapsed_MS ,
r.[status] AS RequestStatus ,
DB_NAME(r.database_id) AS DatabaseName ,
r.[wait_type] ,
r.[wait_resource] ,
r.[wait_time] ,
r.[reads] ,
r.[writes] ,
r.[logical_reads] ,
s.[status] AS SessionStatus ,
s.[host_name] ,
s.[original_login_name] ,
s.[nt_user_name] ,
s.[program_name] ,
s.[client_interface_name] ,
c.[client_net_address] ,
SUBSTRING(qt.text, r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2) AS ExecutingSQL ,
qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY elapsed_MS DESC
February 9, 2015 at 4:25 am
Sometimes I find it better to kill the client rather than kill the query.
🙂
(I loved Gail's restraint on her second reply.....)
February 9, 2015 at 8:56 am
Joy Smith San (2/9/2015)
Thanks Gila.Yes, I have already conveyed to them. I even passed them the queries they need to look at long ago.
Think its done with some pathetic design and coding. All those problematic queries dynamic queries.
So to find the most expensive queries and kill them, can we utilize the activity monitor ?
Thanks.
To second Gail's suggestions, killing the most expensive queries can make things much worse especially if those queries have done inserts or updates which will be rolled back.
Don't kill expensive queries. Identify them and get people to fix them. If they can't, then you need to.
Just killing expensive queries will perpetuate an already very bad situation and could become your full time job if the queries aren't repaired.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2015 at 6:58 pm
gaojia2004 (2/9/2015)
first, you should find the query, and confirm with your client, then kill them.I often find the most longest query first, and check if there is any optimization I can do.
Hope the following script can help you!
SELECT 'kill',s.[session_id] ,
r.[start_time] ,
DATEDIFF(MILLISECOND, r.start_time, GETDATE()) AS elapsed_MS ,
r.[status] AS RequestStatus ,
DB_NAME(r.database_id) AS DatabaseName ,
r.[wait_type] ,
r.[wait_resource] ,
r.[wait_time] ,
r.[reads] ,
r.[writes] ,
r.[logical_reads] ,
s.[status] AS SessionStatus ,
s.[host_name] ,
s.[original_login_name] ,
s.[nt_user_name] ,
s.[program_name] ,
s.[client_interface_name] ,
c.[client_net_address] ,
SUBSTRING(qt.text, r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2) AS ExecutingSQL ,
qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY elapsed_MS DESC
Thanks.
Does this script check for "CPU usage" ?
February 9, 2015 at 6:59 pm
Thank you all for your valuable suggestions. Yes, I am clear on that part. I am planning to give them rights to find and kill their queries, if they cannot fine tune it.
Small doubt.
"CPUTime" returned by "sp_who2 Active" can be used to find the most CPU intensive processes ?
February 10, 2015 at 11:21 am
You can use that CPU time, but you have to be careful both to compare it to the amount of time that process has been running and to check it a few times consecutively to make sure it's increasing.
You have to do the first because you might have a long-running process that is relatively light CPU-wise, but has very high CPU times just because it's run so long. So, between a process that has run for 10 hours and done 30 minutes of CPU time, and a process that has run for 2 minutes and done 4 minutes of CPU time, the latter would be more likely to contribute to CPU pressure.
Even that's not the full story if you're wanting to check real-time CPU usage, because one or both of those queries may not be using CPU any more. That's why you'd also have to check multiple times and look for the processes that are most rapidly increasing CPU time.
To make matters more annoying for the troubleshooter, if the CPU pressure is just caused by a large number of short-running processes coming in, it's difficult to identify processes that are increasing CPU time, since they run quickly and are gone.
It's also difficult when parallel processes are included, as then you have to account for CPU use by child threads, and occasionally those just show a bogus 2147483647 number for CPU.
Because of all that, I tend to use the following to diagnose current CPU pressure:
SELECT threadcount.spid,threadcount.threads, procs.cpu,db_name(procs.dbid) as DB,
procs.hostname, procs.program_name,procs.loginame,text.text
FROM sysprocesses procs
CROSS APPLY sys.dm_exec_sql_text(procs.sql_handle) text
INNER JOIN (select count(*) as Threads, spid FROM sysprocesses
WHERE status IN ('running','runnable') AND spid!=@@spid GROUP BY spid) ThreadCount
ON procs.spid=ThreadCount.spid
ORDER BY threads DESC
It's not perfect (and I need to future-proof it by using sys.dm_exec_requests and sys.dm_os_tasks instead of sysprocesses), but this shows me all the processes that are either running on a scheduler or waiting only on a scheduler to free up, along with a bunch of other information I find useful.
For example, including the query text helps in the case mentioned above where a large volume of short-lived processes are coming in. If they're all the same query or set of queries, that's easy to notice.
I just run that a few times and check for the processes/queries that are continually in the results.
It's been the easiest/most reliable indicator I've found of what's using CPU right this very moment.
Hopefully that helps!
February 10, 2015 at 12:51 pm
Joy Smith San (2/9/2015)
I am planning to give them rights to find and kill their queries, if they cannot fine tune it.
Really, [font="Arial Black"]REALLY [/font]bad idea.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2015 at 1:10 pm
Jeff Moden (2/10/2015)
Joy Smith San (2/9/2015)
I am planning to give them rights to find and kill their queries, if they cannot fine tune it.Really, [font="Arial Black"]REALLY [/font]bad idea.
Plus a really really big number...a number so big it can't be comprehended by current mathematics, then add 1 more to it. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 11, 2015 at 12:50 am
Joy Smith San (2/9/2015)
Yes, I am clear on that part. I am planning to give them rights to find and kill their queries
You do realise that to do so you give them permission to kill *any* session on the server (permission required is ALTER ANY CONNECTION), so their queries, your queries, critical maintenance, application, etc.
Really, really bad idea.
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
February 11, 2015 at 7:44 am
--Current queries
SELECT
r.session_id,
s.TEXT,
r.[status],
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s
Find blocking_session_id or heavy one and then
kill #
February 11, 2015 at 8:16 am
admin 31599 (2/11/2015)
--Current queriesSELECT
r.session_id,
s.TEXT,
r.[status],
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s
Find blocking_session_id or heavy one and then
kill #
If using this script very often the next step is go to your desk and get 3 envelopes. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply