declare @LgReads bigint
select @LgReads=cntr_value from master.dbo.sysperfinfo where counter_name='Page lookups/sec'
if object_id(N'tempdb.dbo.#waits') is not null GOTO InsertTb
if object_id(N'tempdb.dbo.#waits') is null GOTO CreateTb
CreateTb:
create table #waits (type varchar(128), req bigint, waittime bigint, signal bigint) GOTO InsertTb
InsertTb:
insert into #waits exec('dbcc sqlperf(waitstats)')
--insert into WaitsLog (DT,CPU,Locks,Reads,Writes,Network,PhReads,PhWrites,LgReads)
select
getdate() AS DT,
CAST(@@CPU_BUSY * CAST(@@TIMETICKS AS FLOAT) / 1000 AS BIGINT) as CPU, -- in milliseconds
sum(convert(bigint, case when type like 'LCK%'
then waittime else 0 end)) as Locks,
sum(convert(bigint, case when type like 'LATCH%' or type like 'PAGELATCH%' or type like 'PAGEIOLATCH%'
then waittime else 0 end)) as Reads,
sum(convert(bigint, case when type like '%IO_COMPLETION%' or type='WRITELOG'
then waittime else 0 end)) as Writes,
sum(convert(bigint, case when type in ('NETWORKIO','OLEDB')
then waittime else 0 end)) as Network,
@@TOTAL_READ AS PhReads, @@TOTAL_WRITE AS PhWrites, ISNULL(@LgReads, 0) AS LgReads
from #waits
收藏的一个Sqlserver性能查询,包括查询CPU 网络等
最新推荐文章于 2022-04-27 16:40:09 发布