high cpu usage
in [Programming SQL]
|
From: SqlBeginner on 15 Jul 2010 18:41 Hi, My Database (sql 2005) is all of a sudden showing up 100% CPU usage! I googled a bit and found that we need to use the DMV sys.dm_exec_query_stats for finding out the SP which is performing badly. But my question how to use sys.dm_exec_query_stats and to find out the exact t-sql query within the SP which needs more tweaking? Thanks for your help in advance. Regards Pradeep
From: Eric Isaacs on 15 Jul 2010 21:45 To get the cached SQL execution time, try the following: SELECT TOP 100 query_stats.statement_text AS [SQLText], SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [AverageCPUTime] 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.statement_text ORDER BY 2 DESC ; -Eric Isaacs
From: SqlBeginner on 16 Jul 2010 14:56 Thanks for the response Eric. Hope the query lists the top 100 CPU intensive query in a given DB. So the top most record listed is the one which eats up the CPU more. Is my understanding right? I got confused on seeing the name Average CPU time. Regards Pradeep "Eric Isaacs" wrote: > To get the cached SQL execution time, try the following: > > SELECT TOP 100 > query_stats.statement_text AS [SQLText], > SUM(query_stats.total_worker_time) / > SUM(query_stats.execution_count) AS [AverageCPUTime] > 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.statement_text > ORDER BY > 2 DESC ; > > -Eric Isaacs > . >
From: Eric Isaacs on 16 Jul 2010 15:26 The previous version finds the slowest processes on the server that use the most CPU cycles. I tweaked it a little after I left that message. Now if you specify the database, this will provide the top slowest portions of different objects (sprocs, views, triggers, etc.) in that database. It's a little slow to run itself since it's calling a function, but it works. USE databasename SELECT query_stats.objectid, OBJECT_NAME(query_stats.objectid) AS [ObjectName], query_stats.Text, query_stats.statement_text AS [SQLText], SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [AverageCPUTime] FROM ( SELECT QS.* ,ST.objectid, ST.Text, 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 WHERE OBJECT_NAME(query_stats.objectid) IS NOT NULL GROUP BY query_stats.statement_text, query_stats.objectid, query_stats.TEXT ORDER BY SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) DESC ; -Eric Isaacs
From: SqlBeginner on 16 Jul 2010 16:58
Thanks Eric this version gives more richer information. Regards Pradeep "Eric Isaacs" wrote: > The previous version finds the slowest processes on the server that > use the most CPU cycles. I tweaked it a little after I left that > message. Now if you specify the database, this will provide the top > slowest portions of different objects (sprocs, views, triggers, etc.) > in that database. It's a little slow to run itself since it's calling > a function, but it works. > > USE databasename > > SELECT > query_stats.objectid, > OBJECT_NAME(query_stats.objectid) AS [ObjectName], > query_stats.Text, > query_stats.statement_text AS [SQLText], > SUM(query_stats.total_worker_time) / > SUM(query_stats.execution_count) AS [AverageCPUTime] > FROM > ( > SELECT > QS.* > ,ST.objectid, ST.Text, > 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 > WHERE > OBJECT_NAME(query_stats.objectid) IS NOT NULL > GROUP BY > query_stats.statement_text, > query_stats.objectid, > query_stats.TEXT > ORDER BY > SUM(query_stats.total_worker_time) / > SUM(query_stats.execution_count) DESC ; > > > -Eric Isaacs > . > |