/*
------------------------------------------------------------------------------+ #| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : | #|{>/------------------------------------------------------------------------\<}| #|: | Author : 小爱(Beirut) | #|: | Description: 查找占用upu 最多的一些脚本 | #|: | SQL Version: 适用于 SQL 2012, SQL 2008 R2, SQL 2008 | #|: | Copyright : 免费使用和共享e /^(o.o)^\ | #|: | Create Date: 2012-04-13 16:50:20.577 | #|: | Revision : Version: 1.1 持续更新ing | #|{>\------------------------------------------------------------------------/<}| #| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : | #+-----------------------------------------------------------------------------
*/
select
GETDATE()
use tempdb
go
IF
object_id(
'
tempdb..#FindTopCPUQueries_set1
')
is
not
null
DROP
TABLE
[
dbo
].
[
#FindTopCPUQueries_set1
]
GO
declare
@ServerTime
datetime
=
getdate() ,
@ConvertMiliSeconds
bigint
=
1000 ,
@FilterMoreThanMiliSeconds
bigint
=
1 ,
@FilterHours
bigint
=
2 ,
@execution_count
bigint
=
2 ,
@debugFlg
bit
=
0
if
@debugFlg
=
1
select
@ServerTime
as ServerTime,
@ConvertMiliSeconds
as ConvertMiliSeconds ,
@FilterMoreThanMiliSeconds
as FilterMoreThanMiliSeconds,
@FilterHours
as FilterHours ,
@execution_count
as execution_count
select
top
300
@@servername
as servername,
@ServerTime
as runtime ,
isnull(
db_name(QueryText.dbid),
'
PreparedSQL
')
as DBName ,
SUBSTRING(QueryText.
text,(QueryStats.statement_start_offset
/
2)
+
1, (
isnull((
CASE QueryStats.statement_end_offset
WHEN
-
1
THEN
DATALENGTH(QueryText.
text)
WHEN
0
THEN
DATALENGTH(QueryText.
text)
ELSE QueryStats.statement_end_offset
END
- QueryStats.statement_start_offset ) ,
0)
/
2)
+
1 )
AS QueryExecuted ,total_worker_time
AS total_worker_time ,QueryStats.execution_count
as execution_count ,statement_start_offset,statement_end_offset ,(
case
when QueryText.dbid
is
null
then
OBJECT_NAME(QueryText.objectid)
else
OBJECT_NAME(QueryText.objectid, QueryText.dbid)
end )
as ObjectName ,query_hash ,plan_handle ,sql_handle
into #FindTopCPUQueries_set1
from sys.dm_exec_query_stats
as QueryStats
cross apply sys.dm_exec_sql_text(QueryStats.sql_handle)
as QueryText
where QueryStats.query_hash
in (
select QueryStatsBaseTable.query_hash
from sys.dm_exec_query_stats QueryStatsBaseTable
where last_execution_time
>
DATEADD(hh,
-
@FilterHours,
GETDATE())
group
by query_hash
having (
sum(total_worker_time)
/
sum(execution_count))
>
@ConvertMiliSeconds
and
sum(execution_count)
>
@execution_count )
order
by total_worker_time
/execution_count
DESC;
if
@debugFlg
=
1
select
*
from #FindTopCPUQueries_set1
order
by QueryExecuted
if
object_id(
'
tempdb..#FindTopCPUQueries_set2
')
is
not
null
DROP
TABLE
[
dbo
].
[
#FindTopCPUQueries_set2
]
select servername,runtime,
max(DBName)
as DBName,
max(QueryExecuted)
as QueryExecuted ,(
sum(total_worker_time)
/
sum(execution_count))
/
@ConvertMiliSeconds
as AvgCPUTime ,
sum(execution_count)
as execution_count,query_hash,
max(ObjectName)
as ObjectName
into #FindTopCPUQueries_set2
from #FindTopCPUQueries_set1
group
by query_hash,servername,runtime
order
by AvgCPUTime
desc
select
*
from #FindTopCPUQueries_set2
order
by AvgCPUTime
desc
--
drop table #FindTopCPUQueries_set1
--
drop table #FindTopCPUQueries_set2
查看系统中cpu 开销最大的一些sql查询
最新推荐文章于 2023-08-05 23:32:11 发布