MDA表监控语句大全

hannibal

激活监控开关
sp_configure 'enable monitoring',1
go
sp_configure 'per object statistics active',1
go
sp_configure 'enable metrics capture',1
go
关闭监控开关
sp_configure 'enable monitoring',0
go
sp_configure 'per object statistics active',0
go
sp_configure 'enable metrics capture',0
go
常用SQL
1.显示监控时段内耗时最长的前10条SQL（超长SQL只会显示第一段）

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

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

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

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

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

5.显示前30张热点表中未使用的索引


select top 30 ObjectID into #t1
from master..monOpenObjectActivity
where DBID=db_id('db_name')
and IndexID=0
Order by PhysicalWrites desc
use db_name
select convert(varchar,object_name(ObjectID )), convert(varchar,ObjectName)
from master..monOpenObjectActivity
where DBID=db_id('db_name') and
IndexID!=0 and UsedCount=0
And ObjectID in ( select ObjectID from #t1 )
drop table #t1

hannibal

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

7.显示大表的索引数
select convert(varchar,object_name(A.id)) as tablename , count(1)-1 as index_cnt, row_count(db_id('db_name'),A.id) as row_cnt
from sysindexes A, sysobjects B
where A.indid != 255
and A.id = B.id and B.type = 'U'
group by A.id
having row_count(db_id('db_name'),A.id) > 10000
order by 2


列出表空间

select (d.name+"."+u.name+"."+o.name),((reserved_pgs(i.id,doampg) *
2048.0)/1048576),((data_pgs(i.id,doampg) * 2048.0)/1048576)
from sysobjects o, sysindexes i,
sysusers u, master..sysdatabases d
where d.dbid=db_id() and o.id=i.id and o.uid=u.uid
  and reserved_pgs(i.id,doampg) > 0 and o.type='U'
  order by data_pgs(i.id,doampg)/reserved_pgs(i.id,doampg)


To determine which currently executing queries are consuming the most CPU, and to list the text of those queries, enter:

select top 10 s.SPID, s.CpuTime, s.LogicalReads, t.SQLText
from master..monProcessStatement s, master..monProcessSQLText t
where s.SPID = t.SPID
and s.SPID != @@spid
order by s.CpuTime DESC, t.SPID,t.BatchID,t.LineNumber,t.SequenceInLine

hannibal

列出等待事件/时间，注意这条语句要阶段执行，从差值来判断时间段内的等待事件信息
select w.Waits, w.WaitTime, w.WaitEventID, i.Description
from master..monSysWaits w, master..monWaitEventInfo i
where w.WaitEventID = i.WaitEventID
order by 2


When you see a scaling problem due to a spinlock contention, use des_bind to improve the scalability of the server where object descriptors are reserved for hot objects. The descriptors for these hot objects are never reserved.

dbcc tune(des_bind, <dbid>, <objname>)
To remove the binding, use:

dbcc tune(des_unbind, <dbid>, <objname>)


select CacheName,IOBufferSize,AllocatedKB/1024 AllocatedMB,PagesTouched/@@pagesize*1024 CacheBeingUsedKB,
PagesTouched/@@pagesize*1024/AllocatedKB*100 CacheUsage,PagesRead/@@pagesize*1024 CacheUsedKB,Stalls
from master..monCachePool


以下语句可用于列出平均读IO超过1000以上的索引，供你们参考。

use user_db
go
select convert(varchar(50),object_name(ObjectID)) tablename,ObjectName,UsedCount,LogicalReads/UsedCount  AverageReads
from master..monOpenObjectActivity
where
IndexID !=0 and UsedCount != 0
and LogicalReads/UsedCount > 1000
order by AverageReads desc
go

另外，目前系统中很多表的索引建的太多，对于从未使用过的索引应该考虑删除。
可以用以下语句啊查看哪些索引没有被使用过。
use user_db
go
select convert(varchar(50),object_name(ObjectID)) tablename,ObjectName,UsedCount
from master..monOpenObjectActivity
where
IndexID !=0 and UsedCount = 0

go

hannibal

以下语句用于列出当前数据库中消耗cpu最多的前100条SQL的内容，注意：由于监控参数的限制，可能语句会不全，此时建议使用dbcc sqltext(spid)去捕获完整SQL（使用前先dbcc traceon(3604)）

select top 100 s.SPID, s.CpuTime, t.SQLText
from master..monProcessStatement s, master..monProcessSQLText t
where s.SPID = t.SPID
and s.SPID != @@spid
order by s.CpuTime DESC, t.SPID,t.BatchID,t.LineNumber,t.SequenceInLine

通过以下语句检查逻辑读较多的SPID

select SPID, KPID, BatchID, ContextID, DBID, ProcedureID, StartTime,
      ElapsedTime=datediff(ss,StartTime,max(EndTime)),
      CPUTime=sum(CpuTime),LogicalReads=sum(LogicalReads),
      PagesModified=sum(PagesModified)
from monSysStatement
group by SPID, KPID, BatchID, ContextID, DBID, ProcedureID, StartTime
having datediff(ss,StartTime,max(EndTime)) > 5
and sum(LogicalReads) > 300000
order by 7

andkylee

不错~

sn97163

hblt_oltp:wink:

gilbert2002

hannibal

回复 6# sn97163


呵呵，多谢提醒～

zolowgc

顶了再看，哈哈