收藏的一个Sqlserver性能查询，包括查询CPU 网络等

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,case when type like '%IO_COMPLETION%' or type='WRITELOG'
			  then waittime else 0 end)) as Writes,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!