ASE15最常用监控SQL...不断更新中...

hannibal

激活监控开关
sp_configure 'enable monitoring',1
go
sp_configure 'per object statistics active',1
go
sp_configure 'enable metrics capture',1  
go
--注意！ 打开metrics capture监控占用空间较多，建议只在测试调优时打开，
--该监控记录的sysqueryplan表存储于用户库上，需要及时登录用户库使用 sp_metrics 'drop','1' 删除！切记！


关闭监控开关
sp_configure 'enable monitoring',0
go
sp_configure 'per object statistics active',0
go
sp_configure 'enable metrics capture',0
go
常用SQL
以下假设用户库名为 user_database
1.显示监控时段内耗时最长的前5条SQL（超长SQL只会显示第一段）

select distinct top 5 hashkey, (elap_avg+exec_avg)*cnt total_time, cnt, qtext
from user_database..sysquerymetrics
where sequence = 0
order by 2 desc
go

2.根据以上得出的hashkey显示完整SQL

select hashkey, sequence, qtext  
from hblt_oltp..sysquerymetrics
where hashkey = 22943604
order by 2
go

3.检查表扫描情况
select  ObjectName,UsedCount
from master..monOpenObjectActivity
where IndexID=0 and UsedCount > 0
and DBID=db_id('user_database')
order by 2


4.显示前10张热点表
select top 10 ObjectName, PhysicalWrites, UsedCount
from master..monOpenObjectActivity
where DBID=db_id('user_database')
and IndexID=0
Order by PhysicalWrites desc

5.显示未使用的索引
select ObjectName
from master..monOpenObjectActivity
where DBID=db_id('user_database') and
IndexID!=0 and UsedCount=0
order by 1

6.显示使用最频繁的10个索引
select top 10 ObjectName, LogicalReads, PagesRead，UsedCount
from master..monOpenObjectActivity
where IndexID!=0
Order by LogicalReads desc, PagesRead desc

[ 本帖最后由 hannibal 于 2008-8-4 12:05 编辑 ]

robinhm

謝謝, 收下了;