February 24, 2011 at 5:36 am
Dear All,
How to check which process is using more CPU and who is running that query in SQLServer2005?
Please advice.
Thanks in advance.
Regards,
Ravi.
February 24, 2011 at 6:20 am
The optional free Performance Dashboard reports can be installed to give you that type of information.
Also, 3rd party tools like Idera's Diagnostic Manager, among others.
February 24, 2011 at 6:46 am
Use DMV for this:
SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC
More queries are here: http://technet.microsoft.com/en-us/library/bb838723(office.12).aspx
February 24, 2011 at 6:48 am
It is better to configure performance dashboard report in SQL server 2005.
At the same time, you may run below query to collect useful info, this will give top 10 CPU consuming queries
since the instance was started.
SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC
M&M
February 27, 2011 at 10:25 pm
Thanks for your reply !!
Can anyone advice how to check the user who is running the query?
Regards,
Ravi.
February 28, 2011 at 9:46 am
Add sys.dm_exec_requests and sys.dm_exec_sessions to the query. Something like this:
SELECT TOP 50 a.*,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(a.sql_handle)) AS query_text
FROM
(SELECT
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
s.login_name,
s.host_name,
s.program_name,
qs.sql_handle ,
qs.statement_start_offset,
qs.statement_end_offset
FROM sys.dm_exec_query_stats AS qs
LEFT JOIN sys.dm_exec_requests c
ON qs.sql_handle = c.sql_handle
LEFT JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
GROUP BY
s.login_name,
s.host_name,
s.program_name,
qs.sql_handle ,
qs.statement_start_offset,
qs.statement_end_offset) a
ORDER BY total_cpu_time DESC
But this will show only active sessions.
March 1, 2011 at 7:29 am
1) sp_whoisactive
2) search web for glenn berry sql server DMV. here is one for 2008, he has another for 2005. lots of goodness here: http://sqlserverperformance.wordpress.com/2009/05/21/sql-server-2008-diagnostic-script/
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2011 at 7:37 am
TheSQLGuru (3/1/2011)
2) search web for glenn berry sql server DMV. here is one for 2008, he has another for 2005. lots of goodness here: http://sqlserverperformance.wordpress.com/2009/05/21/sql-server-2008-diagnostic-script/%5B/quote%5D
...and to go along with TheSQLGuru's advice on sp_whoisactive, you can check out the following link on how to set up logging with that procedure. Solid information;
http://www.littlekendra.com/2011/02/01/whoisactive/[/url]
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 2, 2011 at 1:01 am
Thanks very much for your valid responses.
Could you please advice for the below questions as well.
1. How many users connected to SQLServer?
2. How many processes are currently running?
Regards,
Ravi.
March 2, 2011 at 2:56 am
hi ravi,
For findout the howmany no.of users connected to sql server you can able to use the stored procedure: sp_who
For find out the no. of the active users connected to sql server you can use: sp_who 'active'
There is alternative one for find out the no.of users connected to sql server :
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses
For findout the list of processes currently running in sql server :sp_who
otherwise go to sql server enterprize manager->management folder-->process info object--->here you can find the details of processes which currently run
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply