有6个数据库1.查询各数据库执行语句总数
序号 语句数量 数据库名称 数据库编号 语句占比（%）
1 1158153 news 9 22.8%
2 1040327 itsd 32 20.5%
3 730757 IIS2 11 14.4%
4 628055 BellWether 38 12.4%
5 554575 XQP2 13 10.9%
6 195566 DMS 37 3.9%2.查询各数据库耗时1000毫秒以上语句总数
序号 语句数量 数据库名称 数据库ID 语句占比（%）
1 2523 XQP2 13 29.1%
2 1878 DMS 37 21.7%
3 1197 itsd 32 13.8%
4 1126 IIS2 11 13.0%
5 914 cxwy 19 10.5%
6 319 news 9 3.7%请指教，谢谢！
序号 语句数量 数据库名称 数据库编号 语句占比（%）
1 1158153 news 9 22.8%
2 1040327 itsd 32 20.5%
3 730757 IIS2 11 14.4%
4 628055 BellWether 38 12.4%
5 554575 XQP2 13 10.9%
6 195566 DMS 37 3.9%2.查询各数据库耗时1000毫秒以上语句总数
序号 语句数量 数据库名称 数据库ID 语句占比（%）
1 2523 XQP2 13 29.1%
2 1878 DMS 37 21.7%
3 1197 itsd 32 13.8%
4 1126 IIS2 11 13.0%
5 914 cxwy 19 10.5%
6 319 news 9 3.7%请指教，谢谢！
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : |
#|{>/------------------------------------------------------------------------\<}|
#|: | 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_countselect 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 descselect * from #FindTopCPUQueries_set2
order by AvgCPUTime desc--drop table #FindTopCPUQueries_set1
--drop table #FindTopCPUQueries_set2