Find SQL statements with high CPU usage

Recently, we have been dealing with database data transmission problems. The data transmitted every day is about 40 w-50w. SQL services often occupy 1.7 GB of CPU memory. Fortunately, the data servers are 8-16 cores. I found an element code on the Internet to test the database's performance and record:

Select top 10 rank () over (order by (total_worker_time + 0.0)/execution_count DESC, SQL _handle, statement_start_offset) as row_no, (rank ()
Over (order by (total_worker_time + 0.0)/execution_count DESC, SQL _handle, statement_start_offset) % 2 as L1, creation_time, last_execution_time,
(Total_worker_time + 0.0)/1000 as total_worker_time, (total_worker_time + 0.0)/(execution_count * 1000) as [avgcputime],
Total_logical_reads as [logicalreads], total_logical_writes as [logicalwrites], execution_count, total_logical_reads + total_logical_writes as [aggio],
(Total_logical_reads + total_logical_writes)/(execution_count + 0.0) as [avgio], case when SQL _handle is null then ''else (substring (St. Text,
(Qs. statement_start_offset + 2)/2, (case when Qs. statement_end_offset =-1 then Len (convert (nvarchar (max), st. Text ))
* 2 else Qs. statement_end_offset end-Qs. statement_start_offset)/2) end as query_text, db_name (St. dbid) as db_name, st. objectid as object_id
From SYS. dm_exec_query_stats QS cross apply SYS. dm_exec_ SQL _text (SQL _handle) ST
Where total_worker_time> 0
Order by [avgcputime] DESC

Author: lifuyun
Source: http://lifuyun.cnblogs.com
The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.