October 21, 2010 at 8:36 am
Hi All,
I have one question here many times if we face the SQLSERVER.EXE is taking more CPU like 90-96% then usulaay we will do some basic steps like SP_who2,Select * from sys.sysprocesses to identify what are all the Processes is running and what activity is using ..
when ever if I fired the query Select * from sys.sysprocesses or Some DMV's Iam unalbe to take the decision this is the SPID is taking more CPU and causing the Bottllenck --we know there are some 3RD part tool are there to capture the data and we can use that to anaylze but as we know in SQL server 2005 have many DMV's that help us to anaylze the performance issue of CPU or do i need to check for Waittypes ot wait events or OS level DMv's or either we need to run the trace(but I not recommend this one)
Simple way to tell
How can i say particulary this is the SPID is causing the CPU and How to anaylze that one if so the what will be the value in KB orMB...
I know that the performance issue anaylzing is not an easy task we need to consider from H/w compnenets to the SQL queries(SP's /queries or DMV's) etc........
Thank you for all your answer ans solutions in Advance!!!!!!!
Correct me If Iam wrong here...
Regards,
Rama udaya.K
October 21, 2010 at 9:33 am
This script extracts the top 20 queries by CPU usage.
SELECT TOP 20
query_stats.sql_handle AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM (SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
) as query_stats
GROUP BY query_stats.sql_handle
ORDER BY 2 DESC ;
Could be a starting point.
-- Gianluca Sartori
October 21, 2010 at 9:37 am
Hi,
Thnx for your Script, yes we can find out topn usuage by the queries and also script also the GUI, But How can I anaylaze in what basis...
Thnx once again....
Regards,
RamaUdaya.K
October 21, 2010 at 9:52 am
Once you found the top queries, you can analyze their exec plans and find if they're pushing CPU to the roof.
Here's a good method to statement-level CPU estimations:
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply